Oracle 生成执行计划的三种方法与实战选型指南

方法对比速览

1. SET AUTOTRACE TRACEONLY (最简便)

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM EMP WHERE DEPTNO = 10;
-- 仅显示计划，不执行，不等待

SET AUTOTRACE TRACEONLY STATISTICS
SELECT * FROM EMP WHERE DEPTNO = 10;
-- 执行并显示统计 (recursive calls, db block gets, consistent gets, physical reads 等)

前置准备：

1. 当前用户执行 @?/rdbms/admin/utlxplan.sql 建立 PLAN_TABLE (或使用公共同义词)。
2. 拥有 PLUSTRACE 角色 (由 @?/sqlplus/admin/plustrce.sql 授予)。

2. EXPLAIN PLAN 命令 (标准化)

-- 1. 确保 PLAN_TABLE 存在
@?/rdbms/admin/utlxplan.sql

-- 2. 生成计划
EXPLAIN PLAN SET STATEMENT_ID = 'MY_QUERY_01' FOR
SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

-- 3. 格式化输出 (Oracle 10g+ 推荐)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'MY_QUERY_01', 'TYPICAL'));

优势：计划持久化在 PLAN_TABLE，便于版本对比、工具解析。

3. 会话级 Trace + TKPROF (最深度)

-- 目标会话 SID=123, SERIAL#=456
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, TRUE);
-- 等待业务跑完
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, FALSE);

-- 定位 trace 文件 (USER_DUMP_DEST)
-- 格式化
TKPROF ORCL_ora_12345.trc OUTPUT.TXT EXPLAIN=USER/PASS SYS=NO SORT=PRSLA,EXELA,FCHLA

输出含：Parse/Execute/Fetch 各阶段 CPU、Elapsed、Disk、Query、Current、Rows 等完整资源画像，唯一能看到实际执行计划 (非估算) 的方法。

实战选型建议

• 开发自测：SET AUTOTRACE TRACEONLY EXPLAIN 秒级反馈。
• 代码评审/版本对比：EXPLAIN PLAN + DBMS_XPLAN.DISPLAY 归档计划。
• 生产疑难慢 SQL：开启会话 Trace → TKPROF 分析，不影响其他会话。
• 全库 Top SQL 巡检：DBA_HIST_SQLSTAT + DBMS_XPLAN.DISPLAY_AWR (需 Diagnostic Pack)。