Dependent Plans

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.