對sql語句的最佳化問題

參照sql例句：

Select * from employees where employee_id=100;

Update  employees  set  employee_id =101 where employee_id=3;

最佳化問題：

執行sql語句的時間明顯長於之前，需要對整體的作業系統進行最佳化分析。

思路分析：

   (透過對sql語句執行的各個過程進行分析以來查詢最佳化項)。

SQL 語句執行過程詳細分析：

<1> 開啟遊標cursor：

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

<2> 解析：

     語法校驗：語法校驗時不需要SGA，系統伺服器程式直接進行處理，有錯直接就會顯示出來。

     語義校驗：語義校驗需要資料字典中的資訊來支撐（資料字典存有使用者資訊，使用者的許可權資訊，所有資料物件的結構資訊，表的約束條件，統計分析資料庫的檢視等，資料字典邏輯存放在system表空間中，物理存放在資料檔案中）。

在讀取表的大量結構資訊的時候，因為從磁碟資料檔案直接取會導致系統效能緩慢，oracle引入了Data Dictionary Cache 元件以用來在記憶體中直接快取資料字典中的資訊，避免了系統從磁碟直接讀取資訊，提高了系統效能。如果Data Dictionary Cache 元件記憶體很小，或者快取的資訊不是本次所需要的資料，就需要從磁碟讀取資訊，會直接影響系統效能，此時，適當的增加Shared pool 以便間接的增加Data Dictionary Cache的記憶體，快取更多的資料字典資訊。（Data Dictionary Cache 元件提高了sql語句解析語義計劃的效率）。

生成執行計劃：當完成語法，語義校驗後，生成執行計劃並且快取到shared pool 的library cache中以便下一次執行同樣的SQL語句時不需要重新執行之前的操作（生成新的執行計劃叫硬解析，直接複用之前的執行計劃叫軟解析）。反覆做硬解析CPU浪費很嚴重，影響系統效能。因此可以透過適當增加library cache的記憶體大小，檢視SQL語句並繫結變數，或者刪除不常用的SQL執行計劃以來將更多頻繁使用的sql語句執行計劃快取到library cache中，以此來提高系統效能。

（shared pool 提高了對sql語句解析計劃階段的效率）

<3> 執行SQL語句

為了保證執行效率的提高，需要將資料從磁碟上的資料檔案快取到DataBase buffer cache中，直接從記憶體中對錶進行操作，（執行階段需考慮全盤掃描和走索引不同的情況，當有大量重複資料時全盤掃描比較快，當資料分佈不均勻時索引比較快），在DataBase buffer cache中建立keep池，recycle池，default池（keep池用來快取那些經常會被訪問的表，並且使用的緩衝區獨立於DEFAULT池和recycle池，平時要適當減少keep池的記憶體分配，使keep池的命中率維持在接近100%的數值。Recycle池用來儲存那些不希望儲存在記憶體中的表。一般沒有細化記憶體分配時，系統預設只有一個default池。）

<4>提取結果並儲存

     因為最終的結果是要透過net網路返回到應用伺服器（客戶服務端）上，所以如果網路出現問題，同樣會導致使用者執行sql語句時返回結果的效能降低。

     sqlprocess提取結果並把最終結果返回給客戶端時，如果面臨order by 排序時，將要進行PGA sort記憶體排，如果資料量很大，PGA不能滿足記憶體排所需空間時，就需要與磁碟上的臨時表空間進行儲存分配交換，這個磁碟分配交換將要對臨時檔案頻繁進行寫和讀，因為對磁碟頻繁操作，導致系統效能大大降低。此時，需要將PGA最佳化，如果不得不使用臨時檔案時，就需要建立多個臨時檔案，併發降低讀寫壓力，以達到最佳化效果。

<5>close cursor

     頻繁對cursor進行開，關操作會降低遊標的複用能力，降低系統效能。（找平衡點）

 

鎖等待也可能是導致sql語句執行效能降低的一個原因，所謂的鎖等待:就是一個事務a對一個資料表進行ddl或是dml操作時，系統就會對該表加上表級的排它鎖，此時其他的事務對該表進行操作的時候會等待a提交或是回滾後，才可以繼續b的操作。當一個使用者a進行ddl或者dml操作卻沒提交或者回滾，使用者b對相同行進行操作就會一直等待使用者a提交或者回滾，導致sql語句執行時間增長。

當對錶進行DML操作時：

<1><2>解析執行如上所示；

<3> 執行SQL語句

      為了保證修改資料的動作能夠被資料庫恢復過來，引入了redo機制，如果redo資訊足夠多時，當系統頻繁向磁碟寫入日誌檔案時就容易導致系統效能低下，降低整個資料庫的執行效率，因此為了提高日誌提交效率，引入了Redolog buffer cache元件。當發現問題為日誌檔案時，應該適當增加Redolog buffer cache的記憶體，redo file的大小和日誌檔案組以來最佳化dml操作的效能問題。

<4><5>如上