Oracle Scratchpad

December 12, 2006

Plans in Memory

Filed under: dbms_xplan,Execution plans,Infrastructure,Performance — Jonathan Lewis @ 8:42 pm GMT 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.

In 9i, you have to use your own SQL to get the equivalent results. If you do, you should avoid using the “traditional” hierarchical type of query that you would use against the plan table, as it can be a little brutal on the library cache latches. Instead, you query the v$sql_plan view by hash_value and child_number with a simple “order by id” using the new, internally calculated, depth column instead of the psuedo-column level as the means of indenting the operations.

If you do want to carry on using the hierarchical style of query – and in 10g, I’ve found a couple of cases where the depth is calculated incorrectly, so you may want to keep some of your old scripts handy for a while – you should think about using  subquery factoring – for example (with just a very simple and far from complete  plan output):

with plan_subq as (
                /*+ materialize */
                operation || ' ' ||
                object_name || ' ' ||
                options                 simple_plan
                hash_value = &m_hash_value
        lpad(' ',2 * level) || simple_plan simple_plan
start with
        id = 0
connect by
                parent_id    = prior id
        and     child_number = prior child_number
order by

With this strategy, you are protecting the library cache latches, but remember that Oracle will be creating a global temporary table (and dumping it to your temporary tablespace) as a consequence.


  1. Jonathan, Tom Kyte published a way to use dbms_xplan.display on v$sql_plan in 9i in his book.
    You can find an excerpt at
    I don’t know its impact on library cache latches but it is a convenient method.

    Comment by Michel Cadot — December 13, 2006 @ 8:19 am GMT Dec 13,2006 | Reply

  2. I like to get the access and filter predicates out too:

    select id,decode(access_predicates,null,’filter: ‘||filter_predicates,’access: ‘
    ||access_predicates) predicates
    from v$sql_plan
    where (access_predicates is not null or filter_predicates is not null)
    and hash_value = &&m_hash_value

    Comment by Andy Helm — December 13, 2006 @ 9:41 am GMT Dec 13,2006 | Reply

  3. Michel, it’s interesting what a simple posting can produce as follow-up (looking at the thread).
    Tom’s solution is a very convenient “developer solution”, but I wouldn’t want to use it for a heavy trouble-shooting session on a big system because it does hammer the library cache latches – as Tom points out – especially since it starts (implicitly) with a scan of v$sql to find the information needed, and then accesses v$sql_plan by address tather than hash_value.

    It also has the defect that a single text in v$sql can be repeated with many child cursors, which means that the query used for the second parameter to dbms_xplan.display could return multiple rows, presumably crashing – although I note a later poster seems to survive an array return for the second parameter and gets a multiply-cloned plan as a result.

    There’s also a follow-on about joining to the correct child in v$sql – not possible directly from v$session, or even v$open_cursor in the earlier versions but x$kgllk (which is underneath v$open_cursor) includes a column that will get you to v$sql (in 9i, and x$kglcursor in 8i, which is the thing underneath v$sql) to pick you the correct child. I’ll have to write that up some time.

    Comment by Jonathan Lewis — December 13, 2006 @ 6:58 pm GMT Dec 13,2006 | Reply

  4. Andy, absolutely – an execution plan without the filter_predicates and access_predicates is only the structure indication of what happened. For detailed understanding of what’s really going on you really need the list of predicates supplied, generated, and eliminated.

    Your script seems to have lost some bits. It is possible to have both access_predicates and filter_predicates on one line, and your script only seems to report one or the other. Watch out, too, for the fact that either could get up to 4,000 characters – at which point your concatenation (||) would cause on Oracle error.

    Comment by Jonathan Lewis — December 13, 2006 @ 7:05 pm GMT Dec 13,2006 | Reply

  5. Jonathan, a bit OT I know – but where did you find the materialize hint documented? Or the cardinality hint come to that. I can find no reference to either of them in the 9i or 10g Performance Guides nor Mentalink.

    Any pointers as to where to find this information gratefully received.

    BTW – Just got your new Book – crackingly good.



    Comment by Ian — December 14, 2006 @ 10:50 am GMT Dec 14,2006 | Reply

  6. Ian, “materialize” hint is not documented but often use.
    If you don’t want to use it you can get the same effect (that is materialization of the result set) with “rownum > 0” in the where clause.
    Have a look at Tom Kyte’s answer at

    Comment by Michel Cadot — December 14, 2006 @ 12:46 pm GMT Dec 14,2006 | Reply

  7. After attending your seminar in London a couple of months ago I wrote this,, which gives you the plan and the predicates. IS there any more info I can get to? I remember you mentioning v$sql_plan_statistics, but it did not seem that useful

    Comment by Dave — December 14, 2006 @ 2:00 pm GMT Dec 14,2006 | Reply

  8. Ian, Finding such things – I use use “strings -a” on the Oracle executable for Unix and search for any interesting text. Looking for “star_transformation” was a good way of finding the list of possible hints. This is no longer so simple in 10g as the hints are now just words in the reserved words list.

    Materialize and Cardinality I first saw (I think) in SQL dumped from the executable and then I searched Metalink although I’m sure I found cardinality somewhere in the documentation once.

    Materialize is actually given as a workaround to a bug (3528916.8). So it is semi-official.

    I can’t find the original documentation reference I had for the cardinality hint (which was incomplete anyway) – but it arrived in 9i and there was a document somewhere on metalink which says something about its existence and the disappearance of the selectivity hint in 10g.

    Comment by Jonathan Lewis — December 14, 2006 @ 8:13 pm GMT Dec 14,2006 | Reply

  9. Dave, take a look at the article on dbms_xplan.display_cursor referenced above; it mentions the benefit of the data you can get from v$sql_plan_statistics, but for 9i you don’t have a convenient hint to enable collection. Instead, you can execute:
    alter session set statistics_level = all;
    alter session set “_rowsource_execution_statistics”=true;
    before executing a query.

    Comment by Jonathan Lewis — December 14, 2006 @ 8:31 pm GMT Dec 14,2006 | Reply

  10. I’ve used sometimes this script on OSX to have graphical display of the query (10g tested, I dont remember if I had coded this when testing the 9i prerelease…). You need graphviz instaled so that the .dot file is loaded and displayed ok… and it sure helps a lot to visualize computations that way :)

    set heading off
    set pagesize 0

    delete from plan_table;
    explain plan for
    select * from drawscan10; /* yeah */

    set linesize 3000

    select ‘digraph a {‘ from dual union all
    select op from(
    select pid, prior op||’->’||op||’;’ op
    from (
    select id, parent_id pid,
    ‘ ‘||operation||
    ‘\n’||object_name||’ (‘||cardinality||’)’||
    ‘\nACCESS: ‘||replace(replace(access_predicates,'”‘,”),’AND’,’\nAND’)||
    ‘\nFILTER: ‘||replace(replace(filter_predicates,'”‘,”),’AND’,’\nAND’)||
    ‘”‘ op
    from plan_table
    start with pid is null
    connect by pid = prior id
    ) where pid is not null
    union all
    select ‘};’ from dual;

    spool off

    spool plan.txt
    select * from table(dbms_xplan.display);
    spool off

    set heading on
    set pagesize 20

    host open

    set linesize 90

    Comment by winden — December 16, 2006 @ 6:02 pm GMT Dec 16,2006 | Reply

  11. Ian, I’ve just rediscovered the reference on Metalink about cardinality and selectivity in 10g. It’s bug number: 4121077 – SELECTIVITY HINT NOT WORKING IN 10G BUT WORKING IN 9206.

    Highlights are:
    There is no SELECTIVITY hint in 10g.
    There is a CARDINALITY hint in 10g and 9.2

    As the SELECTIVITY hint is not a documented item there is no bug here but you can use the undocumented CARDINALITY hint instead, but it too may be obsoleted in future.

    Comment by Jonathan Lewis — December 18, 2006 @ 9:12 pm GMT Dec 18,2006 | Reply

  12. Jonathan, Thanks for the info on the hints. Much appreciated.

    Nice Blog BTW.



    Comment by Ian — December 19, 2006 @ 11:44 am GMT Dec 19,2006 | Reply

  13. OK, to show your predicates, access first then filter, ordered by plan step id:

    select id,acc_or_fil,predicates
    from (
    select id,’access’ acc_or_fil ,access_predicates predicates
    from plan_table
    where access_predicates is not null
    union all
    select id,’filter’,filter_predicates
    from plan_table
    where filter_predicates is not null
    ) order by 1,2

    Comment by Andy Helm — December 19, 2006 @ 2:21 pm GMT Dec 19,2006 | Reply

  14. […] download the PL/SQL package and the SQL query mentioned in the article. I got this idea from a comment left on Jonathan Lewis’ blog. The SQL query generates Graphviz commands that produce a […]

    Pingback by Show Me a Picture! « So Many Oracle Manuals, So Little Time — May 15, 2009 @ 5:48 am BST May 15,2009 | Reply

  15. Hi Jonathan,

    My question is bit different from the content in the post.

    While reading an explain plan output i find predicate information very useful for checking the columns on which access & filter operations are happening. But in the execution plan printed in the tkprof output there is no predicate information. How do we make up for this missing piece of information, if we are given a tkprof output to analyze ?

    What i generally try to do is that take explain plan of the query and see the predicate stuff from there. But in many of the cases the execution plan in the tkprof (actual execution plan) & the plan given by explain plan differ. Would like to know that how to troubleshoot the expensive steps in the execution plan in such cases ?

    Thanks !

    Amardeep Sidhu

    Comment by Amardeep Sidhu — July 21, 2010 @ 5:54 pm BST Jul 21,2010 | Reply

    • If you can’t find a way of getting the same plan, the simplest thing is to get the test run repeated, and monitor the session until it executes the query, then pull the plan from memory. This will still leave a gap because the in-memory plans won’t record predicates that include subquerier properly, but it should help you get a lot closer to the full detail.

      If you can’t do this live, then generating the trace at level 4 will give you the bind variable values and types, which may help you to fake the plan. Note that you may have to change the code so that all references to bind variables have explicit conversion functions applied. You may end up having to hint the SQL to force the plan.

      Inevitably there will be cases where it is extremely difficult to get “explain plan” to reproduce exactly the same path (down to the predicate level) even if you put in hints that make the plan look the same. But since it’s usually the statistics or the variable types that make the differences, you can often fiddle around and get a pretty good idea.

      If you’ve got a closed cursor from your tkprof output, you’ll have the row counts returned as well – which gives you extra information about which execution lines did most work, and that’s important information that you can use to confirm your suspicions.

      Comment by Jonathan Lewis — July 24, 2010 @ 10:04 am BST Jul 24,2010 | Reply

RSS feed for comments on this post. TrackBack URI

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

%d bloggers like this: