Coskan’s Approach to Oracle

March 15, 2007

Viewing plans of old SQLs

Filed under: How To — coskan @ 4:37 pm

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 which helps you to view plan of an old sql. . If you have license for tuning pack and diagnostics pack you can get historical information about the old SQLs which ran on your database. For more info about licensing feature of these packs refer to the Oracle® Database Licensing Information 10g Release 1 (10.1) manual

DBMS_XPLAN.DISPLAY_AWR displays the contents of an execution plan stored in the AWR.

Syntax is;

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

If db_id paramater is not specified the function will use the id of the local db.

If you don’t specify plan_hash_value parameter, function will bring all the stored execution plans for the given sql_id

Format parameter have so many capabilities you can get the list from the manual.

Simple demonstration ; (all tests are done with 10.2.0.1 Express Edition)

code listing 6a

You can also use DBA_HIST_SQL_PLAN table for viewing the historic plan info.

code listing 6b

References Used ;

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

Oracle® Database Reference 10g Release 2 (10.2)

About these ads

2 Comments »

  1. Another feature DISPLAY_CURSOR function with ‘RUNSTATS_LAST’ parameter, it is to indicate that we wish to view the statistics for this execution of the cursor only (a cumulative ‘RUNSTATS_TOT’ is also available).

    SQL> SELECT plan_table_output
    2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,’RUNSTATS_LAST’));

    PLAN_TABLE_OUTPUT
    —————————————————————————————————
    SQL_ID 5rrm60t57x14g, child number 0
    ————————————-
    SELECT /*+ ORDERED */ t1.object_type , COUNT(*) AS object_count FROM t1
    , t2 , t3 WHERE t1.object_id = t2.object_id AND t2.object_id =
    t3.object_id GROUP BY t1.object_type

    Plan hash value: 176539994

    ———————————————————————————————–
    | Id | Operation | Name | E-Rows | A-Rows | Buffers | Reads | Writes | A-Time |
    ———————————————————————————————–
    | 1 | SORT GROUP BY | | 27 | 3 | 1344 | 700 | 196 |00:00:03.42 |
    | 2 | NESTED LOOPS | | 100 | 100 | 1344 | 700 | 196 |00:00:03.42 |
    |* 3 | HASH JOIN | | 48447 | 48447 | 1342 | 700 | 196 |00:00:02.48 |
    | 4 | TABLE ACCESS FULL| T1 | 48447 | 48447 | 671 | 424 | 0 |00:00:00.41 |
    | 5 | TABLE ACCESS FULL| T2 | 48448 | 48448 | 671 | 80 | 0 |00:00:00.47 |
    |* 6 | INDEX RANGE SCAN | IT3 | 1 | 100 | 2 | 0 | 0 |00:00:00.54 |
    ———————————————————————————————–

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$1
    4 – SEL$1 / T1@SEL$1
    5 – SEL$1 / T2@SEL$1
    6 – SEL$1 / T3@SEL$1

    Predicate Information (identified by operation id):
    —————————————————

    3 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
    6 – access(“T2″.”OBJECT_ID”=”T3″.”OBJECT_ID”)

    33 rows selected.

    Oracle provides six high-level runtime statistics as follows:

    * E-Rows: estimated rowcounts flowing through each plan step;
    * A-Rows: the actual rowcounts flowing through each plan step;
    * Buffers: reads from the buffer cache (LIO);
    * Reads: physical reads (PIO);
    * Writes: physical writes (e.g. to temp); and
    * A-Time: elapsed time of each plan step.

    Comment by H.Tonguç Yılmaz — July 26, 2007 @ 6:07 am

  2. Thanks for the comment Tonguc.

    These format options are said to be deprecated for 10Gr2 (http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i998364)

    IOSTAT is used instead of these options. An addition to your comment can be , If “STATISTICS_LEVEL” of database or the specific session is not ALL then we can’t use this format options. If we use it without statistics_level=all, then we will face with the warning below

    NOTE: cannot fetch plan for SQL_ID bk3rxmd68yd44, CHILD_NUMBER: 0
    Was STATISTICS_LEVEL set to ALL for the session that run the statement ?

    Comment by coskan — July 26, 2007 @ 6:34 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

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers

%d bloggers like this: