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

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.