本文為墨天輪數據庫管理服務團隊第140期技術分享，內容原創，作者為技術顧問陳洋，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。

一、執行計劃基礎概念

1.1 什麼是執行計劃？

執行計劃（Execution Plan）是Oracle數據庫的查詢優化器（Query Optimizer）為一條SQL語句制定的執行路徑。它以樹狀或表格的形式，清晰地展示了數據庫為了獲取查詢結果所執行的一系列操作步驟。每一條SQL語句在執行前，Oracle都會先解析並生成一個它認為最高效的執行計劃。

一個典型的執行計劃包含以下核心信息：

• 操作ID（ID）：每個操作步驟的唯一標識符。
• 操作類型（Operation）：具體的數據處理方式，如表掃描（TABLE ACCESS）、索引掃描（INDEX SCAN）、連接（JOIN）等。
• 對象名稱（Name）：操作所涉及的表、索引或其他數據庫對象的名稱。
• 預估行數（Rows/E-Rows）：優化器預測該操作將返回的數據行數，即基數（Cardinality）。
• 成本（Cost）：優化器估算的執行該操作所需的資源消耗，通常是一個相對值，用於比較不同執行計劃的優劣。
• 時間（Time）：預估的執行時間。 

1.2 執行計劃的執行順序

執行計劃的閲讀和執行遵循“最右最內最先執行”的原則。具體來説，其執行順序依據操作的縮進層次來判斷：

• 縮進最深的操作最先執行：在樹狀結構的執行計劃中，縮進最深的節點代表最底層的操作，這些操作會最先被執行。
• 同級操作自上而下執行：如果多個操作處於相同的縮進級別，則按照從上到下的順序依次執行。 

例如，在一個嵌套循環連接（Nested Loops Join）中，通常會先執行對驅動表（driving table）的掃描（通常是縮進最深、位於上方的操作），然後根據驅動表返回的每一行數據，再去訪問被驅動表（probed table）。

通過理解執行順序，可以清晰地追蹤數據的流動過程，判斷哪一步操作是性能瓶頸所在。

1.3 執行計劃的重要組成部分

除了基本的操作流程，執行計劃還包含一些關鍵的謂詞（Predicate）和備註（Note）信息，它們對於深入分析SQL性能至關重要。

• 訪問謂詞（Access Predicate）
  訪問謂詞是指那些能夠利用索引（Index）、哈希表等高效數據結構來快速定位記錄的過濾條件。這些條件通常出現在 WHERE 子句中，並且其涉及的列上建有索引。優化器利用訪問謂詞來縮小數據掃描範圍，從而極大地提升查詢效率。
• 過濾謂詞（Filter Predicate）
  過濾謂詞則是在數據被訪問之後用於篩選記錄的條件。當一個過濾條件無法使用索引時（例如，列上沒有索引，或使用了函數導致索引失效），數據庫必須先將數據塊讀入內存，然後再逐行應用這些過濾條件。如果一個昂貴的操作（如全表掃描）之後跟着一個過濾謂詞，且該謂詞過濾掉了大部分數據，這通常意味着存在優化空間，例如可以考慮為相關列創建索引。
• 備註部分（Note Section）
  執行計劃末尾的 Note 部分提供了關於優化器決策的額外上下文信息，是診斷複雜問題的重要線索。常見的備註信息包括：
• 動態採樣（Dynamic Sampling）：當表或索引缺少統計信息時，優化器會動態採樣一部分數據來估算基數。出現此備註通常提示需要收集統計信息。
• SQL Profile 或 Plan Baselines：表示該SQL使用了SQL Profile或基線計劃來固定執行計劃，這有助於保證性能的穩定性。
• 自適應計劃（Adaptive Plans）：從Oracle 12c開始引入的特性。優化器在執行過程中可以根據實際返回的數據量動態調整執行計劃，例如在嵌套循環和哈希連接之間自動切換。
• Hint報告（Hint Report）：從Oracle 19c開始支持，顯示代碼中使用的Hint是否生效，幫助開發者驗證Hint的正確性。

通過仔細分析這些組成部分，可以全面地理解優化器的決策依據，從而更精準地進行SQL調優。

二、如何生成執行計劃

要分析和優化SQL，首先需要獲取其執行計劃。Oracle提供了多種方法來生成和顯示執行計劃，主要分為兩類：一類是預測執行計劃，即在不實際執行SQL的情況下由優化器估算出的計劃；另一類是實際執行計劃，即SQL在數據庫中真實執行後採納的計劃。實際執行計劃通常更具參考價值，因為它反映了真實的執行環境和數據分佈。

2.1 預測執行計劃：EXPLAIN PLAN 命令

EXPLAIN PLAN 是最基礎的獲取執行計劃的命令。它會將優化器生成的計劃存儲在一張名為 PLAN_TABLE 的默認表中，但不會實際執行該SQL語句。這使得它非常適合在生產環境中快速評估一個查詢的計劃，而不用擔心對系統造成實際負載。

操作步驟：

1. 生成計劃：使用 EXPLAIN PLAN FOR 關鍵字，後跟需要分析的SQL語句。

EXPLAIN PLAN FOR
SELECT p.prod_category, AVG(s.amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY p.prod_category;

2. 顯示計劃：使用 DBMS_XPLAN.DISPLAY 函數從 PLAN_TABLE 中讀取並格式化輸出執行計劃。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'BASIC'));

優點：安全、快速，不消耗實際執行資源。

缺點：生成的計劃是基於優化器的估算，可能與實際執行的計劃存在差異，尤其是在涉及綁定變量、臨時表或複雜環境時。

2.2 實際執行計劃：V$SQL\_PLAN 與 display\_cursor

為了獲取SQL語句在真實執行時所採用的計劃，我們可以查詢數據庫的共享池（Shared Pool）。當一條SQL被執行後，其執行計劃會被緩存到 V$SQL_PLAN 這個動態性能視圖中。通過 DBMS_XPLAN.DISPLAY_CURSOR 函數，可以方便地從共享池中提取和展示最近執行過的SQL的實際計劃。

操作步驟：

1. 執行SQL語句：首先，正常執行需要分析的SQL語句。為了獲取詳細的運行時統計信息（如實際返回行數 A-Rows），建議在會話級別開啓統計信息收集，或使用 gather_plan_statistics Hint。

-- 方法一：使用Hint
SELECT /*+ gather_plan_statistics */ p.prod_category, AVG(s.amount_sold)
FROM sales s, products p
WHERE p.prod_id = s.prod_id
GROUP BY p.prod_category;

-- 方法二：設置會話參數
ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT ... ; -- 運行你的SQL

2. 顯示計劃：執行完畢後，調用 DBMS_XPLAN.DISPLAY_CURSOR 函數來顯示當前會話中最後一條執行的SQL的計劃。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

• NULL, NULL 表示顯示當前會話最後執行的SQL。
• ALLSTATS LAST 是一個強大的格式選項，它會同時顯示優化器的預估值（E-Rows）和實際執行的統計信息（A-Rows），這對於判斷基數估算的準確性至關重要。

優點：反映的是真實的執行情況，數據更準確，是性能調優的首選依據。

缺點：必須先完整執行一次SQL，對於運行時間很長的慢查詢，獲取計劃的時間成本較高。

2.3 DBMS\_XPLAN 的Format參數選項

DBMS_XPLAN 提供了一系列格式化參數，用於控制執行計劃顯示的詳細程度。根據不同的分析需求，選擇合適的參數可以使輸出信息更具可讀性。

•  BASIC ：最基本的信息，僅顯示操作ID、操作名稱和對象名。適合快速瞭解大致的執行流程。
•  TYPICAL：默認選項，顯示了絕大部分常用信息，包括預估行數、成本、時間、訪問謂詞和過濾謂詞等。是日常分析中最常用的格式。
•  ALL：顯示 TYPICAL 的所有信息，並額外展示字段投影（Projection）信息和遠程SQL（Remote SQL）等細節。
•  ADVANCED：顯示所有可用的信息，包括 ALL 的內容以及 Outline（優化器Hint的集合）和SQL計劃基線（SQL Plan Baseline）等高級信息。適合進行深度分析或固定執行計劃。

此外，還可以通過 + 號組合使用其他關鍵字，例如：

•  +OUTLINE：在 TYPICAL 或 ALL 的基礎上，顯示 Outline 數據，即優化器內部生成的Hint集合，可用於手動固定計劃。
•  +PEEKED_BINDS：顯示綁定變量的值，有助於診斷因“綁定變量窺探（Bind Variable Peeking）”引發的性能問題。

在實踐中，'ALLSTATS LAST' 和 'TYPICAL +OUTLINE' 是進行SQL性能分析時最為實用和推薦的組合。

三、執行計劃解讀核心要點

成功解讀執行計劃是SQL優化的關鍵。在分析一個執行計劃時，我們不能僅僅停留在表面，而是要深入理解其中的四個核心要素：基數（Cardinality）、訪問路徑（Access Paths）、連接方法（Join Methods） 和 連接順序（Join Order）。這四個要素共同決定了SQL的執行效率。優化器的工作就是在這四個方面做出它認為最優的選擇組合。而我們SQL調優的過程，很大程度上就是檢查優化器的這些選擇是否合理，並在其出錯時進行干預。

3.1 基數（Cardinality）：一切優化的基礎

1. 什麼是基數？

基數（Cardinality），在執行計劃中通常以 Rows 或 E-Rows（Estimated Rows）表示，是優化器對某個操作步驟預計將返回的行數的估算值。這個數字是整個執行計劃的基石，其準確性直接影響到後續所有決策的質量。

為什麼基數如此重要？

• 影響訪問路徑的選擇：如果優化器認為查詢結果集很小（基數小），它可能傾向於選擇索引掃描（Index Scan）；反之，如果認為結果集很大（基數大），則可能選擇全表掃描（Full Table Scan）。
• 影響連接方法的選擇：例如，在連接兩個表時，如果優化器估算一個表的基數很小，它可能會選擇嵌套循環連接（Nested Loops Join）；如果兩個表的基數都很大，則哈希連接（Hash Join）通常是更優的選擇。
• 影響連接順序的選擇：優化器總是試圖將能夠過濾掉最多數據的操作放在前面執行。一個錯誤的基數估算會導致它選擇一個低效的連接順序，使得中間結果集異常龐大。

簡而言之，一個錯誤的基數估算，幾乎必然導致一個低效的執行計劃。

2. 如何驗證基數估算的準確性？

判斷基數估算是否準確，最直接的方法就是比較 預估行數（E-Rows） 和 實際行數（A-Rows）。要獲取實際行數，你需要在執行SQL時收集運行時統計信息。

操作方法：

• 方法一：使用 gather_plan_statistics Hint

SELECT /*+ gather_plan_statistics */ p.prod_name, SUM(s.quantity_sold)
FROM sales s, products p
WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name;

• 方法二：設置會話的 statistics_level 參數

ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- 執行你的SQL

執行完畢後，通過 DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST') 查看執行計劃。此時，計劃中會同時出現 E-Rows 和 A-Rows 兩列。如果二者差異巨大（例如，相差一個數量級以上），就説明優化器的基數估算出現了嚴重偏差，需要進一步排查原因。

3. 基數估算錯誤的原因與解決方法

3.2 訪問路徑（Access Paths）：如何獲取數據

訪問路徑決定了Oracle如何從數據庫中檢索數據。選擇正確的訪問路徑是高性能SQL的基礎。

常見的訪問路徑類型：

常見訪問路徑問題：

• 不必要的全表掃描：當查詢只返回少量數據時，全表掃描通常是低效的。可能的原因包括：相關列上沒有索引、索引失效（如使用了函數）、統計信息不準導致優化器做出錯誤判斷等。
• 選擇了錯誤的索引：當一個表上有多個索引時，優化器可能會因為基數估算錯誤或統計信息問題而選擇了一個過濾效果較差的索引（即選擇性差的索引）。

3.3 連接方法（Join Methods）：如何關聯表

當查詢涉及多個表時，優化器需要選擇一種方法來將它們連接起來。

常見連接方法：

常見連接方法問題：

• 錯誤地選擇了嵌套循環：當驅動表的結果集遠大於預期時，嵌套循環的效率會急劇下降，因為它會導致對內表的訪問次數遠超預期。這通常是由於對驅動表的基數估算嚴重偏低所致。
• 出現笛卡爾連接（Cartesian Join）：當執行計劃中出現 MERGE JOIN CARTESIAN 或 CROSS JOIN 時，通常意味着連接條件缺失或有誤，導致生成了笛卡爾積。這會使結果集呈指數級增長，必須立即修正。

3.4 連接順序（Join Order）：先連哪張表

連接順序決定了多表連接時的執行次序。一個好的連接順序應該優先連接那些能夠最大程度過濾數據的表，從而使後續操作處理的中間結果集儘可能小。

如何查看連接順序？

在執行計劃中，驅動表通常是第一個被訪問的表（在同級操作中位於上方）。要明確地查看優化器決定的連接順序，可以使用 +OUTLINE 格式選項，在 Outline Data 部分可以看到 LEADING 或 ORDERED Hint，其中指明瞭表的連接順序。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL +OUTLINE'));

連接順序錯誤的原因：

• 基數估算錯誤：這是導致連接順序不佳的根本原因。如果優化器低估了某張表的過濾效果，就可能將它放在連接順序的後面，導致中間結果集膨脹。

通過對這四個核心要點的系統性分析，你就可以像一位偵探一樣，從執行計劃的蛛絲馬跡中定位到SQL性能的真正瓶頸，並採取有效的優化措施。

四、實用工具和命令

除了基礎的 EXPLAIN PLAN 和 DBMS_XPLAN，Oracle生態系統及各類數據庫客户端工具提供了豐富的輔助功能，可以幫助我們更直觀、更高效地分析和監控SQL執行計劃與性能。

4.1 SQL Monitor：實時性能監控利器

SQL Monitor 是Oracle數據庫內置的一個強大工具，尤其適用於監控長時間運行的SQL。當一條SQL的執行時間超過5秒（默認閾值），或者使用了 /*+ MONITOR */ Hint 時，Oracle會自動生成一份實時的SQL監控報告。

這份報告以可視化的方式展示了執行計劃的每一個步驟，並實時更新每個操作的進度、CPU時間、I/O等待、實際返回行數（A-Rows）等關鍵性能指標。它是比較預估基數（E-Rows）和實際返回行數（A-Rows）最直觀、最簡單的方法。

如何使用：

1. 觸發監控：對於長時間運行的SQL，監控會自動啓動。對於短查詢，可以手動添加 MONITOR Hint。

SELECT /*+ MONITOR */ * FROM employees WHERE department_id = 90;

2. 查看報告：可以通過Oracle Enterprise Manager (OEM) Cloud Control 的 “SQL監控” 頁面查看，也可以通過 DBMS_SQLT.REPORT_SQL_MONITOR() 函數生成HTML或文本格式的報告。

-- 生成HTML報告
SELECT DBMS_SQLT.REPORT_SQL_MONITOR(
    sql_id       => 'your_sql_id',
    report_level => 'ALL',
    type         => 'HTML'
) AS report
FROM dual;

4.2 其他常用工具

• Autotrace：SQL*Plus中的一個經典工具，可以通過 SET AUTOTRACE ON 命令開啓。在SQL執行後，它會自動顯示執行計劃和會話的統計信息（如邏輯讀、物理讀、排序次數等）。它簡單易用，適合快速評估。
• SQL Developer / PL/SQL Developer / Toad：這些主流的第三方數據庫客户端工具都內置了強大的SQL分析功能。它們通常提供圖形化的執行計劃展示界面，用户可以方便地點擊計劃中的節點來查看詳細信息，極大地提升了可讀性和分析效率。
• TKPROF：一個用於格式化SQL跟蹤（SQL Trace）文件的命令行工具。通過開啓會話的10046事件進行跟蹤，可以捕獲到SQL執行過程中的所有等待事件、CPU時間、執行計劃等詳細信息。TKPROF 將原始的跟蹤文件轉換成易於閲讀的報告，是進行深度性能診斷的終極武器。

4.3 Oracle 12c及更高版本的自適應特性

從Oracle 12c開始，優化器引入了自適應查詢優化（Adaptive Query Optimization），使得執行計劃在運行時具備了一定的“自我修正”能力。

• 自適應計劃（Adaptive Plans）：這是最具代表性的特性。在SQL執行前，優化器會預先準備一個主計劃和幾個備選的子計劃（例如，同時備好嵌套循環和哈希連接兩種方案）。在執行過程中，當優化器發現實際返回的行數與估算嚴重不符時，它可以在執行到某個臨界點（STATISTICS COLLECTOR 操作）時，自動切換到更合適的子計劃。這大大提高了執行計劃的準確性和魯棒性，減少了因基數估算錯誤導致的性能問題。
• SQL計劃指令（SQL Plan Directives）：當優化器發現持續存在基數估算錯誤時，它會自動創建一條“指令”。在後續的SQL解析中，這條指令會提示優化器使用動態採樣或其他技術來獲取更準確的統計信息，從而生成更優的計劃。

瞭解這些新特性，有助於我們理解在高版本Oracle中執行計劃可能發生的動態變化，並更好地利用它們來提升數據庫性能。

五、案例分析與優化實戰

理論知識最終要應用於實踐。本章將通過一個系統性的分析流程和幾個典型案例，展示如何將前面介紹的知識點融會貫貫，解決實際的SQL性能問題。

5.1 分析執行計劃的系統性步驟

當你拿到一個慢查詢的執行計劃時，可以遵循以下五個步驟進行系統性分析，層層遞進，定位問題根源。

1. 檢查基數估算的準確性（E-Rows vs. A-Rows）

• 這是最重要、最優先的一步。使用 ALLSTATS LAST 格式獲取包含實際行數（A-Rows）的執行計劃。
• 從上到下（或從內到外）逐行比較預估行數（E-Rows）和實際行數（A-Rows）。

• 一旦發現某一步操作的 E-Rows 和 A-Rows 出現巨大差異（通常指一個數量級以上），那麼後續的所有問題，如錯誤的連接方法或連接順序，幾乎都可以歸因於此。

  此時，你的首要任務就是解決這個基數估算不準的問題。

2. 檢查訪問路徑是否最優

• 對於返回少量數據的查詢，是否使用了低效的全表掃描（Full Table Scan）？
• 對於返回大量數據的查詢，是否錯誤地使用了索引掃描（Index Scan），導致了大量的隨機I/O和回表操作？
• 表上是否存在更優的索引，但優化器沒有選擇？這通常也與基數估算不準有關。

3. 檢查連接方法是否恰當

• 是否出現了嵌套循環（Nested Loops）連接一個非常大的驅動表的情況？這通常是性能殺手，根源在於對驅動表的基數估算過低。
• 是否出現了不應有的笛卡爾連接（Cartesian Join）？立即檢查SQL的連接條件是否遺漏。
• 對於大數據量的等值連接，是否正確地選擇了哈希連接（Hash Join）？

4. 檢查連接順序是否合理

• 優化器選擇的驅動表是否是過濾性最好的表（即能返回最少行數的表）？
• 一個糟糕的連接順序會導致中間結果集急劇膨脹，增加後續操作的負擔。這同樣與基數估算密切相關。

5. 檢查其他等待事件和特殊操作

• 執行計劃中是否出現了不尋常的操作，如 FILTER 操作符，其下可能有非常耗時的子查詢？
• 結合SQL監控報告或TKPROF，查看SQL執行過程中主要的等待事件是什麼，例如是CPU繁忙還是I/O等待，從而輔助判斷瓶頸所在。

5.2 常見問題與解決方法彙總

在實踐中，絕大多數性能問題都源於基數估算不準。以下是針對此類問題的實用解決方法，可以根據具體場景靈活選用。

問題場景1：統計信息缺失或陳舊

• 症狀：執行計劃的 Note 部分出現 Dynamic Sampling 提示；E-Rows 與 A-Rows 差異巨大。

• 解決方法：

  DBMS_STATS.GATHER_TABLE_STATS：立即為相關的表、索引和列收集統計信息。這是最直接、最根本的解決方案。

問題場景2：數據傾斜導致估算不準

• 症狀：對於某個特定值的查詢性能很好，但對於另一個值的查詢則非常慢（即“綁定變量窺探”問題）；E-Rows 與 A-Rows 在特定值下差異巨大。

• 解決方法：

  創建直方圖（Histogram）：為存在數據傾斜的列創建直方圖，讓優化器瞭解數據分佈的細節。

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'schema_name',
    tabname => 'table_name',
    method_opt => 'FOR COLUMNS size 254 column_name'

問題場景3：對列使用函數或複雜表達式

• 症狀：WHERE 子句中包含 TRUNC(date\_col)、UPPER(string\_col) 或 col1 + col2 > 100 等表達式，導致索引失效，E-Rows 估算為硬編碼的猜測值。
• 解決方法（按推薦順序）：

1. 改寫查詢：儘可能將查詢改寫為不使用函數的形式，例如將 TRUNC(creation_date) = TO_DATE('2023-01-01') 改為 creation_date >= TO_DATE('2023-01-01') AND creation_date < TO_DATE('2023-01-02')，以便利用 creation_date 列上的索引。
2. 創建函數索引/擴展統計信息：如果無法改寫，可以為該函數表達式創建專門的索引或擴展統計信息。

-- 函數索引
CREATE INDEX idx_func ON table_name (TRUNC(date_col));

-- 擴展統計信息
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(NULL, 'table_name', '(TRUNC(date_col))') FROM DUAL;

3. 使用動態採樣：在SQL中添加 /*+ dynamic_sampling(4) */ Hint，強制優化器在解析時進行採樣。級別4通常是一個不錯的起點。
4. 使用 CARDINALITY Hint：如果你能準確預知返回的行數，可以直接通過Hint告訴優化器。這是最後的手段，因為它會硬編碼邏輯。

SELECT /*+ CARDINALITY(table_alias 10) */ ... -- 提示優化器該表返回10行

問題場景4：多表關聯導致估算不準

• 症狀：多個表連接後，中間結果集的 E-Rows 與 A-Rows 嚴重不符。
• 解決方法：

1. 檢查單個表的基數估算：首先確保參與連接的每個表的單表基數估算是準確的。
2. 創建列組的擴展統計信息：如果連接條件或過濾條件涉及多個列，為這些列組創建擴展統計信息。
3. 使用 SQL Tuning Advisor 或 SQL Profile：運行SQL調優顧問，它可能會建議創建一個SQL Profile。SQL Profile可以存儲一組輔助統計信息和修正因子，專門用於修正特定SQL的基數估算，而無需修改SQL本身或全局統計信息，是解決複雜SQL估算不准問題的強大武器。

墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。
墨天輪數據庫服務官網：https://www.modb.pro/service