本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议 》和 《 阿里云开发者社区知识产权保护指引 》。如果您发现本社区中有涉嫌抄袭的内容,填写 侵权投诉表单 进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

在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及其以上)

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

创建测试表

1、alter system flush shared_pool;

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

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

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

本文转自hbxztc 51CTO博客,原文链接:http://blog.51cto.com/hbxztc/1904641 ,如需转载请自行联系原作者