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:
rem
rem Script: multiple_xplan.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2009
rem
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.