Oracle 從緩存里面查找真實的執行計劃

有關Oracle 的執行計劃說明，參考：Oracle Explain Plan 見 http://www.linuxidc.com/Linux/2011-10/44226.htm

一.  查看當前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) */
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中找到真實的執行計劃
/* Formatted on 2011/6/20 17:39:22 (QP5 v5.163.1008.3004) */
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;

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]