Oracle 從共享池刪除指定SQL的執行計劃
本文轉載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者 。轉載本文請聯系DBA閑思雜想錄公眾號。
Oracle 11g在DBMS_SHARED_POOL包中引入了一個名為PURGE的新存儲過程，用于從對象庫緩存中刷新特定對象，例如游標，包，序列，觸發器等。也就是說可以刪除、清理特定SQL的執行計劃， 這樣在特殊情況下，就避免你要將整個SHARED POOL清空的危險情況。例如某個SQL語句由于優化器產生了錯誤的執行計劃，我們希望優化器重新解析，生成新的執行計劃，必須無將SQL的執行計劃從共享池中刷出或將其置為無效，那么優化器才能將后續SQL進行硬解析、生成新的執行計劃。這在以前只能使用清空共享池的方法或對表進行DDL操作。現在就可以指定刷新特定SQL的執行計劃。當然在10.2.0.4 和10.2.0.5的補丁集中該包也被包含進來，該包的存儲過程有三個參數，如下所示：
- DBMS_SHARED_POOL.PURGE (
- name VARCHAR2,
- flag CHAR DEFAULT 'P',
- heaps NUMBER DEFAULT 1)
- Argument Name Type In/Out Default?
- ------------------------------ ----------------------- ------ --------
- NAME VARCHAR2 IN
- FLAG CHAR IN DEFAULT
- HEAPS NUMBER IN DEFAULT
第一個參數為逗號分隔的ADDRESS列和HASH_VALUE列的值。
第二個參數可以有多個選項，例如C、P、T、R、Q等。具體意義如下所示 C表示PURGE的對象是CURSOR
- Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
- Set to 'T' or 't' to specify that the input is the name of a type.
- Set to 'R' or 'r' to specify that the input is the name of a trigger.
- Set to 'Q' or 'q' to specify that the input is the name of a sequence.
- ...................................
第三個參數heaps，一般使用默認值1
- Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
- 1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged
在ORACLE 11g當中，你可以在$ORACLE_HOME/rdbms/admin/dbmspool.sql中查看該包的具體定義. 但是這個DBMS_SHARED_POOL.PURGE在10.2.0.4.0(實際測試發現10.2.0.5.0也存在同樣問題)都有一些問題，它可能無法生效，當然在Oracle 11g中沒有這個問題，具體演示如下所示：
- SQL> select * from v$version;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
- PL/SQL Release 10.2.0.5.0 - Production
- CORE 10.2.0.5.0 Production
- TNS for Linux: Version 10.2.0.5.0 - Production
- NLSRTL Version 10.2.0.5.0 - Production
- SQL> alter system flush shared_pool;
- System altered.
- SQL> set linesize 1200;
- SQL> select * from scott.dept where deptno=40;
- DEPTNO DNAME LOC
- ---------- -------------- -------------
- 40 OPERATIONS BOSTON
- SQL> select sql_id, first_load_time
- 2 from v$sql
- 3 where sql_text like 'select * from scott.dept%';
- SQL_ID FIRST_LOAD_TIME
- ------------- ---------------------------------------------------------
- 3nvuzqdn6ry6x 2016-12-29/08:51:21
- SQL> col sql_text for a64;
- SQL> select address, hash_value, sql_text
- 2 from v$sqlarea
- 3 where sql_id='3nvuzqdn6ry6x';
- ADDRESS HASH_VALUE SQL_TEXT
- ---------------- ---------- ----------------------------------------------------------------
- 00000000968ED510 1751906525 select * from scott.dept where deptno=40
- SQL> exec dbms_shared_pool.purge('00000000968ED510,1751906525','C');
- PL/SQL procedure successfully completed.
- SQL> select address, hash_value, sql_text
- 2 from v$sqlarea
- 3 where sql_id='3nvuzqdn6ry6x';
- ADDRESS HASH_VALUE SQL_TEXT
- ---------------- ---------- ----------------------------------------------------------------
- 00000000968ED510 1751906525 select * from scott.dept where deptno=40
- SQL>
如上截圖所示，DBMS_SHARED_POOL.PURGE并沒有清除這個特定的SQL的執行計劃，其實這個是因為在10.2.0.4.0 要生效就必須開啟5614566 EVNET，否則不會生效。具體可以參考官方文檔：
- DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (文檔 ID 751876.1)
- Bug 7538951 : DBMS_SHARED_POOL IS NOT WORKING AS EXPECTED
- Bug 5614566 : WE NEED A FLUSH CURSOR INTERFACE
- DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available
- through the fix for Bug 5614566. However, the fix is event protected. You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect.
- Set the event 5614566 in the init.ora to turn purge on.
- event="5614566 trace name context forever"
如下所示，設置5614566 event后，必須重啟數據庫才能生效，這個也是一個比較麻煩的事情。
- alter system set event = '5614566 trace name context forever' scope = spfile;
