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.

Usage is;

DBMS_XPLAN.DISPLAY_CURSOR
(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’
);

code listing 4a

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.

code listing 4b

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
select …..

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 ;

DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’)

code listing 4c

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.

code listing 4d

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.

code listing 4e

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.

References ;

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)

About these ads