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 ( select /*+ materialize */ child_number, id, parent_id, depth, operation || ' ' || object_name || ' ' || options simple_plan from v$sql_plan where hash_value = &m_hash_value ) select child_number, id, parent_id, depth, lpad(' ',2 * level) || simple_plan simple_plan from plan_subq start with id = 0 connect by parent_id = prior id and child_number = prior child_number order by child_number, id ;
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.