Oracle 从共享池删除指定SQL的执行计划

本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者 。转载本文请联系DBA闲思杂想录公众号。

Oracle 11g在DBMS_SHARED_POOL包中引入了一个名为PURGE的新存储过程，用于从对象库缓存中刷新特定对象，例如游标，包，序列，触发器等。也就是说可以删除、清理特定SQL的执行计划， 这样在特殊情况下，就避免你要将整个SHARED POOL清空的危险情况。例如某个SQL语句由于优化器产生了错误的执行计划，我们希望优化器重新解析，生成新的执行计划，必须无将SQL的执行计划从共享池中刷出或将其置为无效，那么优化器才能将后续SQL进行硬解析、生成新的执行计划。这在以前只能使用清空共享池的方法或对表进行DDL操作。现在就可以指定刷新特定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;