在 Oracle 數(shù)據(jù)庫中，查看執(zhí)行計劃是優(yōu)化 SQL 語句性能的重要工具。以下是幾種常用的查看執(zhí)行計劃的方法及其優(yōu)劣比較：

1. 使用 EXPLAIN PLAN FOR 和 DBMS_XPLAN.DISPLAY

方法

執(zhí)行 EXPLAIN PLAN FOR 語句：

EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE your_column = 'some_value';

查看執(zhí)行計劃：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

優(yōu)點(diǎn)

• 簡單易用：適用于大多數(shù)情況，操作簡單。
• 詳細(xì)信息：可以提供詳細(xì)的執(zhí)行計劃信息，包括操作類型、成本、行數(shù)等。

缺點(diǎn)

• 不反映實際執(zhí)行：EXPLAIN PLAN 只是模擬執(zhí)行計劃，不一定反映實際執(zhí)行情況。
• 需要權(quán)限：需要 EXPLAIN PLAN 權(quán)限。

2. 使用 DBMS_XPLAN.DISPLAY_CURSOR

方法

執(zhí)行 SQL 語句：

SELECT * FROM your_table WHERE your_column = 'some_value';

查看執(zhí)行計劃：

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

優(yōu)點(diǎn)

• 反映實際執(zhí)行：查看的是實際執(zhí)行的計劃，更能反映真實的性能情況。
• 詳細(xì)統(tǒng)計信息：可以提供實際的執(zhí)行統(tǒng)計信息，如 I/O 次數(shù)、CPU 時間等。

缺點(diǎn)

• 需要執(zhí)行 SQL：必須先執(zhí)行 SQL 語句，才能查看執(zhí)行計劃。
• 依賴共享池：只能查看在共享池中的 SQL 語句的執(zhí)行計劃。

3. 使用 AUTOTRACE（僅限 SQL*Plus）

方法

啟用 AUTOTRACE：

SET AUTOTRACE ON EXPLAIN;

執(zhí)行 SQL 語句：

SELECT * FROM your_table WHERE your_column = 'some_value';

禁用 AUTOTRACE：

SET AUTOTRACE OFF;

優(yōu)點(diǎn)

• 集成在 SQL*Plus：適用于 SQL*Plus 用戶，操作簡便。
• 即時反饋：執(zhí)行 SQL 語句時立即顯示執(zhí)行計劃。

缺點(diǎn)

• 僅限 SQL*Plus：只能在 SQL*Plus 中使用。
• 功能有限：不如 DBMS_XPLAN.DISPLAY 提供的信息詳細(xì)。

4. 使用 V$SQL_PLAN 視圖

方法

找到 SQL 語句的 SQL_ID：

SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE '%your_sql_statement%';

查詢執(zhí)行計劃：

SELECT * FROM v$sql_plan WHERE sql_id = 'your_sql_id';

優(yōu)點(diǎn)

• 靈活性高：可以直接查詢視圖，靈活度高。
• 實時信息：可以查看當(dāng)前正在執(zhí)行的 SQL 語句的執(zhí)行計劃。

缺點(diǎn)

• 復(fù)雜性：需要手動查詢視圖，操作相對復(fù)雜。
• 信息冗余：返回的信息較多，需要篩選有用的部分。

5. 使用 Oracle Enterprise Manager (OEM)

方法

• 登錄 OEM。
• 導(dǎo)航到 SQL 性能頁面。
• 輸入 SQL 語句并查看執(zhí)行計劃。

優(yōu)點(diǎn)

• 圖形界面：提供圖形化的用戶界面，易于理解和操作。
• 綜合信息：可以查看多種性能指標(biāo)，不僅僅是執(zhí)行計劃。

缺點(diǎn)

• 需要 OEM：需要安裝和配置 Oracle Enterprise Manager。
• 資源消耗：圖形界面可能消耗更多系統(tǒng)資源。

6. 使用 DBMS_XPLAN.DISPLAY_AWR

方法

找到 SQL 語句的 SQL_ID 和 PLAN_HASH_VALUE：

SELECT sql_id, plan_hash_value FROM dba_hist_sqlstat WHERE sql_text LIKE '%your_sql_statement%';

查詢執(zhí)行計劃：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', 'your_plan_hash_value'));

優(yōu)點(diǎn)

• 歷史信息：可以查看 AWR 中的歷史執(zhí)行計劃，有助于長期性能分析。
• 詳細(xì)統(tǒng)計：提供詳細(xì)的執(zhí)行統(tǒng)計信息。

缺點(diǎn)

• 需要 AWR：需要 AWR 功能開啟，且需要相應(yīng)的權(quán)限。
• 復(fù)雜性：操作相對復(fù)雜，需要查找 SQL_ID 和 PLAN_HASH_VALUE。

7. 使用事件 10046 跟蹤

方法

啟用事件 10046 跟蹤：

對于當(dāng)前會話：

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

對于特定的會話（假設(shè) SID 為 123，SERIAL# 為 456）：

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, TRUE);

執(zhí)行 SQL 語句：

SELECT * FROM your_table WHERE your_column = 'some_value';

禁用事件 10046 跟蹤：

對于當(dāng)前會話：

ALTER SESSION SET EVENTS '10046 trace name context off';

對于特定的會話（假設(shè) SID 為 123，SERIAL# 為 456）：

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, FALSE);

查看跟蹤文件：

查找跟蹤文件的位置，通常在

user_dump_dest

參數(shù)指定的目錄下。

使用

tkprof

工具格式化跟蹤文件：

tkprof trace_file.trc output_file.txt explain=your_username/your_password

查看生成的 output_file.txt 文件，其中包含詳細(xì)的執(zhí)行計劃和性能信息。

優(yōu)點(diǎn)

• 詳細(xì)信息：提供詳細(xì)的執(zhí)行計劃、執(zhí)行時間和等待事件等信息，有助于深入分析性能問題。
• 靈活性：可以針對特定的會話或當(dāng)前會話啟用跟蹤。
• 歷史信息：可以保留長時間的跟蹤信息，便于后續(xù)分析。

缺點(diǎn)

• 性能開銷：啟用跟蹤會增加系統(tǒng)開銷，特別是在高負(fù)載情況下。
• 復(fù)雜性：操作相對復(fù)雜，需要手動啟用和禁用跟蹤，以及使用 tkprof 格式化跟蹤文件。
• 文件管理：需要管理和清理生成的跟蹤文件，以免占用過多磁盤空間。

8. 使用 STATISTICS_LEVEL=ALL

方法

設(shè)置統(tǒng)計級別為 ALL：

ALTER SESSION SET STATISTICS_LEVEL=ALL;

執(zhí)行 SQL 語句：

SELECT * FROM your_table WHERE your_column = 'some_value';

查看執(zhí)行計劃和統(tǒng)計信息：

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

優(yōu)點(diǎn)

• 詳細(xì)統(tǒng)計信息：可以提供詳細(xì)的執(zhí)行計劃和統(tǒng)計信息，包括 I/O 次數(shù)、CPU 時間等。
• 反映實際執(zhí)行：查看的是實際執(zhí)行的計劃，更能反映真實的性能情況。
• 操作簡單：只需設(shè)置統(tǒng)計級別并執(zhí)行 SQL 語句即可。

缺點(diǎn)

• 性能開銷：設(shè)置 STATISTICS_LEVEL 為 ALL 會增加執(zhí)行 SQL 語句的性能開銷。
• 臨時設(shè)置：僅對當(dāng)前會話有效，需要在每個會話中手動設(shè)置。

總結(jié)

適用場景

• EXPLAIN PLAN FOR + DBMS_XPLAN.DISPLAY：適用于簡單的查詢優(yōu)化，快速查看執(zhí)行計劃。
• DBMS_XPLAN.DISPLAY_CURSOR：適用于已經(jīng)執(zhí)行的 SQL 語句，需要查看實際執(zhí)行情況。
• AUTOTRACE：適用于 SQL*Plus 用戶，需要快速反饋。
• V$SQL_PLAN：適用于需要靈活查詢執(zhí)行計劃的場景。
• Oracle Enterprise Manager (OEM)：適用于需要圖形化界面和綜合性能信息的場景。
• DBMS_XPLAN.DISPLAY_AWR：適用于需要查看歷史執(zhí)行計劃的場景。
• 事件 10046 跟蹤：適用于需要深入分析性能問題，特別是涉及執(zhí)行時間和等待事件的場景。
• STATISTICS_LEVEL=ALL：適用于需要詳細(xì)統(tǒng)計信息和反映實際執(zhí)行情況的場景，操作簡單但有性能開銷。

以上就是Oracle執(zhí)行計劃查看方法匯總及優(yōu)劣對比的詳細(xì)內(nèi)容，更多關(guān)于Oracle執(zhí)行計劃查看方法的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論