多數開發環境（IDE）都能輕鬆顯示執行計畫，但畫面格式各不相同。本節描述的方法產出的正是本書所用的格式，只需要 Oracle 9iR2 或更新版本。

取得執行計畫#

在 Oracle 中檢視執行計畫分兩步：

  1. explain plan for —— 把執行計畫存入 PLAN_TABLE。
  2. 格式化並顯示該計畫。

建立並儲存執行計畫#

只要在 SQL 敘述前加上 explain plan for：

EXPLAIN PLAN FOR select * from dual;

這個指令可在任何開發環境或 SQL*Plus 中執行。它不會顯示計畫，而是存進名為 PLAN_TABLE 的資料表。

  • 10g 起：該表自動以全域暫存表的形式提供。
  • 更早的版本：需要在每個 schema 中自行建立。請 DBA 代為建立，或使用 Oracle 安裝目錄中的建表敘述：$ORACLE_HOME/rdbms/admin/utlxplan.sql（可在任何 schema 中執行）。

顯示執行計畫#

DBMS_XPLAN 套件（9iR2 引入）能格式化並顯示 PLAN_TABLE 中的計畫。顯示當前 session 中最後一次 explain 的計畫：

select * from table(dbms_xplan.display);
--------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT   |      |    1 |     2 |     2   (0)|
| 1 | TABLE ACCESS FULL  | DUAL |    1 |     2 |     2   (0)|
--------------------------------------------------------------

為了排版，本書在引用執行計畫時移除了其中部分欄位。

操作#

索引與資料表存取#

  • INDEX UNIQUE SCAN：只做 B-tree 走訪。當唯一性約束保證搜尋條件最多匹配一筆時使用。
  • INDEX RANGE SCAN：做 B-tree 走訪並沿葉節點鏈找出所有匹配條目。所謂的索引篩選述詞經常在此造成效能問題，下一節說明如何辨識它們。
  • INDEX FULL SCAN：依索引順序讀取整個索引（所有列）。當需要「依索引順序取得所有列」時（例如對應的 order by），資料庫可能採用此操作；最佳化工具也可能改用 INDEX FAST FULL SCAN 加一次額外排序。
  • INDEX FAST FULL SCAN：以磁碟儲存順序讀取整個索引。當所需欄位全在索引中時，通常用它取代全表掃描。與 TABLE ACCESS FULL 類似，它能受惠於多區塊讀取。
  • TABLE ACCESS BY INDEX ROWID：用前一步索引查找得到的 ROWID 從資料表取回一列。
  • TABLE ACCESS FULL：即全表掃描，以磁碟儲存順序讀取整張表（所有列與欄位）。雖然多區塊讀取大幅提升其速度，它仍是最昂貴的操作之一——除了高 IO，還必須檢視所有列，因此也消耗可觀的 CPU 時間。

Join#

join 操作一次只處理兩張表。查詢中有更多 join 時會依序執行：先兩張表，再把中間結果與下一張表 join。因此在 join 的脈絡下，「表」也可能意指「中間結果」。

  • NESTED LOOPS JOIN：從一張表取得結果，再為其中每一列查詢另一張表。
  • HASH JOIN：把 join 一側的候選記錄載入雜湊表，再以另一側的每一列去探測。
  • MERGE JOIN：像拉鍊一樣合併兩份已排序的清單，兩側都必須預先排序。

排序與分組#

  • SORT ORDER BY：依 order by 排序結果。需要大量記憶體具體化中間結果（非管線化）。
  • SORT ORDER BY STOPKEY：依 order by 排序結果的一個子集。用於無法管線化執行的 top-N 查詢。
  • SORT GROUP BY：先依 group by 欄位排序，第二步再彙總。需要大量記憶體具體化中間結果（非管線化）。
  • SORT GROUP BY NOSORT：對已排序的集合依 group by 彙總。不緩衝中間結果，以管線化方式執行。
  • HASH GROUP BY：用雜湊表分組。需要大量記憶體具體化中間結果（非管線化），且輸出沒有任何有意義的排序。

Top-N 查詢#

Top-N 查詢的效率取決於底層操作的執行模式。中止 SORT ORDER BY 這類非管線化操作時，效率極差。

  • COUNT STOPKEY：抓到所需列數時中止底層操作。
  • WINDOW NOSORT STOPKEY：使用視窗函式（over 子句），抓到所需列數時中止執行。

區分存取述詞與篩選述詞#

Oracle 用三種不同方式套用 where 子句（述詞）：

  • 存取述詞（顯示為 access）：表達葉節點走訪的起訖條件。
  • 索引篩選述詞（索引操作上的 filter）：只在葉節點走訪期間套用，不影響起訖條件、不縮小掃描範圍。
  • 資料表層級篩選述詞（資料表操作上的 filter）：欄位不屬於索引時，只能在資料表層級求值——資料庫必須先從表中載入該列。

用 DBMS_XPLAN 產生的執行計畫，會在表格下方的「Predicate Information」區塊顯示索引的使用方式：

------------------------------------------------------
| Id | Operation          | Name       | Rows | Cost |
------------------------------------------------------
| 0 | SELECT STATEMENT    |            |    1 | 1445 |
| 1 | SORT AGGREGATE      |            |    1 |      |
|* 2 |   INDEX RANGE SCAN | SCALE_SLOW | 4485 | 1445 |
------------------------------------------------------
Predicate Information (identified by operation id):
   2 - access("SECTION"=:A AND "ID2"=:B)
       filter("ID2"=:B)

述詞資訊的編號對應執行計畫的 Id 欄位；資料庫也會用星號標示帶有述詞資訊的操作。

這個取自「效能與擴展性」一章的例子，顯示一個同時帶有存取述詞與篩選述詞的 INDEX RANGE SCAN。Oracle 有個特殊之處：它會把某些篩選述詞也一併顯示為存取述詞（例如上面的 ID2=:B）。

因此上例的實際行為是：INDEX RANGE SCAN 掃過 "SECTION"=:A 的完整範圍，再對每一列套用 "ID2"=:B 篩選。

資料表層級的篩選述詞則顯示在對應的資料表存取操作上，例如 TABLE ACCESS BY INDEX ROWID 或 TABLE ACCESS FULL。

Backward 執行計畫 PostgreSQL Forward