Oracle 10.2.0.4 以上 可以把shared_pool內的特定SQL purge掉.

Oracle 10.2.0.4 以上 可以把shared_pool內的特定SQL purge掉. 參考資料

如下為Oracle blogs 提供之步驟:

 

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

Ref: https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single

 

By Giri Mandalika on May 28, 2010

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:

<![if !supportLists]>o                <![endif]>Oracle Support Document ID 457309.1 "How To Flush an Object out the Library Cache [SGA]"

<![if !supportLists]>o                <![endif]>Oracle Support Document ID 751876.1 "DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4"

<![if !supportLists]>o                <![endif]>DBMS_SHARED_POOL.PURGE() procedure documentation

後記:

如剛分析完表格, indexes 卻忘了加以下參數no_invalidate=>FALSE , 如感受不到SQL CBO改善,

可清掉DB的shared pool , Client程式再重連試試(建議用在測試環境):

SQL> ALTER SYSTEM FLUSH SHARED_POOL;