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

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

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

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

（一）使用 EXPLAIN PLAN 命令

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

EXPLAIN PLAN FOR
<your_sql_statement>;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

例如，我們有一個(gè)簡(jiǎn)單的查詢(xún)語(yǔ)句，用于從員工表（employees）和部門(mén)表（departments）中檢索特定部門(mén)的員工信息：

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

（二）通過(guò) SQL Developer 工具查看

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

（三）啟用 AUTOTRACE 功能

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

SET AUTOTRACE ON;

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

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

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

SET AUTOTRACE OFF;

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

（一）操作類(lèi)型

全表掃描（TABLE ACCESS FULL）

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

索引掃描（INDEX SCAN）

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

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

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

哈希連接（HASH JOIN）

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

（二）執(zhí)行順序

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

（三）謂詞信息

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

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

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

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

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

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

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

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

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

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

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í)行計(jì)劃后，發(fā)現(xiàn)存在以下問(wèn)題：
對(duì)訂單表（orders）進(jìn)行了全表掃描，因?yàn)?order_date 列沒(méi)有合適索引，導(dǎo)致大量不必要的 I/O 操作，查詢(xún)效率低下。
在連接操作中，由于表之間的連接條件選擇性不是特別高，且沒(méi)有充分利用索引，嵌套循環(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ù)存在一定傾斜，部分類(lèi)別數(shù)據(jù)量遠(yuǎn)大于其他類(lèi)別?？紤]收集更精確的統(tǒng)計(jì)信息：

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

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

總結(jié)

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

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

最新評(píng)論