Coskan’s Approach to Oracle

March 4, 2007

Viewing explain plan

Filed under: How To — coskan @ 10:27 pm

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

2 Comments »

  1. [...] March 15th, 2007 in Uncategorized Previously I wrote about how to view a plan of a sql. Today I will tell you about a good feature DBMS_XPLAN.DISPLAY_AWR function comes with Oracle 10G [...]

    Pingback by viewing plans of old SQLs « Coskans Approach to Oracle — March 15, 2007 @ 4:37 pm

  2. Note that to use the DISPLAY_CURSOR functionality, the calling user must have
    SELECT privilege on V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise
    it will show an appropriate error message.

    Comment by coskan — September 24, 2007 @ 11:23 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 205 other followers

%d bloggers like this: