Oracle 从缓存里面查找真实的执行计划

有关Oracle的执行计划说明，参考我的Blog：

Oracle Explain Plan

http://blog.youkuaiyun.com/xujinyang/article/details/6832655

一.查看当前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

FROMv$sqla,v$session b

WHEREa.hash_value=b.sql_hash_valueANDb.sid=&sid;

2）根据hash_value和address的值，从v$sql_plan中找到真实的执行计划

/* Formatted on 2011/6/20 17:39:22 (QP5 v5.163.1008.3004) */

SETLINE200;

COL oper FORMAT a100;

SELECTLPAD(oper,LENGTH(oper)+LEVEL*2,' ')oper,cost

FROM(SELECTobject_name ||':'|| operation ||' '|| optionsASoper,

cost,

id,

parent_id

FROMv$sql_plan

WHEREhash_value='&hash_value'ANDaddress='&address')

STARTWITHid=0

CONNECTBYPRIORid=parent_id;

如：

整理自网络

-------------------------------------------------------------------------------------------------------