dbms_xplan in 10g

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 instroduced a new function in 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.

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.

A couple of odd notes - you’ll see that I set serveroutput off. If serveroutput is on when you call this function, the last statement you will have run will be the (hidden) call to dbms_output that follows your execution of any other statement - so you won’t get the plan and statistics.

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 only to exist 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.