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

142次阅读

共计 7560 个字符，预计需要花费 19 分钟才能阅读完成。

在 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

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

参考：《基于 Oracle 的 SQL 优化》PDF 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-03/141598.htm

正文完

星哥说事-微信公众号