Explain Plan

Submitted by dave on Fri, 10/03/2008 - 17:56

1) Run $ORACLE_HOME/rdbms/admin/utlxplan.sql to create a plan_table in your schema.

2) Explain the statement...

TRUNCATE TABLE plan_table;
EXPLAIN PLAN SET STATEMENT_ID='&name' FOR [ SELECT STATEMENT ];

3) View the output:

@$ORACLE_HOME/rdbms/admin/utlxplps

- or -

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic'));

- or -

SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' ||
object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other
FROM plan_table
START WITH id = 0 AND statement_id='&name'
CONNECT BY PRIOR ID = PARENT_ID AND statement_id = '&name';

References:
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci976623,00.html
http://www.dbspecialists.com/presentations/use_explain.html