Three ways to get a plan with dbms_xplan

Click here to get a zip of the three different scripts I talk about in this post.

There are two different functions in the Oracle supplied package DBMS_XPLAN that I use all the time for tuning SQL queries, DBMS_XPLAN.DISPLAY and DBMS_XPLAN.DISPLAY_AWR.

DBMS_XPLAN.DISPLAY extracts a query’s plan from a plan_table, which is populated by running the EXPLAIN PLAN statement on the query.  I use the script fullplan.sql to run DBMS_XPLAN.DISPLAY.  This script is great for a long running SQL statement when you just want to get a plan and not wait for the SQL to run.  If you are trying different ways to improve the statement and if you know what a fast plan looks like for the statement it is nice to just get the plan of your various attempted fixes.

DBMS_XPLAN.DISPLAY_AWR extracts the plan for a particular query that ran in the past.  We keep six weeks of AWR history so I can go back and find the plan of a problem query as it ran this week and as it ran a month ago.  Many times the plan has changed and then I just need to find out why and cause the query to use the original faster plan.  I use the script getplans.sql to call DBMS_XPLAN.DISPLAY_AWR.

One DBMS_XPLAN function I don’t use much, and probably should, is DBMS_XPLAN.DISPLAY_CURSOR.  This shows the plan of a SQL that is still cached in the shared pool.  The great thing about DISPLAY_CURSOR is that it shows you the plan the query really used when it ran and not the output from explain plan which doesn’t run the query.  Many times these are the same, but there are cases where they are different.  The files test.sql and test.log show how to use DBMS_XPLAN.DISPLAY_CURSOR to run a query and then get its plan.

My typical tuning process is to use getplans.sql to extract historical plans and then fullplan.sql to try different fixes and then run the modified and original queries to compare their run times.  But, in addition to the run time it is nice to see the plans used during the before and after tests to verify that the fix changed the plan as expected.

Right now when I test a query to see how long it runs and to get its plan I usually just use these sqlplus statements:

set autotrace on
set timing on

These give you a plan, timing, and some statistics about disk and memory block reads:

SQL> SELECT * from dual;

D
-
X

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
1  0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        201  bytes sent via SQL*Net to client
        277  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Set autotrace on runs an EXPLAIN PLAN statement against the query to generate an estimated plan so it doesn’t necessarily show you the plan your query really runs.  It also shows you some statistics about the way the query actually ran.  With DBMS_XPLAN.DISPLAY_CURSOR you can extract the actual plan after the query runs and it shows you statistics about how each step of the plan really ran.

SQL> SELECT /*+gather_plan_statistics*/ * from dual;

D
-
X

SQL>
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * 
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

SQL_ID  266x2hrt9mwtp, child number 0
-------------------------------------

SELECT /*+gather_plan_statistics*/ * from dual

Plan hash value: 3543395131

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| DUAL |      2 |      1 |      2 |00:00:00.01 |       6 |
------------------------------------------------------------------------------------

12 rows selected.

This shows the actual rows, actual time, and buffers for the one step of this simple plan.  I had to add the gather_plan_statistics hint to get these statistics.

Also, like DBMS_XPLAN.DISPLAY, DBMS_XPLAN.DISPLAY_CURSOR shows the predicates.  In the above example there is no where clause and hence no predicates.  But if we add a predicate like this:

SELECT /*+gather_plan_statistics*/ * from dual where dummy <> ' ';

Then you get the predicate from display_cursor:

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

   1 - filter("DUMMY"<>' ')

Note that display_awr doesn’t give you the predicates, but you can’t have everything!

Time will tell if using DBMS_XPLAN.DISPLAY_CURSOR to run tests on queries and get their plan will replace my current method of using autotrace but it looks promising.  I’ve had a lot of success with DBMS_XPLAN.DISPLAY and DBMS_XPLAN.DISPLAY_AWR so it stands to reason that all three will work together to provide me the best methods to get execution plans for Oracle query tuning.

- Bobby

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to Three ways to get a plan with dbms_xplan

  1. J says:

    You say ” set autotrace on just does an explain plan on the query” but my understanding is this:
    1. “autotrace on” – same as “autotrace on explain statistics”
    2. “autotrace explain” – explain is a guess at the likely execution plan
    3. “autotrace statistics” – statistics are actual facts from running the query
    therefore “autotrace on” will give you a guessed execution plan and actual statistics.
    I think therefore that your statement could do with some refinement :-)

    • Bobby says:

      Thanks for your comment. I changed the text from:

      But set autotrace on just does an explain plan on the query so it doesn’t necessarily show you the plan your query really runs. But with DBMS_XPLAN.DISPLAY_CURSOR you get the real plan and it shows you statistics about each step of the plan:

      To:

      Set autotrace on runs an EXPLAIN PLAN statement against the query to generate an estimated plan so it doesn’t necessarily show you the plan your query really runs. It also shows you some statistics about the way the query actually ran. With DBMS_XPLAN.DISPLAY_CURSOR you can extract the actual plan after the query runs and it shows you statistics about how each step of the plan really ran.

      Is that more clear?

      Thanks again for your input.

      - Bobby

  2. Philippe says:

    Thanks I was looking for what exactly the time we are getting is. Is that the time it will take for the query to get the output or it is an estimate. I have some stale partitions I get the explain plan I have the same plan after and before gathering the stats. I was confused

    • Bobby says:

      Philippe,

      DISPLAY_CURSOR shows the actual time if you use the gather_plan_statistics hint and the ALLSTATS parameter value.

      As for partition stats, in my testing I’ve only seen partition stats used when only one partition is being accessed. I.e. If you have a range partitioned table with run_dt as the column then if you have a date range that falls in one partition it will use the partition stats. If the date range crosses two or more partitions it will use the global stats only and gathering stats at the partition level wont change the plan.

      - Bobby

Leave a Reply