1 執行過程可能發生的原因及優化方案

? SQL語句的執行過程

1).使用hash演算法得到sql語句的hash_value值

2).如果hash_value值在記憶體中，叫做命中執行軟解析

3).如果hash_value值不存在，執行硬解析

4).語法解析，檢視是否有錯誤

5).語意解析，檢視許可權是否符合

6).若有檢視，取出檢視的定義

7).進行sql語句的自動改寫，如將子查詢改寫為連線

8).選擇最優的執行計劃

9).變數繫結

10).執行執行計劃

11).返回結果給使用者

? 下面針對其中幾個不同階段可能發生的各種問題做重點說明，並給出對應優化方案。

1.1 開啟遊標

? 遊標cursor在伺服器端分配記憶體以來對sql語句進行快取區處理SQL解析。因此複用遊標可以提高效能。

? 在這裡簡單說一下cursor_sharing引數三個不同選項（exact，similar，force）。

1).EXACT精確匹配，原語句不做處理，降低了sql的命中率，但可以保證執行計劃是準確的，此種模式為系統預設的模式； 2).SIMILAR近似匹配，將where條件都用變數處理，單可以區分列值的資料敏感性，一種折中的方案，但oracle在處理該類引數的sql語句時會有一定的問題，慎3).FORCE強制匹配，將where條件都用變數來處理，提高了SQL的命中率，但不能區分列值的資料敏感性，執行計劃有時是正確的，但是有時會出現錯誤； 4).建議儘可能的保持系統預設的EXACT精確匹配模式，如需調整，建議在測試環境做好充足的驗證。

2.2.1語法解析

? 語法解析不需要SGA，系統伺服器程式直接進行處理，語法錯誤則直接顯示。

2.2.2語義解析

? 解釋：語義校驗需要資料字典中的資訊來支撐

? 可能原因：Shared Pool中的Data Dictionary Cache空間過小或快取資訊不是本次需要的資料，導致從磁碟的system表空間的資料檔案中讀取，效能降低。

? 優化方案：適當增加Shared pool 以便間接的增加Data Dictionary Cache的大小，以快取更多的資料字典資訊。

2.2.3生成執行計劃

? 解釋：使用者發出sql命令後，Server process會檢查Shared Pool的Library Cache中是否存有有對應語句的執行計劃。如果Shared Pool中的sql的命中率低於90%就需要對其進行優化。l

? 可能原因：1. Library Cache中沒有的執行計劃，進行硬解析（hard parse） 2. Library Cache存有大量類似的執行計劃

? 優化方案：1 首次執行一次即可，Library Cache便會儲存此執行計劃；適當的增加Shared pool 以便間接的增加Library Cache的大小，快取更多的執行計劃。 2.優化SQL語句，書寫程式時儘量不要過多的使用常量。（使用繫結變數，where department_id=&X）

1.2 執行執行計劃

2.3.1尋找資料

? 解釋：伺服器程式查詢SGA的Database Buffer Cache中是否存有對應資料塊。

? 可能原因：1.對應資料快（例如如employees表）在記憶體中經常被換出。 2.生成了錯誤的執行計劃，如應走索引卻進行了全盤掃描，反之亦然。

? 優化方案：1.將常用表快取在Database Buffer Cache的Keep池中，以增加命中率 2.引導CPU正確計算執行計劃，如刪除索引（前途是滿足對應條件比如無外來鍵關聯等）、使用簡單技巧避開索引（如對索引列進行+0函式運算等）；建立合適的索引（如在country列這種一般不會發生變化的列建立點陣圖索引以提高效率）

2.3.2執行操作前

? 解釋：由於Oracle的日誌提前寫機制的存在，每次對資料進行操作前都會記錄對應的操作日誌，這一機制的存在可能會導致一些問題。

? 可能原因：1.Redo Log Buffer被佔滿、或過小（引數檔案設定） 2.檢查點發生過於頻繁，Redo Log Buffer中的資料以很快的頻率寫入到磁碟，記憶體利用率低，效能下滑 3.日誌組成員分配空間過小，日誌組數過少，日誌切換切換頻繁，迴圈複用因歸檔未完成而發生等待 4.Undo 表空間過小，系統為保護原有資料不丟失而發生等待

? 優化方案：1.增加Redo Log Buffer的大小以快取更多資料 2.調整檢查點發生頻率，合理利用記憶體以提高效率 3.增加日誌組中成員大小，增加日誌組個數，調整日誌切換頻率，關閉歸檔（生產庫不建議） 4.增大Undo表空間大小；調整Undo表空間為自動擴充套件模式。

2.3.3執行過程中

? 可能原因：使用者在執行更新或刪除操作時，另一個使用者在對同樣的資料進行修改而發生鎖等待。

? 優化方案：1.等待另一個使用者程式commit或rollback操作；kill掉對應User； 2.Oracle的Select For Update語句可以實現在讀取資料後馬上鎖定相關資源，防止被其他session修改資料的目的。也就是我們常常談到的“悲觀鎖定”（現實應用開發中，使用悲觀鎖定的情況少之又少，也許是因為樂觀鎖定的實現更加靈活和便捷的緣故）。

? 其他可能問題：1. 是否處在資料庫工作高峰期執行操作 2. CPU、磁碟本身的效能問題

? 對應優化方案：1.儘量在資料庫非高峰期執行操作

2.硬體問題，更新硬體。

1.3 提取資料

? 解釋：SQL語句執行結束後要把執行結果返回給使用者程式。

? 可能原因：1.返回資料時出現網路問題，資料傳輸緩慢或者中斷。 2. 對處理結果進行了group by排序，導致PGA不夠，啟用臨時表空間，效能低下。

? 優化方案：1找出網路問題並修復。 2.不進行排序操作；加大PGA空間大小。

1.4 關閉遊標

? 可能原因：頻繁對cursor進行開，關操作會降低遊標的複用能力，降低系統效能。

? 優化方案：尋找平衡點。

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

SQL語句響應緩慢原因及優化