explain plan

Hi ,
Can anybody provide me with a brief desciption about the explain plan what is it and how to use it ? Any example will be of great help.
thankx in advance.

Hi Akhil,
Basically explian plan is used to find out how Oracle will execute the query.It will show whether indexes will be used or not and incase if it uses the index it will show which indexes are being used or whether the query is performing full table scans or not.
In order to get an explain plan for a query u have to create first the plan table.The plan table is created by running the utlxplan.sql.Run this script and create the plan table.
After u create the plan table,run the follwing query to generate the explain plan


sql> explain plan
set statement_id='A'
for ;

explained

sql> Run the following query to get the explain plan for the
query

select lpad(' ',2*level)
||operation||' '||options||' '||object_name
Execution_path from plan_table where
statement_id='&statementid'
connect by prior id=parent_id and
statement_id='A'
start with id=1;


e.g. Explain plan for a sample query
EXECUTION_PATH
--------------------------------------------------------------------------------
NESTED LOOPS
TABLE ACCESS FULL AP_REQ_LETTER
TABLE ACCESS BY INDEX ROWID M_USER_MAST
INDEX UNIQUE SCAN M_USER_CODE_IDX


In case of any help please be free to ask me at rohitsn@altavista.com

Regards,
ROhit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

Hi rohit,
Thankx for the reply. Everything is fine except the
sql> explain plan
set statement_id='A'
for ;

explained
The above statement does not works fine.
Thanks for the help.

That's because he enclosed some text in < and > symbols. Since this forum is HTML based, it thinks that is some sort of HTML tag (which it isn't) and doesn't display the text within it.

What rohitsn was trying to get displayed was:

sql> explain plan
set statement_id='A'
for <query>;

HTH,

Heath

to see the explain plan result in sql/plus --

set autotrace on

Run the query and it will display the query result and the execution path and the statistics; Example --


SQL> set autotrace on
SQL> select * from test where id < 20;

ID NAME CITY DT1
--------- --------------------------------------------- --------- ---------
1 1 1 10-APR-01
1 3 3 10-APR-01
7 test7 7 10-APR-01
8 test8 8 10-APR-01
9 test9 9 10-APR-01
11 test for 11

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST' (NON-UNIQUE)




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


- Rajeev