清除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.