清除shared pool中某條sql的執行計劃

dbLjy2015發表於2017-01-11
SQL
      今天下午突然出現了大量的物理IO，通過生成ash報告查詢出對應的sql_id以及發現這條sql走的是全表掃。因為使用了繫結變數，繫結變數導致之後的語句不作重新解析,重用了最差的執行計劃，而且還是好多個應用都在執行這條sql。對於一個一億多條資料的表進行全表掃無異於是災難性的，但是為什麼會突然間出現錯誤的執行計劃呢。首先先將問題進行處理，使其走正確的執行計劃。

處理思路：
收集表的統計資訊，清除library cache中現有的執行計劃，從新執行該語句，使其從新硬解析生成新的執行計劃。


                                               圖：處理前的cpu使用

                                               圖：處理前物理IO的影像

                                              圖：處理前的執行計劃（全表掃）

1、收集表的統計資訊
收集表的統計資訊的同時收集該表索引的統計資訊
  1. exec dbms_stats.gather_table_stats('FHL_DCS','OPERATELOG',cascade=>true);
2、清除library cache內現有的執行計劃。
        1）根據sql_id取到改sql的address值以及雜湊值
  1. select s.SQL_TEXT, s.ADDRESS || ',' || s.HASH_VALUE
  2. from v$sqlarea s
  3. where sql_id = '10kq6nc2rfrf0'；
        2）清理該sql在library cache中的執行計劃

  1. exec sys.dbms_shared_pool.purge('0000000141AE1310,91708864','c');
注：其中0000000141AE1310為該sql的address值，91708864為sql的雜湊值。
        3）檢視執行計劃是否已清除
  1. select * from v$sql_plan where sql_id = '10kq6nc2rfrf0'；
生產中我們常使用繫結變數降低硬解析的發生，但是對於同一條sql來說，當library cache存在這條sql的執行計劃時，oracle不會再為它生成一個新的執行計劃，即便這個執行計劃是最差的。

                                           圖：處理後的cpu使用情況

                                            圖：處理後的物理IO變化
變化還是很明顯的，在開始的時候物理IO都會達到120~150KIO/sec，處理後就將至4~10KIO/sec


                                             圖：處理後的執行計劃
可以看出來這時候，走的是相對正確的執行計劃


關於10g使用dbms_shared_pool.purge需要執行alter session set events '5614566 trace name context forever';來啟用
參考部落格：http://blog.itpub.net/8984272/viewspace-619964/
參考 metalink Doc ID:  751876.1  
10.2.0.4預設不開啟,要靠event 5614566或者補丁5614566來啟用




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30820196/viewspace-2132266/，如需轉載，請註明出處，否則將追究法律責任。

相關文章