Oracle Scratchpad

November 15, 2006

dbms_xplan pt.2

Filed under: dbms_xplan,Execution plans,Infrastructure,Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 8:22 am GMT Nov 15,2006

[More on dbms_xplan.display_cursor()]

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).

This is true, and there is yet another function dbms_xplan.display_sqlset which allows you to report execution plans from SQL Tuning Sets if you have been using the Automatic SQL Tuning Tools. But to use these functions you do need to purchase the Tuning Pack licence and the Diagnostic Pack licence.

There is also an enhancement to the basic dbms_xplan.display function itself, which can now take a fourth parameter called filter_preds. This could be quite useful if you decide to build a permanent library of statements with their executions plans – perhaps using a clone of the plan_table to hold the plans, using something like the hash_value combined with the plan_hash_value as the statement_id.

You could even consider using it to query the perfstat table stats$sql_plan if you regularly take statspack snapshots at level 6 – which capture the plans from v$sql_plan for the sql statements captured by level 5. For example, the following call would report the execution plan of a statement with a plan_hash_value of 3992920156:

select *
from	table(

This isn’t really an ideal way of reporting execution plans from the statspack tables though. For a start, you have to find a convenient way of collecting the plan_hash_value before you get to it.

If you want to make best use of snapshots at level 6, then you really need to know the script sprepsql.sql (and for users of the AWR (automatic workload repository) there is the awrsqrpt.sql script) in the $ORACLE_HOME/rbdbms/admin subdirectory.

This script takes as its inputs the snapshot ids for a start and end snapshot, plus a hash_value – which you would have picked up from the “Top SQL” sections of a normal statspack report.

The output is a summary of the cost of the statement for the period covered by the snapshot, and a simplified listing of any execution plans for the statement that existed in v$sql_plan in that period. [Following a comment by Mathew Butler below – for “cost” read “activity and resource consumption”]


I have recently heard of a bug in v$sql_plan in earlier versions of 9.2 that causes queries against v$sql_plan to hang the database (jamming it on one of the library cache latches) with an instance restart being the only recovery option. Essentially this happens if you have a sufficiently complex statement in v$sql_plan when you query it. I haven’t checked the details yet, but “sufficiently complex” may mean something like “more than 255 predicates”. This is fixed, apparently by

Footnote 2

Statspack doesn’t collect the filter_predicates and access_predicates (or other_xml) columns from v$sql_plan. So it does lose some critical detail.

Footnote 3:

The 10g version of the script that creates the dbms_xplan package contains a warning that the filter_preds parameter allows “SQL Injection” to take place.  (See Pete Finnigan’s website for everything you need to know about this, and other, Oracle security topics). In this case, I don’t think it’s a really a threat since the package is created with authid current_user, so you won’t be able to use SQL injection through the base package to do anything that your account couldn’t normally do. However, if you are a DBA and wrap your own dbms_xplan package around the supplied package – to hide the display_awr, display_sqlset and display_cursor calls, for example – then you must remember to create your own package as authid current_user, or it will be possible for anyone you allow to call your package to do almost anything that your DBA privileges allow you to do.

[More on dbms_xplan.display_cursor()]


  1. This is a thought I had when I read this part of your post:

    The output is a summary of the cost of the statement for the period covered by the snapshot, …

    I wondered what value a “summary of the cost of the statement for the period covered by the snapshot” might have. I think it suggests that the plan may have changed, but it might not have. I don’t think that there is anything else to conclude from this summary. What are your thoughts?

    This then reminded me of a colleague I heard of recently who was carrying out a tuning exercise. As part of their analysis they were considering the optimizer cost values as a criteria for identifying SQL statements that might require tuning.

    When I heard this I thought of two things; this comment from Wolfgang Brietling:

    which is to the effect of;

    – you cannot compare the relative costs of a specific query and reliably learn anything about it’s performance
    – it is possible to have two execution plans for the same SQL, the first with a lower cost than the second and yet with the most expensive execution plan performing better.
    ( see Wolfgang Breitlings paper as for an example of this – the section covering the first diagram )

    I understood Wolfgangs comments to mean plan cost != time. However, I understood from your CBO book and the Oracle documentation that the cost is the optimizers best estimate of the time that a query plan will take to execute.

    Initially, I thought that these were conflicting ideas, but they are not. The optimiser strives to achieve cost = time, but stumbles sometimes when the assumptions that it has to make about the data are not true. In this case, the optimisers assumptions produce an inflated cost but at runtime the query is revealed as more efficient than the optimiser calculated. At runtime the data volumes to be processed at each stage of the plan are significantly different to those expected and such that the volume and structure of the data play to the strengths of the more expensive execution plan.

    I think this is a subtle idea to grasp.

    Comment by Mathew Butler — November 15, 2006 @ 11:33 am GMT Nov 15,2006 | Reply

  2. I forgot to say that I am really enjoying your blog.

    Best Regards,

    Comment by Mathew Butler — November 15, 2006 @ 12:06 pm GMT Nov 15,2006 | Reply

  3. Mathew, Thanks for the comment. I have modifed the original statement to point out that I should have said “resource consumption” rather than cost. (The double meaning and consequent confusion is something I stress ni the first chapter of the book – it’s a little embarassing that I then fell into the trap myself).
    The AWR version of the report does better than the statspack version, as it is reports the resource usage for each different execution plan separately; whereas the statpack report simply sums across across every plan. (Although both reports do give a version count, i.e. number of different child cursor found in memory, hence upper limit of the number of execution plans that might exist in the report).

    Comment by Jonathan Lewis — November 15, 2006 @ 12:18 pm GMT Nov 15,2006 | Reply

  4. […] In fact I was aware of it, but had completely forgotten about it. So at the end of the day I did a quick search on Metalink to see if I could find a reference. It looks like bug 3778541, opened on 20th July 2004, last updated 20th July 2006 (with predicted fix in version – so that looks like a long lifetime for 9.2 ;-)).  However this bug is, in fact, reported as fixed in the patch list for […]

    Pingback by Statspack level 6 « Oracle Scratchpad — November 23, 2006 @ 7:59 pm GMT Nov 23,2006 | Reply

  5. Jonathan,

    According to metalink, the bug was fixed in 9207.


    Comment by Jaffar — December 16, 2006 @ 12:09 pm GMT Dec 16,2006 | Reply

  6. Jonathan,

    Somewhat out of the way question. Is Predicate information not stored in AWR repository ? I haven’t been able to get that even with display_awr(‘sql_id’,null,null,’ALL’).


    Comment by Amardeep Sidhu — February 28, 2012 @ 11:08 am GMT Feb 28,2012 | Reply

  7. Hi,
    If you want to have the predicates collected by AWR and Statspack, you can vote for the OTN idea about it: and we will see if it helps.

    Comment by @FranckPachot — November 14, 2014 @ 9:14 am GMT Nov 14,2014 | Reply

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by