No. 10: Show Statement Execution Path

This is the tenth of Kevin Loney's "10 Scripts Every DBA Should Have." Watch for a complete article on how to use these scripts in a future issue of Oracle Magazine. There are other valuable scripts as well, but these ten--covering backup/recovery, space management, account management, maintenance, and tuning--provide basic guideposts to start from during your administration activities.

Don't leave $ORACLE_HOME without them!

This query selects the execution path from the PLAN_TABLE, ordered according to the hierarchy of the steps involved. The query shown in the listing specifically queries for the execution path of a statement using the Statement_ID of 'TEST'. Prior to running this query, you should use the EXPLAIN PLAN command to populate PLAN_TABLE.

select 
 LPAD(' ',2*LEVEL)||Operation||' '||Options 
                ||' '||Object_Name   Q_PLAN  
 from PLAN_TABLE 
 where Statement_ID = 'TEST' 
 connect by prior ID = Parent_ID and Statement_ID = 'TEST' 
 start with ID=1;