ORACLE從共用池刪除指定SQL的執行計劃

Oracle 11g在DBMS_SHARED_POOL包中引入了一個名為PURGE的新存儲過程，用於從對象庫緩存中刷新特定對象，例如游標，包，序列，觸發器等。也就是說可以刪除、清理特定SQL的執行計劃，這樣在特殊情況下，就避免你要將整個SHARED POOL清空的危險情況。例如某個SQL語句由於優化器產生了錯誤的執行計劃，我們希望優化器重新解析，生成新的執行計劃，必須先將SQL的執行計劃從共用池中刷出或將其置為無效，那麼優化器才能將後續SQL進行硬解析、生成新的執行計劃。這在以前只能使用清空共用池的方法。現在就可以指定刷新特定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;