使指定的SQL重新解析

转自 http://www.dbaroad.me/archives/2011/06/dbms_shared_pool_purge.html

有时候，我们需要将某个特定的SQL刷出shared pool，使其重新解析，比如遇到bink peeking 执行计划变差的时候，9I里常用授权，把语句相关表进行授权，但影响太大，与该表相关的语句都重新解析了。
在10.2.0.4以后有了新方法，用BMS_SHARED_POOL.PURGE来完成：

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.

在11G之前需要设置5614566，才能使用DBMS_SHARED_POOL.PURGE。

注：DBMS_SHARED_POOL包可用$ORACLE_HOME/rdbms/admin/dbmspool.sql来创建。

Session 1:
执行语句。

Session 2:

SQL> select sid, sql_hash_value, sql_id,sql_address from v$session where sid = 8762;
 
       SID SQL_HASH_VALUE SQL_ID        SQL_ADDRESS
---------- -------------- ------------- ----------------
      8762     4284659990 0k9z3p3zq5f8q C0000005AAE21DA0
 
SQL> select hash_value, sql_id, address, child_number, executions, loads
  2    from v$sql
  3   where sql_id = '0k9z3p3zq5f8q';
 
HASH_VALUE SQL_ID        ADDRESS          CHILD_NUMBER EXECUTIONS      LOADS
---------- ------------- ---------------- ------------ ---------- ----------
4284659990 0k9z3p3zq5f8q C0000005AAE21DA0            0          1          1
 
SQL> select hash_value,
  2         sql_id,
  3         address,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls
  9    from v$sqlarea
 10   where sql_id = '0k9z3p3zq5f8q';
 
HASH_VALUE SQL_ID        ADDRESS          EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------- ------------- ---------------- ---------- ---------- ------------- ------------- -----------
4284659990 0k9z3p3zq5f8q C0000005AAE21DA0          1          1             1             0           1
 
SQL> 
SQL> alter session set events '5614566 trace name context forever';
 
Session altered.
 
SQL> exec dbms_shared_pool.purge ('C0000005AAE21DA0,4284659990','C');
 
PL/SQL procedure successfully completed.
 
SQL> select hash_value, sql_id, address, child_number, executions, loads
  2    from v$sql
  3   where sql_id = '0k9z3p3zq5f8q';
 
no rows selected
 
SQL> 
SQL> select hash_value,
  2         sql_id,
  3         address,
  4         executions,
  5         loads,
  6         version_count,
  7         invalidations,
  8         parse_calls
  9    from v$sqlarea
 10   where sql_id = '0k9z3p3zq5f8q';
 
no rows selected

可以看出语句已被刷出shared pool。

— The End —