Oracle 从缓存里面查找真实的执行计划
有关Oracle 的执行计划说明,参考我的Blog:
http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5827245.aspx
一. 查看当前session 的SID
SYS@anqing1(rac1)> SELECT USERENV('SID') FROM DUAL;
USERENV('SID')
--------------
137
SYS@anqing1(rac1)> SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;
SID
----------
137
二. 查看缓存中的Explain Plan
1)根据SID,从v$sql中找到相应SQL的HASH_VALUE和ADDRESS
/* Formatted on 2011/6/20 17:38:20 (QP5 v5.163.1008.3004) */
SELECTa.sql_text,a.address,a.hash_value
FROM v$sql a, v$session b
WHEREa.hash_value = b.sql_hash_value AND b.sid= &sid;
2)根据hash_value和address的值,从v$sql_plan中找到真实的执行计划
/* Formatted on 2011/6/20 17:39:22 (QP5 v5.163.1008.3004) */
SET LINE 200;
COL oper FORMAT a100;
SELECTLPAD(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')
STARTWITHid=0
CONNECTBYPRIORid= parent_id;
如: