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