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

本文涉及的产品
全局流量管理 GTM,标准版 1个月
云解析 DNS,旗舰版 1个月
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
简介:

在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
2
3
4
5
6
7
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里的缓存

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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时都和原来一样保持不变。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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缓存的执行计划和解析树。

1
2
3
4
5
6
7
8
9
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



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







相关文章
|
2月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
29天前
|
SQL
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
【YashanDB知识库】使用leading hint调整SQL执行计划后报错YAS-04522 invalid hint leading
|
16天前
|
SQL Oracle 关系型数据库
解决大小写、保留字与特殊字符问题!Oracle双引号在SQL中的特殊应用
在Oracle数据库开发中,双引号的使用是一个重要但易被忽视的细节。本文全面解析了双引号在SQL中的特殊应用场景,包括解决标识符与保留字冲突、强制保留大小写、支持特殊字符和数字开头标识符等。同时提供了最佳实践建议,帮助开发者规避常见错误,提高代码可维护性和效率。
73 6
|
21天前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
|
23天前
|
SQL 存储 Oracle
【YashanDB知识库】对比Oracle和YashanDB对象不一致的方法
本文来自YashanDB官网,介绍从Oracle迁移到YashanDB后的数据校验方法。通过YMP工具迁移数据后,可使用其内置校验功能,或采用本文提供的复杂对比思路:将Oracle的DBA_*数据字典视图保存为用户表,借助YMP同步至YashanDB,再利用SQL查询两数据库间的差异。具体包括视图、存储过程、索引和约束等对象的对比方法,提供详细SQL示例以供参考和修改。
|
1月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】yashandb执行包含带oracle dblink表的sql时性能差
【YashanDB知识库】yashandb执行包含带oracle dblink表的sql时性能差
|
21天前
|
SQL 存储 关系型数据库
【YashanDB知识库】如何从内存中获取SQL语句的执行计划
【YashanDB知识库】如何从内存中获取SQL语句的执行计划
|
23天前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
本文来自YashanDB官网,介绍如何处理Oracle客户端sql*plus中使用@@调用同级目录SQL脚本的场景。崖山数据库23.2.x.100已支持@@用法,但旧版本可通过Python脚本批量重写SQL文件,将@@替换为绝对路径。文章通过Oracle示例展示了具体用法,并提供Python脚本实现自动化处理,最后调整批处理脚本以适配YashanDB运行环境。
|
25天前
|
SQL
【YashanDB知识库】过期统计信息导致SQL执行计划变差
【YashanDB知识库】过期统计信息导致SQL执行计划变差
|
2月前
|
SQL
【YashanDB 知识库】使用 leading hint 调整 SQL 执行计划后报错 YAS-04522 invalid hint leading
在 YashanDB 的所有版本中,使用 leading hint 调整 SQL 执行计划时可能出现“YAS-04522 invalid hint leading”错误,导致 SQL 无法正常执行。原因是 YashanDB 优化器的 Bug。解决方法为避免使用 leading hint。可通过创建测试表 a、b、c 并执行特定 SQL 语句来验证问题是否存在。

推荐镜像

更多