ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

原创 Linux操作系统 作者:denglt 时间:2012-06-07 09:40:03 0 删除 编辑
原文:https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE in the DBMS_SHARED_POOL package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.

The syntax for the PURGE procedure is shown below.

procedure PURGE (
        name VARCHAR2, 
        flag CHAR DEFAULT 'P', 
        heaps NUMBER DEFAULT 1)

Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS 	 HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Note to Oracle 10g R2 Customers

The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.

Also see:

附:清除游标缓存的几种方法

http://www.oracledatabase12g.com/archives/oracle%E4%B8%AD%E6%B8%85%E9%99%A4%E6%B8%B8%E6%A0%87%E7%BC%93%E5%AD%98%E7%9A%84%E5%87%A0%E7%A7%8D%E6%96%B9%E6%B3%95.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/195110/viewspace-732109/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    690594