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

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了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及其以上)

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

创建测试表

  1. zx@MYDB>createtables1asselect*fromdba_objects;
  2.  
  3. Tablecreated.
  4.  
  5. zx@MYDB>createtables2asselect*fromdba_objects;
  6.  
  7. Tablecreated.

1、alter system flush shared_pool;

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

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

  1. zx@MYDB>selectobject_namefroms1whereobject_id=20;
  2.  
  3. OBJECT_NAME
  4. ------------------------------
  5. ICOL$
  6.  
  7. zx@MYDB>selectobject_namefroms2whereobject_id=20;
  8.  
  9. OBJECT_NAME
  10. ------------------------------
  11. ICOL$
  12.  
  13. zx@MYDB>colsql_textfora80
  14. zx@MYDB>selectsql_text,sql_id,version_count,executionsfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  15.  
  16. sql_TEXT									sql_ID				VERSION_COUNTEXECUTIONS
  17. ----------------------------------------------------------------------------------------------------------------------------------------------
  18. selectobject_namefroms1whereobject_id=20					1s45nwjtws2tj					1		1
  19. selectobject_namefroms2whereobject_id=20					a6gw4ht2unxun					1		1
  20.  
  21. zx@MYDB>selectobject_namefroms1whereobject_id=20;
  22.  
  23. OBJECT_NAME
  24. ------------------------------
  25. ICOL$
  26.  
  27. zx@MYDB>selectsql_text,executionsfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  28.  
  29. sql_TEXT									sql_ID				VERSION_COUNTEXECUTIONS
  30. ----------------------------------------------------------------------------------------------------------------------------------------------
  31. selectobject_namefroms1whereobject_id=20					1s45nwjtws2tj					1		2
  32. selectobject_namefroms2whereobject_id=20					a6gw4ht2unxun					1		1

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

  1. zx@MYDB>altersystemflushshared_pool;
  2.  
  3. Systemaltered.
  4.  
  5. zx@MYDB>selectsql_text,executionsfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  6.  
  7. norowsselected

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

2、对表做DDL操作

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

  1. zx@MYDB>selectobject_namefroms1whereobject_id=20;
  2.  
  3. OBJECT_NAME
  4. ------------------------------
  5. ICOL$
  6.  
  7. zx@MYDB>selectobject_namefroms1whereobject_id=30;
  8.  
  9. OBJECT_NAME
  10. ------------------------------
  11. I_COBJ#
  12.  
  13. zx@MYDB>selectsql_text,executionsfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  14.  
  15. sql_TEXT									sql_ID				VERSION_COUNTEXECUTIONS
  16. ----------------------------------------------------------------------------------------------------------------------------------------------
  17. selectobject_namefroms1whereobject_id=20					1s45nwjtws2tj					1		1
  18. selectobject_namefroms1whereobject_id=30					1hdyqyxhtavqs					1		1
  19.  
  20. zx@MYDB>selectobject_namefroms1whereobject_id=20;
  21.  
  22. OBJECT_NAME
  23. ------------------------------
  24. ICOL$
  25.  
  26. zx@MYDB>selectsql_text,executionsfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  27.  
  28. sql_TEXT									sql_ID				VERSION_COUNTEXECUTIONS
  29. ----------------------------------------------------------------------------------------------------------------------------------------------
  30. selectobject_namefroms1whereobject_id=20					1s45nwjtws2tj					1		2
  31. selectobject_namefroms1whereobject_id=30					1hdyqyxhtavqs					1		1

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

  1. zx@MYDB>commentontables1is'testsharedcursor';
  2.  
  3. Commentcreated.
  4.  
  5. zx@MYDB>selectsql_text,executions,OBJECT_STATUSfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  6.  
  7. sql_TEXT									sql_ID				VERSION_COUNTEXECUTIONSOBJECT_STATUS
  8. -------------------------------------------------------------------------------------------------------------------------------------------------------------
  9. selectobject_namefroms1whereobject_id=20					1s45nwjtws2tj					1		2INVALID_UNAUTH
  10. selectobject_namefroms1whereobject_id=30					1hdyqyxhtavqs					1		1INVALID_UNAUTH
  11.  
  12. zx@MYDB>selectobject_namefroms1whereobject_id=20;
  13.  
  14. OBJECT_NAME
  15. ------------------------------
  16. ICOL$
  17.  
  18. zx@MYDB>selectsql_text,OBJECT_STATUSfromv$sqlareawheresql_textlike'selectobject_namefroms%';
  19.  
  20. sql_TEXT									sql_ID				VERSION_COUNTEXECUTIONSOBJECT_STATUS
  21. -------------------------------------------------------------------------------------------------------------------------------------------------------------
  22. selectobject_namefroms1whereobject_id=20					1s45nwjtws2tj					1		1VALID
  23. selectobject_namefroms1whereobject_id=30					1hdyqyxhtavqs					1		1INVALID_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. zx@MYDB>altersystemflushshared_pool;
  2.  
  3. Systemaltered.
  4.  
  5. zx@MYDB>selectobject_namefroms1whereobject_id=20;
  6.  
  7. OBJECT_NAME
  8. ------------------------------
  9. ICOL$
  10.  
  11. zx@MYDB>selectobject_namefroms1whereobject_id=30;
  12.  
  13. OBJECT_NAME
  14. ------------------------------
  15. I_COBJ#
  16.  
  17. zx@MYDB>selectsql_text,OBJECT_STATUS,address,hash_valuefromv$sqlareawheresql_textlike'selectobject_namefroms%';
  18.  
  19. sql_TEXT						sql_ID				VERSION_COUNTEXECUTIONSOBJECT_STATUSADDRESS	HASH_VALUE
  20. -------------------------------------------------------------------------------------------------------------------------------------------------------------------
  21. selectobject_namefroms1whereobject_id=20		1s45nwjtws2tj					1	1VALID	00000000B4F85A181942752049
  22. selectobject_namefroms1whereobject_id=30		1hdyqyxhtavqs					1	1VALID	00000000BE7E56C81637183192

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

  1. zx@MYDB>execsys.dbms_shared_pool.purge('00000000B4F85A18,1942752049','C');
  2.  
  3. PL/sqlproceduresuccessfullycompleted.
  4.  
  5. zx@MYDB>selectsql_text,hash_valuefromv$sqlareawheresql_textlike'selectobject_namefroms%';
  6.  
  7. sql_TEXT						sql_ID				VERSION_COUNTEXECUTIONSOBJECT_STATUSADDRESS	HASH_VALUE
  8. -------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9. selectobject_namefroms1whereobject_id=30		1hdyqyxhtavqs					1	1VALID	00000000BE7E56C81637183192

从输出可以看出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相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问