清除SQL语句的执行计划

1   对对应的表执行ddl操作，comment on、grant 等等，最常用的是grant，但是这种操作影响比较大，尤其是业务高峰期间对一个很忙的表进行此类操作，需要非常慎重，可能导致系统hang。

 

2    如果对于多个表联合查询的，可以对其中关联的某个不忙的表进行grant，而不是我们一直关注的某个核心表。这个简单快捷又安全，我们就处理过类似的事情：两个表联合查询，但是其中的一个核心表由于统计信息错误导致执行计划错误，收集统计信息后没敢直接让它生效。但是这个表很多语句都用到了，也非常的忙；但是另外一个表就是一个不忙的表，如是对另外一个表做grant。这个需要一点点发散思维。

 

3  DBMS_SHARED_POOL.PURGE ，但是有些版本的数据库不能正常工作：

详细请查看oracle  metalink：

How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package [ID 457309.1]

 

The syntax for the DBMS_SHARED_POOL.PURGE package is:
procedure purge (name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1); 

 Explanation: Purge the named object or particular heap(s) of the object. 
 Input arguments: 
  name: The name of the object to purge.
        There are two kinds of objects: 
         PL/SQL objects, triggers, sequences, types and Java objects which are specified by name,
         SQL cursor objects which are specified by a twopart number. The value for this identifier
         is the concatenation of the 'address' and 'hash_value' columns from the v$sqlarea view.

  flag: This is an optional parameter. If the parameter is not specified, 
        the package assumes that the first parameter is the name of a 
        package/procedure/function and will resolve the name. Otherwise, 
        the parameter is a character string indicating what kind of object 
        to purge the name identifies. The string is case insensitive. 
        The possible values and the kinds of objects they indicate are 
        given in the following table: 

        Value Kind of Object to keep 
        ----- ---------------------- 
            P package/procedure/function 
            Q sequence 
            R trigger 
            T type 
           JS java source 
           JC java class 
           JR java resource 
           JD java shared data 
            C cursor 

  heaps: heaps to purge. e.g if heap 0 and heap 6 are to be purged. 
         1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65. 
         Default is 1 i.e heap 0 which means the whole object will be purged.

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

转载于:http://blog.itpub.net/758322/viewspace-708254/