文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Oracle 从共享池删除指定SQL的执行计划

2024-12-02 14:00

关注

本文转载自微信公众号「DBA闲思杂想录」,作者潇湘隐者 。转载本文请联系DBA闲思杂想录公众号。

Oracle 11g在DBMS_SHARED_POOL包中引入了一个名为PURGE的新存储过程,用于从对象库缓存中刷新特定对象,例如游标,包,序列,触发器等。也就是说可以删除、清理特定SQL的执行计划, 这样在特殊情况下,就避免你要将整个SHARED POOL清空的危险情况。例如某个SQL语句由于优化器产生了错误的执行计划,我们希望优化器重新解析,生成新的执行计划,必须无将SQL的执行计划从共享池中刷出或将其置为无效,那么优化器才能将后续SQL进行硬解析、生成新的执行计划。这在以前只能使用清空共享池的方法或对表进行DDL操作。现在就可以指定刷新特定SQL的执行计划。当然在10.2.0.4 和10.2.0.5的补丁集中该包也被包含进来,该包的存储过程有三个参数,如下所示:

  1. DBMS_SHARED_POOL.PURGE ( 
  2.    name    VARCHAR2,  
  3.    flag    CHAR DEFAULT 'P',  
  4.    heaps   NUMBER DEFAULT 1) 
  5.  
  6. Argument Name                  Type                    In/Out Default
  7.  ------------------------------ ----------------------- ------ -------- 
  8.  NAME                           VARCHAR2                IN 
  9.  FLAG                           CHAR                    IN     DEFAULT 
  10.  HEAPS                          NUMBER                  IN     DEFAULT 

第一个参数为逗号分隔的ADDRESS列和HASH_VALUE列的值。

第二个参数可以有多个选项,例如C、P、T、R、Q等。具体意义如下所示 C表示PURGE的对象是CURSOR

  1. Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function
  2. Set to 'T' or 't' to specify that the input is the name of a type. 
  3. Set to 'R' or 'r' to specify that the input is the name of a trigger
  4. Set to 'Q' or 'q' to specify that the input is the name of a sequence
  5. ................................... 

第三个参数heaps,一般使用默认值1

  1. Heaps to be purged. For example, if heap 0 and heap 6 are to be purged: 
  2. 1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged 

在ORACLE 11g当中,你可以在$ORACLE_HOME/rdbms/admin/dbmspool.sql中查看该包的具体定义. 但是这个DBMS_SHARED_POOL.PURGE在10.2.0.4.0(实际测试发现10.2.0.5.0也存在同样问题)都有一些问题,它可能无法生效,当然在Oracle 11g中没有这个问题,具体演示如下所示:

  1. SQL> select * from v$version; 
  2.  
  3. BANNER 
  4. ---------------------------------------------------------------- 
  5. Oracle Database 10g Release 10.2.0.5.0 - 64bit Production 
  6. PL/SQL Release 10.2.0.5.0 - Production 
  7. CORE    10.2.0.5.0      Production 
  8. TNS for Linux: Version 10.2.0.5.0 - Production 
  9. NLSRTL Version 10.2.0.5.0 - Production 
  10.  
  11. SQL> alter system flush shared_pool; 
  12.  
  13. System altered. 
  14.  
  15. SQL> set linesize 1200; 
  16. SQL> select * from scott.dept where deptno=40;  
  17.  
  18.     DEPTNO DNAME          LOC 
  19. ---------- -------------- ------------- 
  20.         40 OPERATIONS     BOSTON 
  21.  
  22. SQL> select sql_id, first_load_time 
  23.   2  from v$sql 
  24.   3  where sql_text like 'select * from scott.dept%'
  25.  
  26. SQL_ID        FIRST_LOAD_TIME 
  27. ------------- --------------------------------------------------------- 
  28. 3nvuzqdn6ry6x 2016-12-29/08:51:21 
  29.  
  30. SQL> col sql_text for a64; 
  31. SQL> select address, hash_value, sql_text 
  32.   2  from v$sqlarea 
  33.   3  where sql_id='3nvuzqdn6ry6x'
  34.  
  35. ADDRESS          HASH_VALUE SQL_TEXT 
  36. ---------------- ---------- ---------------------------------------------------------------- 
  37. 00000000968ED510 1751906525 select * from scott.dept where deptno=40 
  38.  
  39. SQL> exec dbms_shared_pool.purge('00000000968ED510,1751906525','C'); 
  40.  
  41. PL/SQL procedure successfully completed. 
  42.  
  43. SQL> select address, hash_value, sql_text 
  44.   2  from v$sqlarea 
  45.   3  where sql_id='3nvuzqdn6ry6x'
  46.  
  47. ADDRESS          HASH_VALUE SQL_TEXT 
  48. ---------------- ---------- ---------------------------------------------------------------- 
  49. 00000000968ED510 1751906525 select * from scott.dept where deptno=40 
  50.  
  51. SQL>  

如上截图所示,DBMS_SHARED_POOL.PURGE并没有清除这个特定的SQL的执行计划,其实这个是因为在10.2.0.4.0 要生效就必须开启5614566 EVNET,否则不会生效。具体可以参考官方文档:

  1. DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (文档 ID 751876.1) 
  2. Bug 7538951 : DBMS_SHARED_POOL IS NOT WORKING AS EXPECTED 
  3. Bug 5614566 : WE NEED A FLUSH CURSOR INTERFACE 
  4.  
  5. DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available 
  6. through the fix for Bug 5614566. However, the fix is event protected.  You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect. 
  7.  
  8. Set the event 5614566 in the init.ora to turn purge on
  9.  
  10. event="5614566 trace name context forever" 

如下所示,设置5614566 event后,必须重启数据库才能生效,这个也是一个比较麻烦的事情。

  1. alter system set event = '5614566 trace name context forever' scope = spfile; 

 

来源: DBA闲思杂想录 内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯