dbms_shared_pool.purge

在日常管理中，可能有让sql 重新解析的需求，比如使用了绑定变量窥探，但是第一次绑定的执行计划不一定很好，需要重新执行硬解析，这时我们可以使用

dbms_shared_pool.purge

10:43:12 hlb@CRMUAT>create table hlb(id int);

Table created.

10:43:40 hlb@CRMUAT>select * from hlb;

no rows selected

10:44:03 hlb@CRMUAT> select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from hlb';

ADDRESS          HASH_VALUE EXECUTIONS PARSE_CALLS
---------------- ---------- ---------- -----------
0000000547C72BC0 3989195553          1           1

10:44:26 hlb@CRMUAT> exec dbms_shared_pool.purge('0000000547C72BC0,3989195553','C');
BEGIN dbms_shared_pool.purge('0000000547C72BC0,3989195553','C'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

给 hlb 用户授了dba的权限都执行不了， 使用sys 用户 执行：

10:45:59 sys@CRMUAT>exec dbms_shared_pool.purge('0000000547C72BC0,3989195553','C');

PL/SQL procedure successfully completed.

再次查看 sql 执行计划方面的信息，可以发现信息已经被清空。

10:45:13 hlb@CRMUAT>select address,hash_value,executions,parse_calls from v$sql where sql_TEXT like 'select * from hlb';

no rows selected

(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.

Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.

Set to 'T' or 't' to specify that the input is the name of a type.

Set to 'R' or 'r' to specify that the input is the name of a trigger.

Set to 'Q' or 'q' to specify that the input is the name of a sequence.

In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or'q' or 'R' or 'r' or 'T' or 't'.

purge 能够清楚 视图、触发器等。

除了上面的方法，还可以重建对象或者flush 共享池，但是这种方法动作太大。