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


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

    2016-12-29 11:14 by 潇湘隐者, 2836 阅读, 0 评论, 收藏, 编辑

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

       name    VARCHAR2, 
    CHAR DEFAULT 'P', 
    DEFAULT 1);
     
    In/Out Default?
     NAME                           VARCHAR2                IN
    CHAR                    IN     DEFAULT
    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.

    ...................................

    clip_image001

    第三个参数: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中没有这个问题,具体演示如下所示:

    select * from v$version;
    BANNER
    Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
    CORE    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;
    select * from scott.dept where deptno=40; 
        DEPTNO DNAME          LOC
            40 OPERATIONS     BOSTON
    SQL> select sql_id, first_load_time
    from v$sql
    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;
    select address, hash_value, sql_text
    from v$sqlarea
    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
    from v$sqlarea
    where sql_id='3nvuzqdn6ry6x';
    ADDRESS          HASH_VALUE SQL_TEXT
    00000000968ED510 1751906525 select * from scott.dept where deptno=40
    SQL> 

    clip_image002

    如上截图所示,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后,必须重启数据库才能生效,这个也是一个比较麻烦的事情。当然这也是一个没有办法的事情.

    scope = spfile;
  • 相关阅读:
    记一次笔试题
    模块模式浅析
    在windows上面实现ubuntu的三种方法
    移动端事件(五)—— 函数防抖和函数节流的封装
    一文分清强弱、动态静态类型语言
    为什么前端要学TypeScript
    OpenSSL SSL_read: Connection was reset, errno 10054
    浅悉http1.0 、http1.1和http2.0的区别
    上班一个月的总结
    从浅入深——理解JSONP的实现原理
  • 原文地址:https://www.cnblogs.com/xibuhaohao/p/10271541.html
Copyright © 2020-2023  润新知