在Oracle中，如何清除Shared Pool中某条SQL语句？

如果数据库版本为Oracle 10.2.0.4以前，那么只能清空整个Shared Pool，命令为：“ALTER SYSTEM FLUSH SHARED_POOL;”。在生产库上，该句要慎用。而从Oracle 10.2.0.4开始提供了一个包DBMS_SHARED_POOL，该包可以实现清除Shared Pool中某条SQL语句的功能。若该包没有安装，则可以通过$ORACLE_HOME/rdbms/admin/dbmspool.sql进行安装。

使用这种方法可以精确的将一个SQL从共享池中删除，从而使得Oracle为这个SQL重新生成执行计划。这种方法只针对单个SQL语句，使得解决问题的同时不会造成任何的误伤。

示例如下：

SYS@lhrdb>  SELECT ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE ROWNUM<=1;
ADDRESS          HASH_VALUE
---------------- ----------
0000000092D263D0 3231842444
SYS@lhrdb> EXEC DBMS_SHARED_POOL.PURGE('0000000092D263D0,3231842444','C');--这里的C表示游标（Cursor）
PL/SQL procedure successfully completed.
SYS@lhrdb> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA WHERE ADDRESS='0000000092D263D0' AND HASH_VALUE='3231842444';
no rows selected

清除Shared Pool中某条SQL语句无非是让目标SQL语句重新进行硬解析，其实重新进行硬解析的方法还有如下两种：

①　使用DDL操作可以让Oracle再次执行目标SQL时使用硬解析（新产生一个子游标），但这种方法的弊端在于其影响范围还是太广了，因为一旦对某个表执行了DDL操作，库缓存中所有在SQL文本中包含了这个表的Shared Cursor都会被Oracle标记为失效（INVALID），这意味着这些Shared Cursor中存储的解析树和执行计划将不再能被重用，再次执行与这个表相关的所有SQL时就会全部使用硬解析。这是很不好的，特别是对于OLTP类型的应用系统而言，这可能会导致短时间内的硬解析数量剧增，进而影响系统的性能。尽管如此，有时仍然会采用这种方法。这里的DDL操作有很多种，通常选择添加注释的COMMENT语句（“COMMENT ON TABLE TB_NAME IS 'xxx by lhr';”），因为使用COMMENT添加注释也是DDL操作，但同时对系统的影响相对而言比较小。

②　在重新收集统计信息时指定NO_INVALIDATE=>FALSE选项。如果取值为FALSE，那么表示将统计信息对象相关的所有Cursor全部失效。目标SQL语句在下次执行时就会使用硬解析。

& 说明：

有关DBMS_SHARED_POOL.PURGE的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2129896/，有关NO_INVALIDATE可以参考：http://blog.itpub.net/26736162/viewspace-2139300/