在 Oracle 數(shù)據(jù)庫中，執(zhí)行計劃能夠幫助我們深入了解 SQL 語句在數(shù)據(jù)庫內(nèi)部的執(zhí)行細節(jié)，進而優(yōu)化查詢性能、提升系統(tǒng)效率。無論是數(shù)據(jù)庫領(lǐng)域的新手，還是經(jīng)驗豐富的工程師，掌握執(zhí)行計劃的查看與分析方法都至關(guān)重要。

一、什么是執(zhí)行計劃

執(zhí)行計劃是 Oracle 數(shù)據(jù)庫優(yōu)化器為 SQL 語句生成的一種執(zhí)行藍圖，它描述了數(shù)據(jù)庫將如何檢索數(shù)據(jù)以滿足查詢要求。簡單來說，執(zhí)行計劃告訴我們 SQL 語句的各個步驟，例如通過哪些索引進行數(shù)據(jù)查找、表之間以何種連接方式關(guān)聯(lián)、數(shù)據(jù)如何排序等操作的先后順序。優(yōu)化器會基于數(shù)據(jù)庫對象的統(tǒng)計信息、SQL 語句的語法結(jié)構(gòu)以及數(shù)據(jù)庫的配置參數(shù)等因素，綜合考量來生成它認為最優(yōu)的執(zhí)行計劃。

二、查看執(zhí)行計劃的方法

（一）使用 EXPLAIN PLAN 命令

這是最基礎(chǔ)、也是最常用的查看執(zhí)行計劃的方式之一。它的語法如下：

EXPLAIN PLAN FOR
<your_sql_statement>;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

例如，我們有一個簡單的查詢語句，用于從員工表（employees）和部門表（departments）中檢索特定部門的員工信息：

EXPLAIN PLAN FOR
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

執(zhí)行上述代碼后，第二句查詢會以表格形式展示出詳細的執(zhí)行計劃。其中包括各操作的 ID、操作名稱（如 TABLE ACCESS FULL 表示全表掃描，INDEX RANGE SCAN 表示索引范圍掃描等）、對象名稱（涉及的表或索引）以及執(zhí)行順序等關(guān)鍵信息。

（二）通過 SQL Developer 工具查看

SQL Developer 是 Oracle 官方提供的一款功能強大的數(shù)據(jù)庫開發(fā)工具。在使用它執(zhí)行 SQL 語句時，可以方便地同時查看對應(yīng)的執(zhí)行計劃。只需在執(zhí)行 SQL 的窗口中，點擊 “解釋計劃” 按鈕（通常是一個帶有放大鏡和閃電標志的圖標），工具就會在下方的面板中以可視化的樹狀結(jié)構(gòu)展示執(zhí)行計劃。這種方式相較于命令行，更加直觀，易于理解。各個節(jié)點展示了詳細的操作信息，并且可以通過鼠標懸停查看更多細節(jié)，如謂詞信息（WHERE 子句中的過濾條件）等。

（三）啟用 AUTOTRACE 功能

在 SQL*Plus 環(huán)境下，我們可以啟用 AUTOTRACE 來查看執(zhí)行計劃及相關(guān)的執(zhí)行統(tǒng)計信息，如物理讀、邏輯讀、執(zhí)行時間等。首先需要確保當前用戶具有執(zhí)行 AUTOTRACE 相關(guān)權(quán)限，并且數(shù)據(jù)庫實例已正確配置。啟用 AUTOTRACE 的命令如下：

SET AUTOTRACE ON;

之后執(zhí)行 SQL 語句，例如：

SELECT * FROM customers WHERE customer_city = 'New York';

執(zhí)行完 SQL 后，除了返回查詢結(jié)果，還會輸出執(zhí)行計劃的概要信息以及上述提到的統(tǒng)計信息。這對于快速評估 SQL 語句的性能開銷非常有幫助。要關(guān)閉 AUTOTRACE 功能，使用：

SET AUTOTRACE OFF;

三、執(zhí)行計劃中的關(guān)鍵信息解讀

（一）操作類型

全表掃描（TABLE ACCESS FULL）

這意味著數(shù)據(jù)庫會讀取表中的所有行來滿足查詢條件。當沒有合適的索引可用，或者優(yōu)化器認為全表掃描的成本更低時，會選擇這種方式。例如，在一個數(shù)據(jù)量較小的表上進行沒有過濾條件或過濾條件選擇性很差的，全表掃描可能是最快的方法。但對于大表，全表掃描通常會導致大量的 I/O 操作，嚴重影響性能。

索引掃描（INDEX SCAN）

又分為索引唯一掃描（INDEX UNIQUE SCAN）、索引范圍掃描（INDEX RANGE SCAN）等。索引唯一掃描用于查找具有唯一鍵值的行，比如通過主鍵查詢單條記錄。索引范圍掃描則適用于基于某個范圍條件的查詢，如查詢某個時間段內(nèi)的數(shù)據(jù)，它會利用索引的有序性快速定位到符合條件的起始和結(jié)束位置，并掃描其間的索引條目。

嵌套循環(huán)連接（NESTED LOOPS）

這是一種常見的表連接方式，對于外部表的每一行，都會在內(nèi)層表中查找匹配的行。它適用于連接條件選擇性高、關(guān)聯(lián)表數(shù)據(jù)量較小的場景。優(yōu)點是能快速返回少量精確匹配的結(jié)果，但如果表數(shù)據(jù)量大，可能會產(chǎn)生大量的循環(huán)操作，性能急劇下降。

哈希連接（HASH JOIN）

先對一張表構(gòu)建哈希表，然后利用哈希函數(shù)快速查找另一張表中匹配的行。通常在連接大數(shù)據(jù)集時表現(xiàn)較好，尤其是當兩張表都比較大且沒有合適索引的情況下，哈希連接能通過減少數(shù)據(jù)比較次數(shù)來提高連接效率。

（二）執(zhí)行順序

執(zhí)行計劃中的操作 ID 標識了各操作的執(zhí)行順序，通常是從縮進少的節(jié)點開始，逐步向縮進多的節(jié)點推進。數(shù)字越小，執(zhí)行優(yōu)先級越高。通過觀察執(zhí)行順序，我們可以了解數(shù)據(jù)的流動方向，以及哪些操作是基礎(chǔ)，哪些是后續(xù)基于前面結(jié)果的進一步處理。例如，先進行表的訪問操作獲取原始數(shù)據(jù)，然后可能進行過濾、連接等操作，最后進行排序或聚合等滿足最終查詢需求的步驟。

（三）謂詞信息

謂詞即 WHERE 子句中的過濾條件，在執(zhí)行計劃中會顯示哪些謂詞用于索引查找，哪些用于最終結(jié)果的過濾。如果某個謂詞能夠有效利用索引，說明該過濾條件具有較好的效果，可以快速縮小數(shù)據(jù)檢索范圍。反之，如果謂詞只能在全表掃描后進行過濾，那可能需要考慮優(yōu)化過濾條件或添加合適索引。例如，“WHERE column_name> 100 AND column_name < 200” 這樣的范圍謂詞，若在索引列上，可能觸發(fā)索引范圍掃描；而 “WHERE function (column_name) = some_value”（函數(shù)作用于列上的條件），一般情況下會導致索引失效，引發(fā)全表掃描。

四、分析執(zhí)行計劃的技巧

（一）關(guān)注高成本操作

執(zhí)行計劃中的每個操作都有對應(yīng)的成本估算，通常以 COST 值表示，包括 CPU 成本和 I/O 成本。重點關(guān)注成本較高的操作，這些往往是性能瓶頸所在。比如，當發(fā)現(xiàn)一個全表掃描操作的成本占比很大，且表數(shù)據(jù)量龐大時，就需要思考是否可以通過創(chuàng)建合適索引、優(yōu)化查詢條件等方式來改變執(zhí)行計劃，降低成本。可以通過對比不同優(yōu)化方案下執(zhí)行計劃的成本變化，來評估優(yōu)化效果。

（二）結(jié)合數(shù)據(jù)量與分布情況

了解表的實際數(shù)據(jù)量大小以及數(shù)據(jù)在索引列上的分布狀況，對于準確分析執(zhí)行計劃至關(guān)重要。例如，一個索引在理論上看起來很完美，但如果表中的大部分數(shù)據(jù)在索引列上具有相同的值（數(shù)據(jù)傾斜），那么索引的選擇性就會大打折扣，優(yōu)化器可能會錯誤地選擇使用這個低效的索引，導致性能問題。此時，可能需要考慮收集更準確的統(tǒng)計信息，或調(diào)整查詢語句以適應(yīng)數(shù)據(jù)分布特點，如增加額外的過濾條件來減少數(shù)據(jù)傾斜的影響。

（三）對比不同執(zhí)行計劃版本

在對 SQL 語句進行優(yōu)化調(diào)整過程中，如修改索引、調(diào)整查詢結(jié)構(gòu)、更新數(shù)據(jù)庫統(tǒng)計信息等操作后，重新查看并對比執(zhí)行計劃的變化。觀察優(yōu)化措施是否達到預(yù)期效果，新的執(zhí)行計劃中是否消除了高成本操作，數(shù)據(jù)檢索路徑是否更加合理。通過這種迭代式的對比分析，逐步逼近最優(yōu)的查詢性能。

五、優(yōu)化執(zhí)行計劃的案例

假設(shè)我們有一個電商訂單數(shù)據(jù)庫，包含訂單表（orders）、訂單明細表（order_items）和產(chǎn)品表（products）。經(jīng)常執(zhí)行的查詢是獲取某個時間段內(nèi)特定產(chǎn)品類別的訂單總金額。初始查詢語句如下：

SELECT p.product_category, SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2023-01-31'
AND p.product_category = 'Electronics'
GROUP BY p.product_category;

使用 EXPLAIN PLAN 查看執(zhí)行計劃后，發(fā)現(xiàn)存在以下問題：
對訂單表（orders）進行了全表掃描，因為 order_date 列沒有合適索引，導致大量不必要的 I/O 操作，查詢效率低下。
在連接操作中，由于表之間的連接條件選擇性不是特別高，且沒有充分利用索引，嵌套循環(huán)連接的成本較高。
優(yōu)化方案：
在訂單表的 order_date 列上創(chuàng)建索引：

CREATE INDEX idx_order_date ON orders(order_date);

分析產(chǎn)品表（products）上 product_category 列的數(shù)據(jù)分布，發(fā)現(xiàn)該列數(shù)據(jù)存在一定傾斜，部分類別數(shù)據(jù)量遠大于其他類別?？紤]收集更精確的統(tǒng)計信息：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'your_schema', tabname => 'products');
END;

重新執(zhí)行查詢并查看執(zhí)行計劃，發(fā)現(xiàn)訂單表改為使用索引范圍掃描，大大減少了數(shù)據(jù)讀取量；連接操作也因為統(tǒng)計信息的更新，優(yōu)化器選擇了更合適的哈希連接方式，整體查詢性能提升了數(shù)倍，執(zhí)行時間從原來的幾十秒縮短到幾秒。

總結(jié)

Oracle 數(shù)據(jù)庫執(zhí)行計劃的查看與分析是數(shù)據(jù)庫優(yōu)化工作中的核心技能。通過熟練掌握多種查看執(zhí)行計劃的方法，深入解讀其中的關(guān)鍵信息，并運用有效的分析技巧，我們能夠精準定位 SQL 語句的性能問題，采取針對性的優(yōu)化措施。從創(chuàng)建合適索引、優(yōu)化查詢語句結(jié)構(gòu)，到確保準確的統(tǒng)計信息，每一個環(huán)節(jié)都可能成為提升數(shù)據(jù)庫性能的關(guān)鍵。持續(xù)實踐與經(jīng)驗積累，將幫助我們在面對復雜的數(shù)據(jù)庫環(huán)境時，游刃有余地優(yōu)化查詢性能，保障系統(tǒng)高效穩(wěn)定運行。

以上就是Oracle數(shù)據(jù)庫執(zhí)行計劃的查看與分析技巧的詳細內(nèi)容，更多關(guān)于Oracle執(zhí)行計劃的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論