-
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.
When the going gets tough, the tough gets going
-
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.
When the going gets tough, the tough gets going
-
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
Rajeev Suri
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|