清除掉shared pool中某条sql语句方法_清除、绑定单个sql语句sharepool的执行计划-优快云博客

在论坛中看到一个帖子，如何清除掉shared pool中某条sql语句，如果是在10g以前的版本，那估计会比较麻烦，为了删除一条sql语句记录，需要清空整个shared pool，在10g中提供了新的dbms_shared_pool包可以实现该功能(如果该包没有安装，可以通过?/rdbms/admin/dbmspool.sql安装)，下面我通过在11g中试验证明该问题

1.数据库版本信息

 
        SQL>  
        select 
         *  
        from 
         v$version; 
       
        BANNER 
       
        ---------------------------------------------------------------------- 
       
        Oracle  
        Database 
         11g Enterprise Edition Release 11.2.0.1.0 - Production 
       
        PL/SQL Release 11.2.0.1.0 - Production 
       
        CORE    11.2.0.1.0      Production 
       
        TNS  
        for 
         32- 
        bit 
         Windows: Version 11.2.0.1.0 - Production 
       
        NLSRTL Version 11.2.0.1.0 - Production

2.模拟一条sql语句

 
        SQL>  
        create 
         table 
         xff  
        as 
       
        2   
        select 
         *  
        from 
         dba_tables 
       
        3   
        where 
         rownum<10; 
       
        表已创建。 
       
        SQL>  
        select 
         count 
        (*)  
        from 
         xff; 
       
        COUNT 
        (*) 
       
        ---------- 
       
        9 
       
        SQL> col sql_text  
        for 
         a30 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        ADDRESS  HASH_VALUE SQL_TEXT 
       
        -------- ---------- ------------------------------ 
       
        1EFB91B8 3642190903  
        select 
         count 
        (*)  
        from 
         xff

3.打破神话一:权限操作
对表进行权限操作，可以清除该表在shared pool中关于该表记录

 
        SQL>  
        grant 
         select 
         on 
         xff  
        to 
         chf; 
       
        授权成功。 
       
        SQL> col sql_text  
        for 
         a30 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        ADDRESS  HASH_VALUE SQL_TEXT 
       
        -------- ---------- ------------------------------ 
       
        1EFB91B8 3642190903  
        select 
         count 
        (*)  
        from 
         xff 
       
        SQL>  
        revoke 
         select 
         on 
         xff  
        from 
         chf; 
       
        撤销成功。 
       
        SQL> col sql_text  
        for 
         a30 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        ADDRESS  HASH_VALUE SQL_TEXT 
       
        -------- ---------- ------------------------------ 
       
        1EFB91B8 3642190903  
        select 
         count 
        (*)  
        from 
         xff

证明grant和revoke操作没有清除shared pool中关于该表的sql语句

4.打破神话二:ddl操作
对表进行ddl操作，可以清除该表在shared pool中关于该表记录

 
        SQL>  
        alter 
         table 
         xff  
        add  
         owner1 varchar2(100); 
       
        表已更改。 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        ADDRESS  HASH_VALUE SQL_TEXT 
       
        -------- ---------- ------------------------------ 
       
        1EFB91B8 3642190903  
        select 
         count 
        (*)  
        from 
         xff

事实证明ddl操作不能达到预期效果，没有清除掉这条sql语句

5.刷新shared pool

 
        SQL>  
        alter 
         system flush shared_pool 
       
        2  ; 
       
        系统已更改。 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        未选定行

把整个shared pool都刷新了，自然其中的一条sql语句也没有了，在10g前只能通过这种方法实现

6.使用dbms_shared_pool.purge

 
        SQL>  
        select 
         count 
        (*)  
        from 
         xff; 
       
        COUNT 
        (*) 
       
        ---------- 
       
        9 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        ADDRESS  HASH_VALUE SQL_TEXT 
       
        -------- ---------- ------------------------------ 
       
        1EFB91B8 3642190903  
        select 
         count 
        (*)  
        from 
         xff 
       
        SQL>  
        exec 
         dbms_shared_pool.purge( 
        '1EFB91B8, 3642190903' 
        , 
        'C' 
        ); 
       
        PL/SQL 过程已成功完成。 
       
        SQL>  
        SELECT 
         ADDRESS,HASH_VALUE,SQL_TEXT  
        FROM 
         V$SQLAREA 
       
        2   
        WHERE 
         SQL_TEXT  
        LIKE 
         'select % xff' 
        ; 
       
        未选定行

试验证明在shared pool中清除了一条sql记录

7.关于dbms_shared_pool.purge参数说明

 
        purge(name varchar2, flag char DEFAULT  
        'P' 
        , heaps number DEFAULT 1); 
       
        --    name 
       
        --      The name of the object to keep.  There are two kinds of objects: 
       
        --      PL 
        /SQL 
         objects, triggers, sequences, types and Java objects, 
       
        --       
        which 
         are specified by name, and 
       
        --      SQL cursor objects  
        which 
         are specified by a two-part number 
       
        --      (indicating a location  
        in 
         the shared pool).  For example: 
       
        --        dbms_shared_pool.keep( 
        'scott.hispackage' 
        ) 
       
        --      will keep package HISPACKAGE, owned by SCOTT.  The names  
        for 
       
        --      PL 
        /SQL 
         objects follows SQL rules  
        for 
         naming objects (i.e., 
       
        --      delimited identifiers, multi-byte names, etc. are allowed). 
       
        --      A cursor can be keeped by 
       
        --        dbms_shared_pool.keep( 
        '0034CDFF, 20348871' 
        ,  
        'C' 
        ) 
       
        --      The complete hexadecimal address must be  
        in 
         the first 8 characters. 
       
        --      The value  
        for 
         this identifier is the concatenation of the 
       
        --       
        'address' 
         and  
        'hash_value' 
         columns from the  
        v 
        $sqlarea view.  This 
       
        --      is displayed by the  
        'sizes' 
         call above. 
       
        --      Currently  
        'TABLE' 
         and  
        'VIEW' 
         objects may not be keeped. 
       
        -- 
       
        --    flag 
       
        --      This is an optional parameter.  If the parameter is not specified, 
       
        --        the package assumes that the first parameter is the name of a 
       
        --        package 
        /procedure/function 
         and will resolve the name.  Otherwise, 
       
        --        the parameter is a character string indicating what kind of object 
       
        --        to keep the name identifies.  The string is  
        case 
         insensitive. 
       
        --        The possible values and the kinds of objects they indicate are 
       
        --        given  
        in 
         the following table: 
       
        --          Value        Kind of Object to keep 
       
        --          -----      ---------------------- 
       
        --        P          package 
        /procedure/function 
       
        --        Q          sequence 
       
        --        R          trigger 
       
        --        T           
        type 
       
        --            JS         java  
        source 
       
        --            JC         java class 
       
        --        JR         java resource 
       
        --        JD         java shared data 
       
        --        C          cursor 
       
        --      If and only  
        if 
         the first argument is a cursor address and  
        hash 
        -value, 
       
        --        the flag parameter should be  
        set 
         to  
        'C' 
         (or  
        'c' 
        ). 
       
        -- 
       
        --    heaps 
       
        --      heaps to purge. e.g  
        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 i.e heap 0  
        which 
         means the whole object will be purged.