如何調優 Oracle SQL系列文章：查詢優化器介紹

如何調優 Oracle SQL系列文章 第四篇： 查詢優化器概念之查詢優化器介紹 。

4 查詢優化器概念

本章描述了與查詢優化器相關的最重要的概念，及其主要元件。

4.1 查詢優化器介紹

查詢優化器（簡稱為優化器）是內建資料庫軟體，用於確定 SQL 語句訪問請求資料的最有效方法。

4.1.1 查詢優化器的用途

優化程式嘗試為 SQL 語句生成最佳執行計劃。

優化程式在所有考慮的候選計劃中選擇成本最低的計劃。優化程式使用可用的統計資訊來計算成本。對於給定環境中的特定查詢，成本計算考慮了查詢執行的因素，例如I/O、CPU和通訊。

例如，查詢職位為經理的員工的資訊。如果優化程式統計資訊表明80％的員工是經理，那麼優化程式可能會認為全表掃描最有效。但是，如果統計資訊表明很少有員工是經理，那麼通過讀取索引後根據 rowid 進行回表訪問可能比全表掃描更有效。

由於資料庫有許多內部統計資訊和工具可供使用，因此優化器通常比使用者更有利於確定語句執行的最佳方法。因此，所有 SQL 語句都使用優化程式。

4.1.2 基於成本的優化

查詢優化是選擇執行 SQL 語句的最有效方法的過程。

SQL是一種非過程語言，因此優化器可以按任何順序自由合併，重組和處理。資料庫根據收集的有關訪問資料的統計資訊優化每個 SQL 語句。優化器通過檢查多個訪問方法（如全表掃描或索引掃描），不同的連線方法（如巢狀迴圈和雜湊連線），不同的連線順序以及可能的轉換來確定 SQL 語句的最佳計劃。

對於給定的查詢和環境，優化程式會為可能的計劃的每個步驟分配相對數字成本，然後將這些值計算在一起以生成計劃的總體成本估算。在計算替代計劃的成本之後，優化程式選擇具有最低成本估算的計劃。出於這個原因，優化器有時被稱為基於成本的優化器（CBO），以將其與基於規則的優化器（RBO）進行對比。

注意：
優化程式可能不會從一個版本的Oracle資料庫到下一個版本做出相同的決策。 
在最新版本中，優化器可能會做出不同的決定，因為它可以獲得更你全面的資訊，並且可以進行更多的優化器轉換。

4.1.3 執行計劃

執行計劃描述了 SQL 語句的推薦執行方法。

該計劃顯示了 Oracle 資料庫用於執行 SQL 語句的步驟的組合。每個步驟要麼從資料庫物理上檢索資料行，要麼為發出語句的使用者準備資料行。

執行計劃顯示整個計劃的成本（在第0行中）和每個單獨的操作。成本是一個內部單位，執行計劃僅僅是顯示它，以便與其它計劃進行比較。 因此，您無法調整或更改成本值。

在下圖中，優化程式為輸入 SQL 語句生成兩個可能的執行計劃，使用統計資訊估算其成本，比較其成本，然後選擇成本最低的計劃。

圖4-1 執行計劃

4.1.3.1 查詢塊

優化器的輸入是 SQL 語句的解析表示。

原始 SQL 語句中的每個 SELECT 塊由查詢塊在內部表示。 查詢塊可以是頂級語句，子查詢或未合併檢視。

示例4-1查詢塊

以下SQL語句由兩個查詢塊組成。 括號中的子查詢是內部查詢塊。 外部查詢塊（SQL語句的其餘部分）檢索由子查詢提供ID的部門中的員工的名稱。 查詢表單確定查詢塊如何相互關聯。

SELECT
 first_name, last_name
FROM
   hr.employees
WHERE
  department_id 
IN
(
SELECT
 department_id 
        
FROM
   hr.departments      
WHERE
  location_id = 1800
);

4.1.3.2 查詢子計劃

對於每個查詢塊，優化器生成查詢子計劃。

資料庫從下到上分別優化查詢塊。 因此，資料庫首先優化最裡面的查詢塊併為其生成子計劃，然後生成表示整個查詢的外部查詢塊。

查詢塊的可能計劃數與FROM子句中的物件數成比例。該數字隨著物件的數量呈指數增長。例如，五個表的連線的可能計劃明顯高於兩個表的連線的可能計劃。

4.1.3.3 優化器的類比

優化器可以比作是線上旅行顧問。

騎自行車的人想要知道從A點到B點的最有效的自行車路線。查詢就像指令“我需要從A點到B點的最有效路線”或“我需要從A點到到B點經過C點的最有效路線”。“旅行顧問使用內部演算法來確定最有效的路線，該演算法依賴於速度和難度等因素。騎車人可以通過使用諸如“我想盡快到達”或“我希望最簡單的騎行”等指令來影響旅行顧問的決定。

在這個類比中，執行計劃是旅行顧問生成的一個可能的路由。在內部，顧問可以將整個路由劃分為幾個子路由(子計劃)，並分別計算每個子路由的效率。例如，旅行顧問可能會評估一箇中等難度的子路由為15分鐘，另一個難度最小的子路由為22分鐘，以此類推。

顧問根據使用者指定的目標以及有關道路和交通狀況的可用統計資料，選擇最有效（最低成本）的總體路線。統計越準確，建議越好。例如，如果顧問無法獲取交通擁堵，道路封閉和惡劣的道路狀況等資訊，那麼推薦的路線可能效率低（成本高）。

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