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

发表于:2017-1-06 10:01

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:潇湘隐者    来源:51Testing软件测试网采编

#
Oracle
分享:
  Oracle 11g在DBMS_SHARED_POOL包中引入了一个名为PURGE的新存储过程,用于从对象库缓存中刷新特定对象,例如游标,包,序列,触发器等。也就是说可以删除、清理特定SQL的执行计划,这样在特殊情况下,就避免你要将整个SHARED POOL清空的危险情况。例如某个SQL语句由于优化器产生了错误的执行计划,我们希望优化器重新解析,生成新的执行计划,必须先将SQL的执行计划从共享池中刷出或将其置为无效,那么优化器才能将后续SQL进行硬解析、生成新的执行计划。这在以前只能使用清空共享池的方法。现在就可以指定刷新特定SQL的执行计划。当然在10.2.0.4 和10.2.0.5的补丁集中该包也被包含进来,该包的存储过程有三个参数,如下所示:
DBMS_SHARED_POOL.PURGE (
name    VARCHAR2,
flag    CHAR DEFAULT 'P',
heaps   NUMBER DEFAULT 1);
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NAME                           VARCHAR2                IN
FLAG                           CHAR                    IN     DEFAULT
HEAPS                          NUMBER                  IN     DEFAULT
  第一个参数: 为逗号分隔的ADDRESS列和HASH_VALUE列的值。
  第二个参数: 可以有多个选项,例如C、P、T、R、Q等。具体意义如下所示
  C表示PURGE的对象是CURSOR
  Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
  Set to 'T' or 't' to specify that the input is the name of a type.
  Set to 'R' or 'r' to specify that the input is the name of a trigger.
  Set to 'Q' or 'q' to specify that the input is the name of a sequence.
  ...................................
  第三个参数: heaps,一般使用默认值1
  Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
  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中没有这个问题,具体演示如下所示:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> alter system flush shared_pool;
System altered.
SQL> set linesize 1200;
SQL> select * from scott.dept where deptno=40;
DEPTNO DNAME          LOC
---------- -------------- -------------
40 OPERATIONS     BOSTON
SQL> select sql_id, first_load_time
2  from v$sql
3  where sql_text like 'select * from scott.dept%';
SQL_ID        FIRST_LOAD_TIME
------------- ---------------------------------------------------------
3nvuzqdn6ry6x 2016-12-29/08:51:21
SQL> col sql_text for a64;
SQL> select address, hash_value, sql_text
2  from v$sqlarea
3  where sql_id='3nvuzqdn6ry6x';
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ----------------------------------------------------------------
00000000968ED510 1751906525 select * from scott.dept where deptno=40
SQL> exec dbms_shared_pool.purge('00000000968ED510,1751906525','C');
PL/SQL procedure successfully completed.
SQL> select address, hash_value, sql_text
2  from v$sqlarea
3  where sql_id='3nvuzqdn6ry6x';
ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------------------
00000000968ED510 1751906525 select * from scott.dept where deptno=40
SQL>
  如上截图所示,DBMS_SHARED_POOL.PURGE并没有清除这个特定的SQL的执行计划,其实这个是因为在10.2.0.4.0 要生效就必须开启5614566 EVNET,否则不会生效。 具体可以参考官方文档:
DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (文档 ID 751876.1)
Bug 7538951 : DBMS_SHARED_POOL IS NOT WORKING AS EXPECTED
Bug 5614566 : WE NEED A FLUSH CURSOR INTERFACE
DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available
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.
Set the event 5614566 in the init.ora to turn purge on.
event="5614566 trace name context forever"
  如下所示,设置5614566 event后,必须重启数据库才能生效,这个也是一个比较麻烦的事情。当然这也是一个没有办法的事情.
  alter system set event = '5614566 trace name context forever' scope = spfile;
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号