如何調優 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分鐘，以此類推。

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