在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
2from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS
HASH_VALUE EXECUTIONS SHARABLE_MEM OBJECT_STATUS
---------------- ---------- ---------- ------------ -------------------
000000039FCBEBE0 4184050671
114656 VALID
SQL> grant select on testsqlsql to dba;
Grant succeeded.
SQL> select ADDRESS,HASH_VALUE,EXECUTIONS,SHARABLE_MEM,OBJECT_STATUS
2from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS
HASH_VALUE EXECUTIONS SHARABLE_MEM OBJECT_STATUS
---------------- ---------- ---------- ------------ -------------------
000000039FCBEBE0 4184050671
114656 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
2from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
ADDRESS
HASH_VALUE EXECUTIONS SHARABLE_MEM OBJECT_STATUS
---------------- ---------- ---------- ------------ -------------------
000000039FCBEBE0 4184050671
114656 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
2from v$sqlarea where SQL_TEXT like 'select count(*) from testsqlsql%';
no rows selected
|
