dbms_xplan(3)

dbms_xplan(3)
Filed under: Execution plans — Jonathan Lewis @ 8:01 am UTC Mar 6,2008
http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/

Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor().

I’ve just seen a note on Rob van Wijk’s blog where he investigates many more parameter values that can be used with the function.


When I tried to add a comment to the blog listing a couple more of the options that I knew, I found that I couldn’t do so without creating a google or blogger account. So I’ve posted them here, and I’d be happy for someone with a suitable acccount to add them to his blog so that they’re all in the same place.

‘All’: Prints the Query block/Object Alias section, the Predicate information, and the Column Projection information after the basic plan.

‘Advanced’: as for ‘All’, but also include the Outline information (the set of hints that will reproduce the plan).

‘Outline’: prints just the Outline information and the Predicate information after the basic plan.

I got the ‘Advanced’ one from one of the Oak Table members - I think it may have been Christian Antognini - and guessed the ‘Outline’ one when I saw that it was possible to get the outline from the advanced output.
9 Comments »

1.

Oracle documentation explain about ‘ALL’ parameter, but not mentioned any thing about ‘Advanced’ and ‘Outline’. ORACLE_HOME/rdbms/admin/dbmsxpln.sql file has many good example for different procedure/ function and there parameters.
Nice to know 2 more parameter of display_cursor(), Thanks

Comment by Virag Sharma — March 6, 2008 @ 10:41 am UTC Mar 6,2008
2.

Why do you suppose Oracle are Parameterising their functions in such an unusual way?

Are they deliberately attempting to make performance tuning the obfuscated art of a closed society?

Why not add these options as pseudo-boolean type parameters and atleast allow them to be revealed by “describe” - if keeping the documentation up to date is too onerous?

Comment by Basil — March 6, 2008 @ 2:56 pm UTC Mar 6,2008
3.

Thanks Jonathan for letting me know and for making the list more complete.

I’ve added a small update section because of it.

Regards,
Rob.

Comment by Rob van Wijk — March 6, 2008 @ 10:36 pm UTC Mar 6,2008
4.

Basil,

I don’t think they’re trying to make life difficult. It’s probably more a case of “and here’s another good idea” and a quick bolt-on. Once you start doing it one way, it’s hard to re-engineer.

Personally I’d love it if the Statspack code allowed you to select which bit of functionality went into the snapshot by setting boolean flags - as it is I have to hack the code to get just the bits I want. (But at least the source is there for hacking).

Comment by Jonathan Lewis — March 11, 2008 @ 6:34 am UTC Mar 11,2008
5.

At least we have access to the spreport.sql script and can query the PERFSTAT schema
so that we can design modified versions of spreport.sql
Can’t do the same with awrrpt.sql at all ! It just calls a stored procedure which
“automagically” generates the report for us.
(BTW, have you used the “NO_OPTIONS=8″ to “ENABLE_ADDM”, modifying awrrpti.sql ?
Hemant

Comment by Hemant K Chitale — March 12, 2008 @ 7:20 am UTC Mar 12,2008
6.

[...] dbms_xplan in 10g Filed under: Execution plans, Hints, Tuning — Jonathan Lewis @ 9:17 pm UTC Nov 9,2006 [More in dbms_stats.display_cursor()] [...]

Pingback by dbms_xplan in 10g « Oracle Scratchpad — May 9, 2008 @ 3:33 pm UTC May 9,2008
7.

[...] dbms_xplan pt.2 Filed under: Execution plans, Infrastructure, Statspack, Troubleshooting — Jonathan Lewis @ 8:22 am UTC Nov 15,2006 [More on dbms_xplan.display_cursor()] [...]

Pingback by dbms_xplan pt.2 « Oracle Scratchpad — May 9, 2008 @ 3:36 pm UTC May 9,2008
8.

[...] changing input bind values. (With no change to the “peeked_binds” output from dbms_xplan.display_cursor() [...]

Pingback by Bind Capture « Oracle Scratchpad — July 24, 2008 @ 6:30 am UTC Jul 24,2008
9.

[...] is are a couple of great posts by Rob Van Wijk and Jonathan Lewis on DBMS_XPLAN.DISPLAY_CURSOR on their blogs. Also the Oracle Optimizer Development Group Blog has a [...]

Pingback by Kerry Osborne’s Blog » Blog Archive » Explain Plan Lies — October 15, 2008 @ 2:28 am UTC Oct 15,2008