Oracle删除一条SQL在Shared Pool里缓存的执行计划的三

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类型的应用系统而言,因为这可能会导致短时间内的硬解析数量剧增,进而影响系统的性能。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/008e244fedff0dc5e7f608eeb02b4f1a.html