ba6.us - Dave's Database Related Stuff

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