Explain Plan Explained - 9i Release 2 |
 |
EXPLAIN PLAN Syntax:
|
EXPLAIN PLAN
[ SET STATEMENT_ID = 'statement_id' ]
[ INTO table_name ]
FOR sql_statement;
|
The default plan table is PLAN_TABLE and is created by running $ORACLE_HOME/rdbms/admin/utlxplan.sql
|
9i Release 2 provides DBMS_XPLAN which uses pipelined functions to query a plan table as follows:
|
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY(
'plan_table_name',
'statement_id',
'format_keyword'
));
format_keyword: [ BASIC | TYPICAL | ALL | SERIAL ]
- Specify amount of detail
- SERIAL is used when TYPICAL output is desired without parallel information
|
For pre - 9i releases, querying the plan_table directly is used:
|
SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name ||
DECODE(id, 0, 'Cost = '|| positions) "Execution Plan"
FROM plan_table_name
START WITH ID = 0 AND STATEMENT_ID = 'statement_id'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'statement_id';
|
Statistics are required to correctly compute cost!
|