Explain Plan
Submitted by dmann 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