1)根据SID,从v$sql中找到相应SQL的HASH_VALUE和ADDRESS ;
SELECT a.sql_text , a.address , a.hash_value
FROM v$sql a , v$session b
where a.hash_value = b.sql_hash_value
and b.sid = &sid ;
2)根据hash_value和address的值,从v$sql_plan中找到真实的执行计划。
set line 200;
col oper format a100;
select lpad(oper,length(oper)+level*2,' ') oper,cost
from (
select object_name||':'||operation||' '||options as oper,cost,id,parent_id
from v$sql_plan
where hash_value = &hash_value
and address = '&address'
)
start with id=0
connect by prior id = parent_id;