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

Oracle 11g在DBMS_SHARED_POOL包中引入了一个名为PURGE的新存储过程，用于从对象库缓存中刷新特定对象，例如游标，包，序列，触发器等。也就是说可以删除、清理特定SQL的执行计划，这样在特殊情况下，就避免你要将整个SHARED POOL清空的危险情况。例如某个SQL语句由于优化器产生了错误的执行计划，我们希望优化器重新解析，生成新的执行计划，必须先将SQL的执行计划从共享池中刷出或将其置为无效，那么优化器才能将后续SQL进行硬解析、生成新的执行计划。这在以前只能使用清空共享池的方法。现在就可以指定刷新特定SQL的执行计划。当然在10.2.0.4 和10.2.0.5的补丁集中该包也被包含进来，该包的存储过程有三个参数，如下所示：

name    VARCHAR2,

CHAR DEFAULT 'P',

DEFAULT 1);

In/Out Default?

NAME                           VARCHAR2                IN

CHAR                    IN     DEFAULT

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中没有这个问题，具体演示如下所示：

select * from v$version;

BANNER

Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

CORE    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;

select * from scott.dept where deptno=40;

DEPTNO DNAME          LOC

40 OPERATIONS     BOSTON

SQL> select sql_id, first_load_time

from v$sql

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;

select address, hash_value, sql_text

from v$sqlarea

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

from v$sqlarea

where sql_id='3nvuzqdn6ry6x';

ADDRESS          HASH_VALUE SQL_TEXT

00000000968ED510 1751906525 select * from scott.dept where deptno=40

SQL>