oracle 9i sql_id,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'));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31520497/viewspace-2156891/，如需转载，请注明出处，否则将追究法律责任。