Mohamed Houri’s Oracle Notes

April 20, 2011

DBMS_XPLAN versus Autotrace

Filed under: explain plan — hourim @ 5:18 pm

Since Oracle 10g, there is a nice BUILT-IN package named DBMS_XPLAN that gives a very nicely formatted explain plan. This blog article is just to show that you can still continue using the ‘set autotrace on’ SQL command in order to get your explain plan but you need to be aware that this SQL command use the estimated part of the DBMS_XPLAN package. In other words when you use autotrace Oracle is using behind the scene the  explain plan for plus select * from table (dbms_xplan. display).

Let’s see this in action:

mhouri> cl scr
mhouri> desc t

Name                    Null?    Type
----------------------- -------- ----------------
ID                               VARCHAR2(10)
NAME                             VARCHAR2(100)

mhouri> set linesize 150

mhouri> var x number

mhouri> exec : x :=99999

PL/SQL procedure successfully completed.

mhouri> explain plan for

2  select sum(length(name)) from t where id >  : x;

Explained.

mhouri> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1188118800
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    23 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |    23 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    58 |  1334 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I    |    11 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">:X)
15 rows selected.

mhouri> set autotrace on
mhouri> select sum(length(name)) from t where id >  : x;

SUM(LENGTH(NAME))
-----------------
10146

Execution Plan
----------------------------------------------------------
Plan hash value: 1188118800
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    23 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |    23 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    58 |  1334 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I    |    11 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">:X)

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

mhouri> set autotrace off

mhouri> select sum(length(name)) from t where id >  : x;

SUM(LENGTH(NAME))
-----------------
10146

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  7zm570j6kj597, child number 0
-------------------------------------
select sum(length(name)) from t where id >  : x
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    23 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    59 |  1357 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("ID")>:X)

19 rows selected.

Advertisements

2 Comments »

  1. So,
    You mean to say that explain plan either by dbms_xplan or autotrace displayed what CBO thought but not that it actually used.
    Thats why the explain plan uses the index but execution plan does a full scan.

    Comment by Yasir Hashmi — May 10, 2011 @ 10:06 am | Reply

  2. Yasir,

    There are several ways to get the explain plan of a query

    (a) ‘explain plan for (select * from emp)’; then ‘select * from table(dbms_xplan.display);’
    (b) set autotrace on
    (c) ‘select * from emp;’ then ‘select * from table(dbms_xplan.display_cursor)’;

    (a) and (b) are equivalnet. They,both, give the estimated explain plan which might be different from the real explain plan followed by the SQL engine to execute the query.

    If you want to rely on the real explain plan used by a query then you have no other way than to use (c).

    What makes point (a) and (b) approximative is that they consider all bind variables as of a varchar2 data type. And this is why an implicit conversion like to_number might occur in the real explain plan making the CBO ignoring the index.

    Hope this helps

    Comment by hourim — May 10, 2011 @ 6:09 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: