Oracle SQL 效能最佳化技巧

標籤：過程   最佳化器   整合   需要   合并   自動   覆蓋   session   訪問   

Select語句完整的執行順序：

SQL Select語句完整的執行順序：

1、 from子句組裝來自不同資料來源的資料； 2、where子句基於指定的條件對記錄行進行篩選； 3、group by子句將資料劃分為多個分組； 4、使用聚集合函式進行計算； 5、使用having子句篩選分組； 6、計算所有的運算式； 7、 使用order by對結果集進行排序

 

效能最佳化技巧

Oracle SQL 效能最佳化技巧

1.選用適合的ORACLE最佳化器      ORACLE的最佳化器共有3種

     A、RULE (基於規則) b、COST (基於成本) c、CHOOSE (選擇性)

     設定預設的最佳化器，可以通過對init.ora檔案中OPTIMIZER_MODE參數的各種聲明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你當然也在SQL句級或是會話(session)級對其進行覆蓋。

     為了使用基於成本的最佳化器(CBO， Cost-Based Optimizer) ， 你必須經常運行analyze 命令，以增加資料庫中的對象統計資訊(object statistics)的準確性。

     如果資料庫的最佳化器模式設定為選擇性(CHOOSE)，那麼實際的最佳化器模式將和是否運行過analyze命令有關。如果table已經被analyze過， 最佳化器模式將自動成為CBO ， 反之，資料庫將採用RULE形式的最佳化器。

在預設情況下，ORACLE採用CHOOSE最佳化器，為了避免那些不必要的全表掃描(full table scan) ，你必須盡量避免使用CHOOSE最佳化器，而直接採用基於規則或者基於成本的最佳化器。

2.訪問Table的方式      ORACLE 採用兩種訪問表中記錄的方式：      A、 全表掃描           全表掃描就是順序地訪問表中每條記錄。ORACLE採用一次讀入多個資料區塊(database block)的方式最佳化全表掃描。      B、 通過ROWID訪問表           你可以採用基於ROWID的訪問方式情況，提高訪問表的效率， ROWID包含了表中記錄的物理位置資訊。ORACLE採用索引(INDEX)實現了資料和存放資料的物理位置(ROWID)之間的聯絡。通常索引提供了 快速存取ROWID的方法，因此那些基於索引列的查詢就可以得到效能上的提高。

3.共用SQL語句      為了不重複解析相同的SQL語句，在第一次解析之後，ORACLE將SQL語句存放在記憶體中。這塊位於系統全域地區SGA(system global area)的共用池(shared buffer pool)中的記憶體可以被所有的資料庫使用者共用。因此，當你執行一個SQL語句(有時被稱為一個遊標)時，如果它和之前的執行過的語句完全相同， ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑。ORACLE的這個功能大大地提高了SQL的執行效能並節省了記憶體的使用。

     可惜的是ORACLE只對簡單的表提供高速緩衝(cache buffering)，這個功能並不適用於多表串連查詢。

     資料庫管理員必須在init.ora中為這個地區設定合適的參數，當這個記憶體地區越大，就可以保留更多的語句，當然被共用的可能性也就越大了。

     當你向ORACLE提交一個SQL語句，ORACLE會首先在這塊記憶體中尋找相同的語句。這裡需要註明的是，ORACLE對兩者採取的是一種嚴格匹配，要達成共用，SQL語句必須完全相同(包括空格，換行等)。

     資料庫管理員必須在init.ora中為這個地區設定合適的參數，當這個記憶體地區越大，就可以保留更多的語句，當然被共用的可能性也就越大了。

     共用的語句必須滿足三個條件：

     A、 字元級的比較：當前被執行的語句和共用池中的語句必須完全相同。

     B、 兩個語句所指的對象必須完全相同：

     C、兩個SQL語句中必須使用相同的名字的綁定變數(bind variables)。

4.選擇最有效率的表名順序(只在基於規則的最佳化器中有效)      ORACLE的解析器按照從右至左的順序處理FROM子句中的表名，因此FROM子句中寫在最後的表(基礎資料表 driving table)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎資料表。當ORACLE處理多個表時，會運用排序及合并 的方式串連它們。首先，掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序，然後掃描第二個表(FROM子句中最後第二個表)，最後將所有從第 二個表中檢索出的記錄與第一個表中合適記錄進行合并。

     如果有3個以上的表串連查詢，那就需要選擇交叉表(intersection table)作為基礎資料表，交叉表是指那個被其他表所引用的表。

5.WHERE子句中的串連順序      ORACLE採用自下而上的順序解析WHERE子句，根據這個原理，表之間的串連必須寫在其他WHERE條件之前，那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

6.SELECT子句中避免使用 ‘ * ‘      當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用 ‘*‘ 是一個方便的方法。不幸的是，這是一個非常低效的方法。實際上，ORACLE在解析的過程中，會將‘*‘ 依次轉換成所有的列名，這個工作是通過查詢資料字典完成的，這意味著將耗費更多的時間。

7.減少訪問資料庫的次數      當執行每條SQL語句時，ORACLE在內部執行了許多工作：解析SQL語句，估算索引的利用率，綁定變數，讀資料區塊等等。由此可見，減少訪問資料庫的次數，就能實際上減少ORACLE的工作量。

8.使用DECODE函數來減少處理時間      使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表。

9.整合簡單，無關聯的資料庫訪問      如果你有幾個簡單的資料庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關係)

10.重複資料刪除記錄

11.用TRUNCATE替代DELETE      當刪除表中的記錄時，在通常情況下，復原段(rollback segments ) 用來存放可以被恢複的資訊。如果你沒有COMMIT事務，ORACLE會將資料恢複到刪除之前的狀態(準確地說是恢複到執行刪除命令之前的狀況)。

     而當運用TRUNCATE時，復原段不再存放任何可被恢複的資訊。當命令運行後，資料不能被恢複。因此很少的資源被調用，執行時間也會很短。

12.盡量多使用COMMIT      只要有可能，在程式中盡量多使用COMMIT，這樣程式的效能得到提高，需求也會因為COMMIT所釋放的資源而減少

     COMMIT所釋放的資源：

     A、 復原段上用於恢複資料的資訊。

     B、被程式語句獲得的鎖。

     C、 redo log buffer 中的空間。

     D、ORACLE為管理上述3種資源中的內部花費。

13.計算記錄條數      和一般的觀點相反，count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的。例如 COUNT(EMPNO)

14.用Where子句替換HAVING子句      避免使用HAVING子句，HAVING 只會在檢索出所有記錄之後才對結果集進行過濾。這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷。

15.減少對錶的查詢      在含有子查詢的SQL語句中，要特別注意減少對錶的查詢。

16.通過內建函式提高SQL效率。

17.使用表的別名(Alias)      當在SQL語句中串連多個表時，請使用表的別名並把別名首碼於每個Column上。這樣一來，就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

18.用EXISTS替代IN      在許多基於基礎資料表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯結。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。

19.用NOT EXISTS替代NOT IN      在子查詢中，NOT IN子句將執行一個內部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍曆)。為了避免使用NOT IN ，我們可以把它改寫成外串連(Outer Joins)或NOT EXISTS。

20.用表串連替換EXISTS      通常來說 ，採用表串連的方式比EXISTS更有效率

21.用EXISTS替換DISTINCT      當提交一個包含一對多表資訊(比如部門表和僱員表)的查詢時，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換

Oracle SQL 效能最佳化技巧