Oracle删除一条SQL在SharedPool里缓存的执行计划的三种方法

在Oracle里第一次执行一条SQL语句后，该SQL语句会被硬解析，而且执行计划和解析树会被缓存到Shared Pool里。方便以后再次执行这条SQL语句时不需要再做硬解析，方便应用系统的扩展。但是如果该SQL对应的表数据量突变或其他原因，Shared Pool里缓存的执行计划和解析树已经不再适用于现在的情况，SQL执行效率急速下降，这种情况下就需要把该SQL缓存在Shared Pool里的执行计划和解析树清理出去，以便对该SQL重新做硬解析，生成新的执行计划和解析树。

创新互联专业为企业提供陆丰网站建设、陆丰做网站、陆丰网站设计、陆丰网站制作等企业网站建设、网页设计与制作、陆丰企业网站模板建站服务，十载陆丰做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

从Shared Pool删除SQL缓存的执行计划有三种方法：

alter system flush shared_pool;
对表做DDL操作
dbms_shared_pool.purge包(10.2.0.4及其以上)

上面三种方法的影响范围依次递减，下面分别用实例做演示

创建测试表

zx@MYDB>create table s1 as select * from dba_objects;

Table created.

zx@MYDB>create table s2 as select * from dba_objects;

Table created.

1、alter system flush shared_pool;

这条命令是清除Shared Pool里缓存的所有数据，自然可以删除想要删除的SQL对就的执行计划，但负作用是它把Shared Pool里的所有数据都清除了，影响太大。生产系统一定要谨慎使用这个命令。

执行两个查询，并查看在Shared Pool里的缓存

zx@MYDB>select object_name from s1 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>select object_name from s2 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>col sql_text for a80
zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT									 SQL_ID 				 VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------
select object_name from s1 where object_id=20					 1s45nwjtws2tj					     1		1
select object_name from s2 where object_id=20					 a6gw4ht2unxun					     1		1

zx@MYDB>select object_name from s1 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT									 SQL_ID 				 VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------
select object_name from s1 where object_id=20					 1s45nwjtws2tj					     1		2
select object_name from s2 where object_id=20					 a6gw4ht2unxun					     1		1

上面查询分别对表s1和表s2做查询，从输出可以看出上面执行的两个SQL的执行计划和解析树被缓存到了Shared Pool中，再次执行时会直接用缓存的执行计划(EXECUTIONS变为2)。现在想删除表s1对应SQL的执行计划，执行alter system flush shared_pool;

zx@MYDB>alter system flush shared_pool;

System altered.

zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';

no rows selected

从上面查询结果可以看出命令确实删除了s1对应SQL的执行计划，但同时也把表s2对应的SQL的执行计划也删除了，伤及了无辜。

2、对表做DDL操作

一旦对某个表执行了DDL操作，库缓存中所有在SQL文本中包含了这个表的Shared Cursor都会被Oracle标记为失效(invalid)，这意味着这些Shared Cursor中存储的解析树和执行计划将不再能被重用，所以当Oracle再次执行与这个表相关的SQL时就会使用硬解析。但这种方法的弊端在于其影响范围还是太广，因为一旦对某个表执行了DDL操作，再次执行与这个表相关的所有SQL时就会全部使用硬解析。这是很不好的，特别是对于OLTP类型的应用系统而言，因为这可能会导致短时间内的硬解析数量剧增，进而影响系统的性能。

zx@MYDB>select object_name from s1 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>select object_name from s1 where object_id=30;

OBJECT_NAME
------------------------------
I_COBJ#

zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT									 SQL_ID 				 VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------
select object_name from s1 where object_id=20					 1s45nwjtws2tj					     1		1
select object_name from s1 where object_id=30					 1hdyqyxhtavqs					     1		1

zx@MYDB>select object_name from s1 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT									 SQL_ID 				 VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------
select object_name from s1 where object_id=20					 1s45nwjtws2tj					     1		2
select object_name from s1 where object_id=30					 1hdyqyxhtavqs					     1		1

上面查询对表s1做了两个不同的查询，从输出可以看出上面执行的两个SQL的执行计划和解析树被缓存到了Shared Pool中，再次执行时会直接用缓存的执行计划(EXECUTIONS变为2)。现在要删除object_id=20对应SQL的执行计划，这里选择对表添加注释(COMMENT)，它也是DDL操作。

zx@MYDB>comment on table s1 is 'test shared cursor';

Comment created.

zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT									 SQL_ID 				 VERSION_COUNT EXECUTIONS OBJECT_STATUS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------
select object_name from s1 where object_id=20					 1s45nwjtws2tj					     1		2 INVALID_UNAUTH
select object_name from s1 where object_id=30					 1hdyqyxhtavqs					     1		1 INVALID_UNAUTH

zx@MYDB>select object_name from s1 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT									 SQL_ID 				 VERSION_COUNT EXECUTIONS OBJECT_STATUS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------
select object_name from s1 where object_id=20					 1s45nwjtws2tj					     1		1 VALID
select object_name from s1 where object_id=30					 1hdyqyxhtavqs					     1		1 INVALID_UNAUTH

从上面的输出可以看出，对表s1做DDL操作后缓存在Shared Pool里的执行计划没有被清除，但是两个SQL对应的执行计划状态都变为了“INVALID_UNAUTH”，当再次执行SQL时会做硬解析，重新缓存解析树和执行计划。

3、dbms_shared_pool.purge包

它是从Oracle 10.2.0.4开始引入的一种方法，它可以用来删除指定的缓存在库缓存中的Shared Cursor，其影响范围公限于目标SQL所对应的Shared Cursor，也就是说它可以做到让Oracle在执行目标SQL时使用硬解析，在执行其他所有SQL时都和原来一样保持不变。

zx@MYDB>alter system flush shared_pool;

System altered.

zx@MYDB>select object_name from s1 where object_id=20;

OBJECT_NAME
------------------------------
ICOL$

zx@MYDB>select object_name from s1 where object_id=30;

OBJECT_NAME
------------------------------
I_COBJ#

zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT						     SQL_ID				     VERSION_COUNT EXECUTIONS OBJECT_STATUS   ADDRESS	       HASH_VALUE
------------------------------------------------------------ --------------------------------------- ------------- ---------- --------------- ---------------- ----------
select object_name from s1 where object_id=20		     1s45nwjtws2tj					 1	    1 VALID	      00000000B4F85A18 1942752049
select object_name from s1 where object_id=30		     1hdyqyxhtavqs					 1	    1 VALID	      00000000BE7E56C8 1637183192

现在要删除object_id=20对应的SQL缓存的执行计划和解析树。

zx@MYDB>exec sys.dbms_shared_pool.purge('00000000B4F85A18,1942752049','C');

PL/SQL procedure successfully completed.

zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS,address,hash_value from v$sqlarea where sql_text like 'select object_name from s%';

SQL_TEXT						     SQL_ID				     VERSION_COUNT EXECUTIONS OBJECT_STATUS   ADDRESS	       HASH_VALUE
------------------------------------------------------------ --------------------------------------- ------------- ---------- --------------- ---------------- ----------
select object_name from s1 where object_id=30		     1hdyqyxhtavqs					 1	    1 VALID	      00000000BE7E56C8 1637183192

从输出可以看出object_id=20对应的SQL缓存的执行计划和解析树被删除了，而object_id=30对应的SQL的执行计划没有受影响。

需要注意的是，如果在10.2.0.4中使用dbms_shared_pool.purge，则在使用之前必须特工设置event 5614566(alter session set events '5614566 trace name context forever')，否则dbms_shared_pool.purge将不起作用，这个限制在10.2.0.4以上的版本中已经不存在了。如果默认没有安装dbms_shared_pool包的可以执行@?/rdbms/admin/dbmspool.sql

参考《基于Oracle的SQL优化》

官方文档：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_shared_pool.htm#ARPLS68077

分享名称：Oracle删除一条SQL在SharedPool里缓存的执行计划的三种方法
当前网址：http://chengdu.cdxwcx.cn/article/jscjgg.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

Oracle删除一条SQL在SharedPool里缓存的执行计划的三种方法

其他资讯

linux系统命令卡住了 linux系统卡住了怎么弄

Java黑盒测试用例代码黑盒测试常用的测试用例设计方法

vb点虐登录面代码 vb的登录界面如何制作

智能聊天替代码农的公司的简单介绍

帝国cms模板判断帝国cms移动端

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

Oracle删除一条SQL在SharedPool里缓存的执行计划的三种方法

其他资讯

linux系统命令卡住了 linux系统卡住了怎么弄

Java黑盒测试用例代码 黑盒测试常用的测试用例设计方法

vb点虐 登录面代码 vb的登录界面如何制作

智能聊天替代码农的公司的简单介绍

帝国cms模板判断 帝国cms移动端

成都网站建设设计将想法与焦点和您一起共享

Java黑盒测试用例代码黑盒测试常用的测试用例设计方法

vb点虐登录面代码 vb的登录界面如何制作

帝国cms模板判断帝国cms移动端