All language subtitles for [SubtitleTools.com] Optimizer And Execution Plans - Learning Oracle 12c [Video]

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

In this lesson, we're going to take a look at execution plans,

SQL execution plans, and the explain plan tool

or command that we can use to determine

how Oracle is going to execute a given SQL statement.

And let's put in a simple SQL statement.

So we're selecting all the columns from the M table

where E name equals Smith.

But let's think about all of the things that

go into that SQL statement.

It's a simple one line SQL statement.

It has a select clause, a from and a where,

but there's actually a lot more that goes on behind the scenes

that it's important to understand.

So what happens when we execute this statement?

Oracle has to perform what's called a parse.

It has to pass this statement, and that basically

means that it's going to break down the statement into machine

language that it can understand.

So consider all that it has to verify in this statement

before it can execute it.

So first and foremost, it has to be syntactically correct,

meaning all of the clauses are spelled correctly.

If we misspell a clause and execute it,

we get a error message that says from keyword not

found where expected.

So that's the first step it has to go

through, syntactical verification of the statement.

The next thing it has to do is verify

that the objects actually exist.

So we say select all the columns from the M table,

but in the parse phase, Oracle has

to decide whether the M table actually exists

and whether the Scott user owns it.

So for instance, if we misspell that and execute,

table or view does not exist.

The next thing it has to do is column verification,

it has to verify that this column exists in this table.

So again, misspelled and executed,

we get an invalid identifier error.

So all of these things come into play.

And then the security phase begins, if you will.

So if we are syntactically correct

and if these objects and columns exist,

then Oracle has to determine whether or not

the user executing the command has the proper permissions

to read from this table and, in some cases, from this column.

So that has to occur, as well.

Now all of that is part of the parse

and we're starting to see that that

could add up to a lot of work on the part of Oracle.

And indeed, parsing a statement is

considered a relatively expensive operation.

But the most important thing that Oracle

does during the parse phase is it breaks down the statement

and comes up with an execution plan.

The execution plan is going to be Oracle's determination

for the best way, the fastest way

to retrieve the data that's requested.

So it looks at all the possible ways

that data could be retrieved in this statement.

Does it look through every row in the table?

Does it use an index?

Does it use a partition?

So any of those things can affect

how a statement is executed, and to DBAs, the execution plan

is incredibly important, and really developers, too.

The execution plan determines really how well

the statement will perform.

So if we write a statement that's

really written improperly or could be written better,

then it's going to perform poorly.

And so we've got a fantastic tool, a very useful tool

in order to determine what the execution plan of a statement

is going to be.

Else how would we know--

when we write this statement, how

would we know if it's basically well

written or not well written?

And that's something called an explain plan.

Now there's different ways to do explain plan,

but SQL Developer has a very useful facility here right up

in our toolbar called the explain plan button.

And we click the button and it gives us the output

that you see here.

It's going to access the Table M,

it's going to do a full scan, a full table scan,

meaning it's going to read every row in the search for E name

equals Smith, and it has a cost--

a resource cost of 3.

That's kind of a relative number,

but it can be used at times to determine

whether you've made an improvement in a statement

or not.

This gives us a basic execution plan

for how our statement is going to run in the database.

So what kinds of things can we do

to influence the outcome of an execution plan?

Well, in this case, we might be able to improve

on this idea of a full table scan.

So basically what will occur here

is that, as Oracle searches this table,

it will take the first row and look

to see whether the e name equals Smith.

If it does, it will return the row.

If not, it goes to the next where

it checks it, goes to the next, next, next, on down the line.

Well, the M table in our case has 14 rows.

That's not a terribly high cost, if we would.

But what if our table had millions of rows?

Is doing a full table scan really the best way

to approach querying a table for that particular piece

of information?

So we might use something called an index, which will basically

create a structure that allows Oracle to more quickly access

that piece of data.

So we've created an index for the M table

on the E name column.

So now anytime that we run this statement,

it will not do a full table scan of every row.

Instead, it will search the index that we've created.

And we can see this using the explain plan button.

Notice how our explain plan is different now.

We've added the index and our explained plan is different,

our execution plan is different.

We've affected it.

So it's going to use the objects M, like before,

but it's also going to use M index.

And instead of full here, it says by index row ID

patched and range scan.

So it's going to do a scan of the index to come up

with the information.

And notice that the cost, the total cost

here has gone from three to two.

Even in a very small table, we can see

the benefit of adding an index.

And if we drop the index, again, look at our explained plan,

we see that it has gone back to the original cost

and doing a full table scan.

Now all of these examples are relatively simple

and these explain plans aren't terribly complex,

but that's not always the case.

These tables are small.

In the real world, tables can be much larger.

There can be joins involved in the select statement

and it can get much more complicated.

So just to show you what that looks like,

let's use kind of an extreme example.

I'm going to make a connection to the database as system

because I want to query from a data dictionary view.

So I'm looking for the M table owned by Scott

and this gives us information about it.

But again, this statement has to be parsed

and an execution plan has to be developed for it,

so let's look at what this plan might look like.

Considerably more complicated.

In fact, it's nested so far that it's

difficult to see in the explain plan.

The reason that this is such a complex execution plan

is that DBA tables is actually a view that

looks at many tables in the underlying system.

Now a lot of these tables are things

like, OBJ, pound sign, file pound sign, user dollar,

OBJ dollar.

These are underlying tables in the Oracle Data dictionary

that Oracle uses itself.

They're not meant to be queried directly.

They're very difficult to read, they

don't have column names that are very indicative of what

the data contains.

But Oracle puts simpler views on top of those tables

in order to make things simpler for us to understand.

But notice a few things here.

It's doing things like a join in this execution

plan with a Cartesian product even, an outer join.

It's doing an index row ID scan of the user dollar table.

So there's many different types of options in this column

that Oracle can tell us that it's

using in order to communicate the execution plan to us.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.