Oracle Scratchpad

May 5, 2009

Dependent Plans

Filed under: dbms_xplan,Execution plans,lateral view — Jonathan Lewis @ 6:09 pm BST May 5,2009

I’ve written several posts about dbms_xplan, and the display_cursor function in 10g. One of the nice feature of this function is that it is a “pipelined” function – which means that you can treat the call to the function as a “virtual table”. Here’s an example (run on 10.2.0.3) of what this allows us to do:


select
        t.plan_table_output
from    (
        select
                sql_id, child_number
        from
                v$sql
        where
                hash_value in (
                        select  from_hash
                        from    v$object_dependency
                        where   to_name = 'XXXXX'
                )
        ) v,
        table(dbms_xplan.display_cursor(v.sql_id, v.child_number)) t
;

In this example, I’ve joined an inline view to a table – but the “table” is the result set from a call to dbms_xplan.display_cursor, and the join is a variation of the lateral join mechanism which takes as its inputs some values from an earlier object in the from clause.

The output is a list of the execution plans for all the queries currently in the library cache that are dependent on the object I’ve named in the inline query against v$object_dependency.

This could be very useful information when you’re trying to work out the possible side effects of (for example) changing the indexes on a table.  Be very cautious with queries like this, though, as they’re likely to cause a lot of contention on library and shared pool latches – but just occasionally the cost may be worth it.)

Footnote: ideally I would like to introduce an order by clause that ensures the execution plan output (column plan_table_output from dbms_xplan calls) appears in the right order – but there is no available column, and the nature of pipelined functions probably ensures that the data will appear in the correct order … in this case.

4 Comments »

  1. Very nice idea ! Thanks for sharing this, I will be using this for sure. We are migrating an Oracle ERP from version 11.5.9 to 11.5.10, and I need to describe the impact of our customization in the 11.5.10 environnement.

    I was unaware of pipeline function. I guess I have some documentation reading to do ;-)

    Comment by Statistique — May 5, 2009 @ 6:47 pm BST May 5,2009 | Reply

  2. Very smart. I don’t think I would have thought of that!

    Comment by Doug Burns — May 5, 2009 @ 10:09 pm BST May 5,2009 | Reply

  3. Brilliant work from a brilliant mind.

    Thank you very much for this small script

    Comment by coskan — May 6, 2009 @ 4:23 pm BST May 6,2009 | Reply

  4. [...] sui Join in Oracle. Tre giorni fa Jonathan Lewis ha scritto sul suo blog un post intitolato “Dependent Plans” in cui descrive una query per ottenere tutti i piani di esecuzione in cache di query che [...]

    Pingback by SQL JOIN – aggiornamento « Oracle and other — May 8, 2009 @ 2:48 pm BST May 8,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers