dbms_xplan in 10g

[More on dbms_xplan.display_cursor()]

If you’re using 9i and haven’t learned about the dbms_xplan package, then you should take a good look at it right away. It’s (usually) a much better way of getting execution plans from your system than writing your own queries against the plan_table.

If you’ve been using dbms_xplan, and upgraded from 9i to 10g, make sure that you look at the new features – there are some things which are really useful, and this note is about just one of them.

dbms_xplan.display_cursor()

In 10g, Oracle introduced a new function in the package dbms_xplan that can read the in-memory execution plan (v$sql_plan and associated structures).  The call is the display_cursor() call, and takes three optional parameters, the sql_id and child_number of the SQL statement you want to see the plan for, and a formatting string.

The best bit of this function shows up when you look at the script that generates it($ORACLE_HOME/rdbms/admin/dbmsxpln.sql) when you decide to find out how to use the format parameter.  Here’s a “live” demo:

set serveroutput off        

select
	/*+
		gather_plan_statistics
		ordered use_nl(t1) index(t1)
	*/
	count(t1.n2), count(t2.n2)
from
	t2, t1
where
	t2.n2 = 45
and	t1.n1 = t2.n1
;        

COUNT(T1.N2) COUNT(T2.N2)
------------ ------------
         225          225        

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));        

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  98cw5a9c0pw33, child number 0
-------------------------------------
select  /*+   gather_plan_statistics   ordered use_nl(t1) index(t1)  */  count(t1.n2),
count(t2.n2) from  t2, t1 where  t2.n2 = 45 and t1.n1 = t2.n1        

Plan hash value: 3795562434        

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:00.01 |     146 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |     15 |    225 |00:00:00.01 |     146 |
|   3 |    NESTED LOOPS              |      |      1 |    225 |    241 |00:00:00.02 |     116 |
|*  4 |     TABLE ACCESS FULL        | T2   |      1 |     15 |     15 |00:00:00.01 |      99 |
|*  5 |     INDEX RANGE SCAN         | T_I1 |     15 |     15 |    225 |00:00:00.01 |      17 |
-----------------------------------------------------------------------------------------------        

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."N2"=45)
   5 - access("T1"."N1"="T2"."N1")        

If you want to call this function, you need access to several of the dynamic performance views: v$session, v$sql, v$sql_plan and v$sql_plan_statistics_all seem to cover all the options between them; and v$sql_plan_statistics_all is the most useful one.

As you can see in the query above, I’ve not supplied an sql_id or child_number, and I’ve used the format ‘ALLSTATS LAST’. By default display_cursor() reports the last statement I executed and the effect of ‘ALLSTATS LAST’ is to report the actual execution stats alongside the predicted row counts.  [Update: note that this is the stats for the last execution, if you omit “last” then you get the sum of stats for all the executions that took place while rowsource execution stats were enabled. I have seen a couple of notes suggesting that you need to use “allstats all” to do this (I may have written one myself) but the “all” option isn’t necessary and adds lots of other bits of information.]

If you’ve ever got into Wolfgang Breitling’s “Tuning by Cardinality Feedback” – you’ve now got a tool that can make it a lot easier.

Footnotes

• You’ll see that I set serveroutput off. If serveroutput is on when you call this function, the “last statement you ran” will be the (hidden) call to dbms_output that SQL*Plus runs after the execution of any other statement – so you won’t get the plan and statistics, you’ll see a note to the effect that Oracle “cannot fetch plan for SQL_ID: b3s1x9zqrvzvc, CHILD_NUMBER: 0”.
• The hint /*+ gather_plan_statistics */ enables collection of rowsource execution statistics for the statement. It’s a “nearly undocumented” hint, in that the documentation seems to exist only in odd places like the script that generates the package and as part of the output of the function call if the statistics weren’t enabled when you try to report statistics.
• You have to be a little careful comparing the actual and estimated row counts. They are not supposed to be the same in all cases. The estimated values are estimates for each execution of a rowsource, the actual values are the cumulative counts. So, for example, it is perfectly reasonable in line 5 to see E-rows = 15 and A-rows = 225, because line 5 Starts 15 times: so 225 actual rows = 15 starts * 15 estimated rows per start.

 

[More on dbms_xplan.display_cursor()]