March 4, 2007

Viewing explain plan

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.

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.