9i and 10g 透過SQL_ADDRESS 或sql_id查詢執行計劃

V$SQL_PLAN, DBMS_XPLAN.DISPLAY_CURSOR:

If you know sql_id or the set of (hash_value, address) of the shared cursor, you can get the correct plan using V$SQL_PLAN or DBMS_XPLAN.DISPLAY_CURSOR.  Make sure the calling user has privileges on fixed following views: V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN.  


-- For 9i and later

col operation for a20
col object_name for a20
col options for a20
col optimizer for a12
col child_number a3
SELECT child_number, id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost
FROM V$SQL_PLAN 
WHERE hash_value = &hash_value 
AND address = '&address' 
ORDER BY child_number, id;

-- For 10g and later 

col operation for a20
col object_name for a20
col options for a20
col optimizer for a12
col child_number a3
SELECT child_number, id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost
FROM V$SQL_PLAN 
WHERE sql_id = '&sql_id' 
ORDER BY child_number, id;




select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));