Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.