Skip to Main Content
  • Questions
  • difference of explain plan, autotrace and tkprof

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Car.

Asked: May 31, 2007 - 11:17 am UTC

Last updated: May 17, 2022 - 3:49 pm UTC

Version: 10g R2

Viewed 10K+ times! This question is

You Asked

I have confusion about explain plan, autotrace and tkprof. When explain plain showing to us the prediction about how optimizer will work to produce result and tkprof show the real thing that happen, so how about autotrace. In autotrace there are two part, as far as I concern, plan and statistics. What is the plan presented there ? (based on explain plan or tkprof) and if it is just a prediction, second question occur: how oracle know the statistic (eg. oracle will require hardparse or not, n bytes consistent gets, etc)

Thanks.
Elcaro

and Tom said...

the plan from autotrace is an explain plan.

there is no statistic regarding hard parse and optimization - not sure where you are going with that, but the optimizer estimates cardinalities and those in turn with other statistics like clustering factor of indexes, blocks in tables and such - are used to derive IO estimations.

the autotrace statistics reported however are FACTS, after executing the statement, not guesses, not part of optimization.


So, autotrace used explain plan to show the plan that probably, likely will be used and autotrace shows the ACTUAL number of resources expended to execute the query.

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Confusion cleared

Car Elcaro, June 01, 2007 - 1:47 am UTC

Thanks Tom.

Then why use EXPLAIN PLAN at all?

W, June 01, 2007 - 9:20 am UTC

If EXPLAIN PLAN does not give the correct plan then why should anyone use EXPLAIN PLAN at all? I have read your blog

http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html

To be fair with you the more I read about the EXPLAIN PLAN the more Im getting confused.
Tom Kyte
June 01, 2007 - 1:13 pm UTC

if you understand the small number of times it could be misleading, you can use it to judge what will probably happen.

it shows the GUESS, we compare the guess to reality to understand why a bad plan is generated.


I always want the reality, and the plan - reality shows me what actually happened, the plan is the guess and as long as the explain plan steps match the actual - we can use that to help understand what the optimizer was thinking.

as a person tuning sql, that is very useful.

but by itself, the plan is "interesting" but must be considered for what it is - "probably what might happen"

Autotrace - plan and statistics

A reader, June 01, 2007 - 11:46 am UTC

Hi Tom,

Are you saying that when you run autotrace with the statistics turned on, the statistics displayed may not necessarily be what you would get if the query used the plan suggested by the explain plan? Is there anyway to determine if the statistics displayed are a result of the query executing as per the displayed explain plan?

Hope this makes sense.

Thanks
Tom Kyte
June 01, 2007 - 1:31 pm UTC

no, the statistics (logical ios, physical ios, sorts, etc) are what actually happened.

the plan displayed MIGHT NOT be the plan that was used.


ok, so maybe I am saying "the statistics displayed might not be the statistics you would get if the optimizer used the plan displayed"

but not really. it is more that the statistics are what were OBSERVED, the explain plan is a guess as to the plan that MIGHT (or might not) be used.


eg:

ops$tkyte%ORA10GR2> create table t
  2  as
  3  select to_char(rownum) id, all_objects.*
  4    from all_objects
  5  /

Table created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(id);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable n number
ops$tkyte%ORA10GR2> exec :n := 5;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2> select count(*) from t where id = :n;

  COUNT(*)
----------
         1

ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select count(*) from t where id = :n;

  COUNT(*)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 3186229038

------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    22 |     1   (0)| 00:00:0
|   1 |  SORT AGGREGATE    |      |     1 |    22 |            |
|*  2 |   INDEX UNIQUE SCAN| T_PK |     1 |    22 |     1   (0)| 00:00:0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:N)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        118  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte%ORA10GR2> set autotrace off


118 logical IOs where done to find that one row. the plan says index unique, table access by index rowid. I would expect 3-5 IOs, not 118.

That is because the plan 'guessed' is not the plan 'used':

select count(*)
from
 t where id = :n


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          3          0           0
Fetch        2      0.02       0.02        111        118          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03        111        121          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 165  (OPS$TKYTE)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=118 pr=111 pw=0 time=23794 us)
      1   INDEX FAST FULL SCAN T_PK (cr=118 pr=111 pw=0 time=23767 us)(object id 75620)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      1    INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 'T_PK' (INDEX (UNIQUE))


the actual plan used is an index fast full scan (because explain plan saw :N as a string, but it is a number and there is an implicit conversion happening)

A reader, June 04, 2007 - 12:07 pm UTC

Thanks for the great explanation Tom. Makes things a lot clearer.

Would there ever be a situation however where the statistics displayed by autotrace is different from the one displayed by sql_trace/tkprof? IF yes, what would cause this to happen.

As always, thank you for your help
Tom Kyte
June 06, 2007 - 9:43 am UTC

autotrace shows logical IO's for the sql you execute AND recursive SQL (sql executed on behalf of your sql) - tkprof shows statistics attributed to each individual SQL statement.

So, for example:

ops$tkyte%ORA10GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA10GR2> create or replace function f( x in number ) return number
  2  as
  3          l_n number;
  4  begin
  5          for i in 1 .. x
  6          loop
  7                  select count(dummy) into l_n from dual;
  8          end loop;
  9          return x;
 10  end;
 11  /

Function created.

ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select f(user_id) from t;

F(USER_ID)
----------
       165
...
         5
         0

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    38 |   494 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |    38 |   494 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       2451  recursive calls
          0  db block gets
       7278  consistent gets
          1  physical reads
          0  redo size
        918  bytes sent via SQL*Net to client
        422  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         38  rows processed



but the tkprof will show:

select f(user_id)
from
 t


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          1          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.20       0.21          0        501          0          38
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.21       0.22          1        507          0          38

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 165  (OPS$TKYTE)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   TABLE ACCESS (FULL) OF 'T' (TABLE)

********************************************************************************

SELECT COUNT(DUMMY)
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   2422      0.06       0.06          0          0          0           0
Fetch     2422      0.10       0.08          0       7266          0        2422
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4845      0.17       0.15          0       7266          0        2422

Could I Get The Best of Both Worlds ...

Phil singer, June 04, 2007 - 12:15 pm UTC

By running SET AUTOTRACE ON STATISTICS and querrying V$SQL_PLAN (as I craftily finesse stating how one could get the cursor hash_value)?

On a related note, since SQL*Plus must have access to the hash_value for the cursor of query it just traced, why does it not go after V$SQL_PLAN? I have a hunch that V$SQL_PLAN is more complex than it appears at first glance.
Tom Kyte
June 06, 2007 - 9:50 am UTC

actually, if you have "current" software....

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> set serveroutput off
ops$tkyte%ORA10GR2> select * from t where dt = sysdate;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  1xuhvd8h000f0, child number 0
-------------------------------------
select * from t where dt = sysdate

Plan hash value: 1413293367

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |       |       |     2 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    39 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | T    |     1 |    39 |     2   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DT"=SYSDATE@!)

Note
-----
   - dynamic sampling used for this statement


23 rows selected.

Confusion

Boris, October 21, 2017 - 1:16 am UTC

What’s the difference between explain plan and execution plan?

My understanding is that explain plan shows the execution plan and it’s guess estimate.

https://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

Connor McDonald
October 21, 2017 - 1:39 am UTC

"explain plan" is actually a *command* to give you the execution plan.

Probably a better terminology is

*execution* plan, versus
*executed* plan

Using 'explain plan' gives you an *execution* plan under the *current* circumstances in your current session and current known information. That same SQL when you actually run it will have an *executed* plan, that is, the plan that was actually used to run the SQL.

It is *often* the case than the *execution* plan you see from an explain command will match the *executed* plan that was actually used.

But "often" is not the same as "always" :-)

When it comes to the actual running of the SQL, the derivation of the plan (that will ultimately become the *executed* plan) can take *more* into account than what the 'explain plan' command does, eg data types of bind variables, bind variable peeking, adaptive cursor sharing etc etc.


explain plan

Boris, October 21, 2017 - 1:21 pm UTC

Thanks for your response Connor.

Here is my understanding about oracle plan

Guess execution plan

1. The plan generated from autotrace is an explain plan ( guess plan)

2. The plan generated from explain plan for <<select query>>> followed by select * from FROM table(dbms_xplan.display()) is

so, in short the plan retrieved from PLAN table without executing the actual SQL will produce guess estimated plan. Explain plain lies and it' does not have any clue on bind peeking

Actual execution plan

- Run the query and fetch the plan from shared pool is actual execution plan

Example:

select count(*) from emp;

select * from table(dbms_xplan.display_cursor());

- the "Row Source" from TKPROF is actual execution plan

The actual execution never lies and its aware of data types of bind variables, bind variable peeking, adaptive cursor sharing etc etc.

Now, the confusion pops up when I read the below article

https://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

in literal terms, the above article says ( based on my understanding)

“The explain plan is what the optimizer thinks will happen when you run, the execution plan is actually happened when you ran the query”

Not sure my understanding is correct or I misunderstood the above article.


Connor McDonald
October 22, 2017 - 9:12 am UTC

Both (you and the blogpost) are stating essentially the same thing - it is just semantics here that can lead to confusion.

But ultimately yes - the *only* execution plan you can absolutely rely on is the one that was used to actually run your SQL statement.

AUTOTRACE : plan in V$SQL

David, August 25, 2018 - 7:46 am UTC

Hello,
I made my tests and, surprise, the explain plan generated by AUTOTRACE is in V$SQL_PLAN, not in PLAN_TABLE, like EXPLAIN does. Can you tell me why?

But I agree, execution plan generated by AUTOTRACE is not the real plan because AUTOTRACE and EXPLAIN PLAN are blind if I use bind variables.

David
Connor McDonald
August 28, 2018 - 1:34 am UTC

I did this in SQL Plus

SQL> set autotrace traceonly explain
SQL> alter session set events = '10046 trace name context forever, level 8';

Session altered.

SQL> select * from scott.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> exit


and you'll find this in the trace file

=====================
PARSING IN CURSOR #1265379229168 len=23 dep=0 uid=107 oct=3 lid=107 tim=667341359539 hv=52404428 ad='7ff9f65780e8' sqlid='ggqns3c1jz86c'
select * from scott.emp
END OF STMT
PARSE #1265379229168:c=15625,e=6027,p=0,cr=103,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=667341359539
WAIT #1265379229168: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=667341359623
WAIT #1265379229168: nam='SQL*Net message from client' ela= 144 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=667341359798
CLOSE #1265379229168:c=0,e=4,dep=0,type=0,tim=667341359833
=====================
PARSING IN CURSOR #1265379229168 len=45 dep=0 uid=107 oct=7 lid=107 tim=667341360021 hv=349105966 ad='7ff9f9278b10' sqlid='g72kdvcacxvtf'
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
END OF STMT
PARSE #1265379229168:c=0,e=162,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=667341360020
EXEC #1265378284408:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3845132125,tim=667341360175
FETCH #1265378284408:c=0,e=16,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=3845132125,tim=667341360212
CLOSE #1265378284408:c=0,e=1,dep=1,type=3,tim=667341360234
EXEC #1265378276472:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2570921597,tim=667341360266
FETCH #1265378276472:c=0,e=5,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=2570921597,tim=667341360283
CLOSE #1265378276472:c=0,e=1,dep=1,type=3,tim=667341360297


So it looks like we clean up any entries in the plan_table.

Great answer :-)

A reader, September 10, 2018 - 6:08 pm UTC


Hello Connors,

Thank you for your answers, I didn't notice the
"DELETE FROM PLAN_TABLE".

It is now much clear.

Have a nice day,

David

Confused bw autotrace and tkprof

Gautam, January 26, 2020 - 6:06 am UTC

Initially by the post of Tom it was clear that autotrace and trace has actual execution plan and explain has has the guessing plan.but now after reading till end Its saying autotrace and trace(tkprof) has also guess plan .

My question is ...does trace and autotrace have same plan which is actual execution plan?If not please clear.
Also I am assuming that explain plan provides us the guessing plan which is best suited to optimizer.please confirm if I am right here or not.

Thanks,
Gautam
Chris Saxon
January 27, 2020 - 1:32 pm UTC

The PLAN reported by autotrace in SQL*Plus is a prediction (guess). But the statistics (consistent gets etc.) are the actual values for that query.

There is also an autotrace tool in SQL Developer. This reports the REAL plan and its statistics.

TKPROF can provide the real plan and stats - provided this information is in the trace file itself.

So when does the plan change

Bhavesh, May 17, 2022 - 4:28 am UTC

Understood that plan can change during the REAL execution. When in the query execution cycle, does this happen. I assume that Oracle would start with the explain plan generated (the guess) and finds something and in the mid way finds another path to execute the query?
Chris Saxon
May 17, 2022 - 3:49 pm UTC

The plan is fixed during the parse phase. It can't change its mind part-way through execution and generate a new plan.

Explain plans do NOT run the query - this is why they're a guess. When you run the query "for real", it's parsed, generating the plan. Then it's executed.

This changes slightly with adaptive plans. With these, the optimizer creates a plan with two different join types (nested loops or hash join) - the database chooses which to use at runtime based on the rows processed. It's still constrained to the possible joins in the adaptive plan though.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.