There are lots of ways to view explain plan of the sql which is running or which you are going to run.
For the sqls which are already running or which were run;
1- Using DBMS_XPLAN package;
You can use DISPLAY_CURSOR procedure of DBMS_XPLAN package to view execution plans of running sqls. You need two parameters to use this package. First parameter is sql_id and second parameter is child number of the preceding statement. For getting the sql_id and child number you can use v$sql or v$session views.
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’
2- Using v$sql_plan view
You can query v$sql_plan view to get the execution plan of given sql_id or hash value.
For the sqls which you are going to run;
1- Using DBMS_XPLAN package
You can use DISPLAY procedure of DBMS_XPLAN package. Firts you execute and explain plan command for the query you are executing. Then you issue a select command like below;
explain plan for
then select the result of the last EXPLAIN PLAN command stored in the plan table
select * from table (DBMS_XPLAN.DISPLAY)
parameters for the procedure ;
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’)
2- Using autotrace traceonly command;
You can use autotrace traceonly clause for getting the explain plan for the queries you run after executing this command. But the plan of the query will be shown after the execution of the query. Autotrace traceonly command will also give details about the i/o ,network and sort usage of the execution.
2- Using autotrace traceonly explain clause;
If you want to see only the execution plan without executing the query you can execute the command autotrace traceonly explain.
Note; On 10G Release 2 the autotrace output is the same with dbm_xplain package but prior to 10G R2 autotrace output was not displayed with cell environment.
Tests are done on 10.2.0.1 Express Edition
Please take a look at Mr Kytes Blog about when the real plan differ from guessed plan. This entry may save your life.