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'));

ITPub博客

9i and 10g 通过SQL_ADDRESS 或sql_id查询执行计划

最新文章