11G can flush one SQL Cursor out of shared pool

在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博客 ” ，链接：http://blog.itpub.net/15415488/viewspace-627066/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15415488/viewspace-627066/