• Certifications

  • Moderator at

  • Member at

  • Archives

  • May 2011
    M T W T F S S
    « Dec   Jun »
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Recent Posts

  • Blog Stats

    • 62,627 hits
  • Enter your email address to follow this blog and receive notifications of new posts by email.

How to read Oracle Explain Plan

Posted by sriram on May 10, 2011


The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer.A statement’s execution plan is the sequence of operations Oracle performs to run the statement.execution plans are read inside-out.if there are two statements at the same level, the first statement is executed first.

You can use “Explain plan statement for this.” which should be explained first.
The basic syntax to get explain plan is explain plan set statement_id= for

For Ex:
ind> explain plan set statement_id=’ORAFAQ’ for
2 select
3 a.empno,
4 a.ename,
5 b.dname
6 from
7 emp_sriram a,
8 dept b
9 where
10 a.deptno=b.deptno
11 /

Explained.
Now the sequential steps of this sql statement is stored in a Table called PLAN_TABLE.

Lets have a look at the table.

ind> select LPad(‘ ‘, 2*(Level-1)) || Level || ‘.’ || Nvl(Position,0)|| ‘ ‘ ||
2 Operation || ‘ ‘ || Options || ‘ ‘ || Object_Name || ‘ ‘ || Object_Type
3 || ‘ ‘ || Decode(id, 0, Statement_Id ||’ Cost = ‘ || Position) || cost
4 || ‘ ‘ || Object_Node “Query Plan”
5 from plan_table
6 start with id = 0 And statement_id=’ORAFAQ’
7 connect by prior id = parent_id
8 and statement_id=’ORAFAQ’
9 /

Query Plan
———————————————————————————————–
1.4 SELECT STATEMENT ORAFAQ Cost = 44
2.1 NESTED LOOPS 4
3.1 TABLE ACCESS FULL EMP_SRIRAM TABLE 3
3.2 TABLE ACCESS BY INDEX ROWID DEPT TABLE 1
4.1 INDEX UNIQUE SCAN PK_DEPT INDEX (UNIQUE) 0

5 rows selected.

You can Also Use DBMS_XPLAN.DISPLAY.
ind> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’ORAFAQ’,’BASIC’));

PLAN_TABLE_OUTPUT
———————————————————————————-
Plan hash value: 2868360194

—————————————————
| Id | Operation | Name |
—————————————————
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS FULL | EMP_SRIRAM |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 4 | INDEX UNIQUE SCAN | PK_DEPT |
—————————————————

11 rows selected.

The basic structure of this package is
dbms_xplan.display(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’);

Format choices are
BASIC ….. displays minimum information
TYPICAL … displays most relevant information
SERIAL …. like TYPICAL but without parallel information
ALL ……. displays all information

SQL plus autotrace also produce the explain  plan …..

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
Setting autotrace allows to display some statistics and/or an query execution plan for DML statements.
set autotrace on:	Shows the execution plan as well as statistics of the statement.
set autotrace on explain:	Displays the execution plan only.
set autotrace on statistics:	Displays the statistics only.
set autotrace traceonly:	Displays the execution plan and the statistics (as set autotrace on does), but doesn't print a query's result.
set autotrace off:	Disables all autotrace

Now lets have a look at execution plan order ….

ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,
2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),
3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||
4 NVL(position, 0) “ORDER”, SUBSTR(optimizer,1,6) “OPT”
5 from plan_table
6 start with id = 0
7 and statement_id=’ORAFAQ’
8 connect by prior id = parent_id
9 and statement_id=’ORAFAQ’;

OPERATION OPTIONS OBJECTNAME ORDER OPT
—————————— —————————— —————————— ——-
SELECT STATEMENT COST = 4 0-0-4 ALL_RO
NESTED LOOPS 1-0-1
TABLE ACCESS FULL EMP_SRIRAM 2-1-1 ANALYZ
TABLE ACCESS BY INDEX ROWID DEPT 3-1-2 ANALYZ
INDEX UNIQUE SCAN PK_DEPT 4-3-1 ANALYZ

5 rows selected.

ind> select LPAD(‘ ‘,2*(LEVEL-1))||operation “OPERATION”, options “OPTIONS”,

2 DECODE(TO_CHAR(id),’0’,’COST = ‘ || NVL(TO_CHAR(position),’n/a’),

3 object_name) “OBJECTNAME”, id ||’-‘|| NVL(parent_id, 0)||’-‘||

4 NVL(position, 0) “ORDER”

5 from plan_table

6 start with id = 0

7 and statement_id=’ORAFAQ’

8 connect by prior id = parent_id

9 and statement_id=’ORAFAQ’;

OPERATION OPTIONS OBJECTNAME ORDER

—————————— —————————— —————————— ——-

SELECT STATEMENT COST = 4                      0-0-4

NESTED LOOPS                                                      1-0-1

TABLE ACCESS FULL EMP_SRIRAM             2-1-1

TABLE ACCESS BY INDEX ROWID DEPT      3-1-2

INDEX UNIQUE SCAN PK_DEPT                        4-3-1

5 rows selected.

On  0-0-4  it is in an order like ID and parent id nad its position.

Like wise if we can order them

00
10
21
31
43
here Level 1 has 2 childs(2,3) and  3 has 1 child(4).
So the execution steps sequential order will be 2,4,3,1
i.e
TABLE ACCESS               FULL                           EMP_SRIRAM  
INDEX                    UNIQUE SCAN                    PK_DEPT
TABLE ACCESS               BY INDEX ROWID                 DEPT  
Produce the result
SELECT STATEMENT Indicates that its a "SELECT STATEMENT"

Here First it will read data from emp_sriram then a unique Index scan  on dept (step 4) 
which gives the rowids and based the rowid input it goes to the step 1
(we can say for understanding) and produce the result.

For More Information Please have a look at Oracle documents.
Advertisements

2 Responses to “How to read Oracle Explain Plan”

  1. sales said

    I hardly comment, but i did a few searching and wound up here How to read Oracle Explain Plan |.

    And I actually do have some questions for you if you tend not to mind.
    Could it be simply me or does it look like some of these responses come across
    like they are written by brain dead visitors?
    😛 And, if you are writing on additional sites, I’d like to follow anything new
    you have to post. Would you list of every one of all your community sites
    like your Facebook page, twitter feed, or linkedin profile?

  2. great site said

    great site

    How to read Oracle Explain Plan «

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

 
Puddintopia

The Ramblings and Ravings Of Author Jason A. Rust

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.

flashdba

Oracle databases, storage and the high-performance world of flash memory

jarneil

The thoughts of Jason Arneil

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

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

Martins Blog

Trying to explain complex things in simple terms

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Talip Hakan Ozturk's ORACLE BLOG

The secret of success is at your fingertips!...

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

Richard Foote's Oracle Blog

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

Oracle Scratchpad

Just another Oracle weblog

Oracle DBA Blog

Procedures, learnings and fixes encountered by a Junior DBA

Oracle ACE Director Kai Yu's Oracle Blog

Share Oracle Tips and Tricks and Related News

Online Identity & Access Management

One Stop Shop for Identity & Access Management

Uwe Hesse

about Database Technology

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Support

WordPress.com Support

Kamran Agayev's Oracle Blog

Oracle Certified Master

%d bloggers like this: