在11G之前（準確的說在10204之前），當我們需要flush一些literal SQL out of shared pool時，我們馬上想到的是對於這個SQL所在的object上做一些DDL使其馬上被flush出shared pool。例如：
under 10G:
SQL> select ADDRESS,HASH_VALUE,EXECUTIONS from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS       HASH_VALUE EXECUTIONS
---------------- ---------- ----------
00000003ADA4FFB8 4184050671       2

SQL> grant select on testsqlsql to dba;
Grant succeeded.

SQL> select ADDRESS,HASH_VALUE,EXECUTIONS from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
no rows selected

但是11G開始這招不靈了。
做DDL只是將這個objects相關的SQL標誌為INVALID，然後下次執行時才被清除。

under 11G:
SQL> select ADDRESS,HASH_VALUE,EXECUTIONS,SHARABLE_MEM,OBJECT_STATUS
  2  from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS       HASH_VALUE EXECUTIONS SHARABLE_MEM OBJECT_STATUS
---------------- ---------- ---------- ------------ -------------------
000000039FCBEBE0 4184050671       1       14656 VALID
SQL> grant select on testsqlsql to dba;
Grant succeeded.
SQL> select ADDRESS,HASH_VALUE,EXECUTIONS,SHARABLE_MEM,OBJECT_STATUS
  2  from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS       HASH_VALUE EXECUTIONS SHARABLE_MEM OBJECT_STATUS
---------------- ---------- ---------- ------------ -------------------
000000039FCBEBE0 4184050671       1       14656 INVALID_UNAUTH

一般情況下是沒問題的，但是如果我們希望flush一些literal SQL或者佔用shared pool非常之大的SQL，而且希望他們馬上消失，這時我們需要其他的辦法。
就是11G的dbms_shared_pool.purge。（準確的說10204就有了，只是有一些bug）。

PROCEDURE PURGE
Argument Name                Type                   In/Out Default?
------------------------------ ----------------------- ------ --------
NAME                         VARCHAR2             IN
FLAG                         CHAR                   IN    DEFAULT
HEAPS                         NUMBER                IN    DEFAULT

如果對於SQL cursor來說，NAME引數的格式是ADDR,HASH_VALUE from v$sqlarea。
FLAG引數標明瞭NAME引數的種類，對於SQL Cursor來說，FLAG只要不為'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'即可。所以在其他的文章中，為了flush一個SQL Cursor，FLAG常被設為'C'，其實設為其他任何字母都沒問題。
HEAPS引數為1代表所有heaps都被purge。即：

SQL> select ADDRESS,HASH_VALUE,EXECUTIONS,SHARABLE_MEM,OBJECT_STATUS
  2  from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS       HASH_VALUE EXECUTIONS SHARABLE_MEM OBJECT_STATUS
---------------- ---------- ---------- ------------ -------------------
000000039FCBEBE0 4184050671       1       14656 INVALID_UNAUTH

SQL> exec sys.dbms_shared_pool.purge('000000039FCBEBE0,4184050671','C',1);
PL/SQL procedure successfully completed.

SQL> select ADDRESS,HASH_VALUE,EXECUTIONS,SHARABLE_MEM,OBJECT_STATUS
  2  from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
no rows selected

11G can flush one SQL Cursor out of shared pool

相關文章