dbms_xplan in 10g

dbms_xplan in 10g
Filed under: Execution plans, Hints, Tuning — Jonathan Lewis @ 9:17 pm UTC Nov 9,2006
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/

[More in 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 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.

[More in dbms_xplan.display_cursor()]
19 Comments »

1.

Jonathan,

I’ve been using dbms_xplan.display for a while now, but could never seem to get dbms_xplan.display_cursor to work, I can’t thank you enough for pointing out the ‘gather_plan_statistics’ hint, which was the key to getting it to work.

Keep up the blogging please!

Comment by John Scott — November 9, 2006 @ 11:50 pm UTC Nov 9,2006
2.

I have been using 10g for a while but didn’t know about this. Thank you!!!

Comment by Kirtan Desai — November 10, 2006 @ 4:25 am UTC Nov 10,2006
3.

well i knew about dbms_xplan but didn’t know about display_cursor.

Comment by Kirtan Desai — November 10, 2006 @ 4:26 am UTC Nov 10,2006
4.

Hi Jaffar,
This is Hitesh from India. I work as Oracle DBA Developer. Yes,It is a Great Blog for oracle professionals,But only thing ,It lacks AD potential,especially Google ADs,which i thought could provide more info about Oracle related stuffs.

Anyway,If you intend to insert google ADs,do mail me at hitesh222002@yahoo.co.in, and you can take the link from my blogsite.

Comment by Hitesh Shetty — November 10, 2006 @ 6:00 am UTC Nov 10,2006
5.

John, There are alternatives to the hint. Setting statistics_level = all, or _rowsource_execution_statistics = true, will enable the collection (and on some versions, just sql_trace = true does so as well). But the hint is the neatest.

Hitesh, I did consider Google Ads briefly last night; but there’s an awful lot of garbage about Oracle advertised on the internet, and it occurred to me that I wouldn’t want advertisements for garbage on my blog.

Comment by Jonathan Lewis — November 10, 2006 @ 7:21 am UTC Nov 10,2006
6.

This works particularly nicely in tools such as PL/SQL Developer where you can add your own tabs to the Session Browser window, and so you can define one named “Current Plan”, with the query as:

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR( :sql_id, :sql_child_number))

Now you can easily see the execution plan for any active session.

Comment by William Robertson — November 10, 2006 @ 4:37 pm UTC Nov 10,2006
7.

In Oracle 10g, dbms_xplan.display_awr can be used to get the historical information with respect to execution plans.

Jaffar

Comment by Jaffar Hussain — November 11, 2006 @ 7:19 am UTC Nov 11,2006
8.

[...] A comment on my previous posting about dbms_xplan pointed out that in 10g you also get the function dbms_xplan.display_awr that allows you to report historical execution plans from the AWR (automatic workload repository). [...]

Pingback by dbms_xplan pt.2 « Oracle Scratchpad — November 15, 2006 @ 8:23 am UTC Nov 15,2006
9.

I found that 10gR1 (at least on AIX) does not have the ALLSTATS LAST parameter, but includes RUNSTATS_LAST and RUNSTATS_TOT instead of ALLSTATS. The script documentation for 10gR2 says that these are included for backwards compatibility and are replaced by IOSTATS.

Comment by Daniel Fink — December 13, 2006 @ 11:20 pm UTC Dec 13,2006
10.

[...] Plans in Memory Filed under: Infrastructure, Performance, Execution plans — Jonathan Lewis @ 8:42 pm UTC Dec 12,2006 In an earlier article I described how dbms_xplan.display_cursor() could be used to query memory to find the execution plans (and row source statistics) of recently executed SQL. [...]

Pingback by Plans in Memory « Oracle Scratchpad — December 30, 2006 @ 8:26 pm UTC Dec 30,2006
11.

[...] — Jonathan Lewis @ 12:03 pm UTC Dec 22,2006 I’ve discussed the capabilities of the dbms_xplan package in a couple of posts already; and shown how useful it can be in two examples: understanding a [...]

Pingback by dbms_xplan - again « Oracle Scratchpad — February 1, 2007 @ 1:07 pm UTC Feb 1,2007
12.

[...] blogged before about the improved features in 10g of the dbms_xplan package, in particular the display_cursor() procedure, with its option for displaying rowsource execution [...]

Pingback by Heisenberg « Oracle Scratchpad — April 26, 2007 @ 9:15 pm UTC Apr 26,2007
13.

[...] Since this was a 10g machine, I hunted down the query (v$sql.sql_text like …) and called the dbms_xplan.display_cursor() procedure to show me the full plan. It looked a bit like [...]

Pingback by NLS « Oracle Scratchpad — July 26, 2007 @ 9:51 pm UTC Jul 26,2007
14.

[...] dbms_xplan in 10g « Oracle Scratchpad Oracle Scratchpad November 9, 2006 dbms_xplan in 10g Filed under: Execution plans, Tuning, Hints — Jonathan Lewis @ 9:17 pm UTC Nov 9,2006 If you’re using 9i and haven’t learned about the dbms_xplan package, then you should take a good l [...]

Pingback by 10g Application Oracle Server — September 13, 2007 @ 3:34 pm UTC Sep 13,2007
15.

[...] dbms_xplan(3) Filed under: Execution plans — Jonathan Lewis @ 8:01 am UTC Mar 6,2008 Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor(). [...]

Pingback by dbms_xplan(3) « Oracle Scratchpad — March 6, 2008 @ 8:02 am UTC Mar 6,2008
16.

[...] and “after” execution plans in 10.2.0.3 (pulled out of memory using the dbms_xplan.display_cursor() procedure) for an example of this type of [...]

Pingback by Cursor_sharing « Oracle Scratchpad — March 9, 2008 @ 9:14 pm UTC Mar 9,2008
17.

[...] >= 11 — Last M rows, typically a bind variable order by v2.rn ; Execution Plan (10.2.0.3 - dbms_xplan.display_cursor() edited to remove columns) [...]

Pingback by Manual Optimisation - 2 « Oracle Scratchpad — May 9, 2008 @ 3:44 pm UTC May 9,2008
18.

[...] dbms_xplan in 10g [...]

Pingback by Page hits « Oracle Scratchpad — September 7, 2008 @ 11:30 am UTC Sep 7,2008
19.

[...] in the documentation here, so for more detailed explanation reading Mr.Jonathan Lewis’s post here may be good starting [...]

Pingback by On Formating Treasures of Execution Plan Interpretation « H.Tonguç Yılmaz - Oracle Blog — September 30, 2008 @ 4:37 pm UTC Sep 30,2008