dbms_shared_pool.purge

来源：互联网发布：交友软件机器人编辑：程序博客网时间：2024/05/16 06:56

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

10:43:12 hlb@CRMUAT>create table hlb(id int);Table created.10:43:40 hlb@CRMUAT>select * from hlb;no rows selected10: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           110: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 declaredORA-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 共享池，但是这种方法动作太大。

0 0