在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

ITPub博客

11G can flush one SQL Cursor out of shared pool

最新文章