如何从Shared Pool中flush一条SQL语句

最近遇到一个问题,就是有一个系统运行的语句,是查询系统视图的一些语句,运行一段时间会引发数据库Hung。查看AWR,看到大量的等待事件是Latch:libarary Cache。然后我们发现数据库的一些递归调用的SQL中的Version Count特别高。然后就查了一下MOS,发现确实有下列问题的存在。这些类型的系统递归调用的SQL不仅仅会造成这种等待,有可能还会造成:

  • library cache load lock
  • cursor: pin S wait on X
  • library cache: mutex X
  • library cache lock

当然这些都是Oracle的一些bug导致的,有各种各样的bug导致游标无法共享。例如:
Bug 12976376 – High VERSION_COUNT for SQL with binds, including recursive dictionary SQL – superseded
Bug 14613900 – ORA-7445 [kkscsSearchChildList] with fix of bug 12976376 present – superseded

解决这些问题的手段也是无非就两种:
1.通过设置隐含参数来解决;
2.通过打补丁来永久解决这些bug;

不过有时候,我们会遇到一些复杂的情况。例如我们会考量,隐含参数设置是否会禁用掉一些功能,或者是引发别的问题?数据库的业务是7*24的,暂时没有停机窗口,无法安装补丁。为了应对这种无奈的困境,我们可以使用DBMS_SHARED_POOL包进行Purge。一旦我们发现某条语句的子游标达到一定数量级后,我们就将其Purge出共享池。下面是Purge的一些参数,我们还能purge各种各样的对象。例如包、存储过程、触发器、游标等等。

procedure purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);  
 Explanation: Purge the named object or particular heap(s) of the object.  
 Input arguments:  
  name: The name of the object to purge. 
        There are two kinds of objects:  
         PL/SQL objects, triggers, sequences, types and Java objects which are specified by name,
         SQL cursor objects which are specified by a twopart number. The value for this identifier
         is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view. 
  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 purge 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  
  heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged.  
         1<<0 | 1<         Default is 1 i.e heap 0 which means the whole object will be purged.

说了这么多,下面来做个演示:

-------SESSION1
SQL> connect scott/tiger
Connected.

SQL> select ename from emp where empno=7900;

ENAME
----------
JAMES

-------SESSION2

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls 
  2  from v$sqlarea 
  3  where sql_text = 'select ename from emp where empno=7900';

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
0000000074CF6798 1052545619          1          1             1             0           1

SQL> exec dbms_shared_pool.purge ('0000000074CF6798,1052545619','C'); 

PL/SQL procedure successfully completed.

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls   
  2  from v$sqlarea                                                                             
  3  where sql_text = 'select ename from emp where empno=7900';  

no rows selected

非常容易我们就把我们的游标从共享池中清掉了。所以在实际情况中,我们可以写一个脚本,去检测数据库,如果发现VERSION COUNT达到一定的阈值,我们就使用Purge来进行清理,清理完之后,这些语句下一次运行会重新进入到共享池。当然这只是一个临时解决的办法,如果可以我觉得还是打补丁为好。不过这个东西在10.2.0.4以下的平台有限制。10.2.0.2和10.2.0.3需要打PATCH 5614566。而在10.2.0.4这个版本中,我们需要设置事件event="5614566 trace name context forever"才能起作用。

参考文档:
How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package [ID 457309.1]
DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1]


分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *