DB / Oracle / SQL 开发

Oracle SQL 调优进阶

2026.09.06

Oracle SQL 调优进阶

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

SQL 调优进阶技术[1]：

内容：

  • 执行计划深入
  • 优化器统计
  • SQL Profile
  • SQL Plan Baseline
  • Hint

详细见：Oracle SQL 调优最佳实践。

2. 执行计划分析

2.1 查看

-- EXPLAIN PLAN
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

-- 实际执行
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

-- AWR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

详细见：Oracle 执行计划详解。

2.2 关键字段

  • Cost
  • Cardinality
  • Bytes
  • 估算 vs 实际

2.3 Predicate Information

1 - filter("salary" > 5000)
2 - access("e"."dept_id" = "d"."id")

3. 统计信息

3.1 收集

-- 表
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCOTT',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE
);

-- Schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

-- 数据库
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

3.2 直方图

-- 数据倾斜列
method_opt => 'FOR COLUMNS size 254 col1 size 254 col2'

详细见：Oracle 直方图与统计信息。

3.3 锁定

-- 锁定统计
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');

-- 解锁
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');

4. Extended Stats

4.1 创建

-- 列组
DECLARE
  cg VARCHAR2(30);
BEGIN
  cg := DBMS_STATS.CREATE_EXTENDED_STATS(
    ownname => 'SCOTT',
    tabname => 'EMPLOYEES',
    extension => '(dept_id, job_id)'
  );
END;
/

-- 收集
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

4.2 表达式

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
  'SCOTT', 'EMPLOYEES', '(UPPER(name))'
) FROM dual;

5. SQL Profile

5.1 创建

-- SQL Tuning Advisor
EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');

-- 执行
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TASK_NAME');

-- 查看
SELECT * FROM TABLE(DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_NAME'));

-- 接受 Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => 'TASK_NAME',
  name => 'profile_1',
  force_match => TRUE
);

详细见：Oracle SQL 调优顾问。

5.2 管理

-- 查看
SELECT name, status, force_matching FROM dba_sql_profiles;

-- 禁用
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('profile_1', 'STATUS', 'DISABLED');

-- 启用
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('profile_1', 'STATUS', 'ENABLED');

-- 删除
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile_1');

6. SQL Plan Baseline

6.1 捕获

-- 启用
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

-- 自动捕获

6.2 加载

-- 从游标缓存
DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id',
    plan_hash_value => 12345
  );
END;
/

6.3 管理

-- 查看
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines;

-- 演进
SELECT * FROM TABLE(DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
  sql_handle => 'SQL_...'
));

-- 禁用
DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_...',
    plan_name => 'SQL_PLAN_...',
    attribute_name => 'ENABLED',
    attribute_value => 'NO'
  );
END;
/

详细见：Oracle SQL Plan Baseline 基线。

7. Hint

7.1 优化器

/*+ FIRST_ROWS(10) */     -- 优化前 N 行
/*+ ALL_ROWS */            -- 优化全部行
/*+ CHOOSE */              -- 选择
/*+ RULE */                -- RBO（不推荐）

7.2 访问路径

/*+ FULL(table) */            -- 全表
/*+ INDEX(table idx) */       -- 索引
/*+ INDEX_FFS(table idx) */   -- 快速全索引
/*+ ROWID(table) */           -- ROWID

7.3 JOIN

/*+ USE_NL(a b) */     -- Nested Loop
/*+ USE_HASH(a b) */   -- Hash Join
/*+ USE_MERGE(a b) */  -- Sort Merge
/*+ LEADING(a b) */    -- 顺序
/*+ SWAP_JOIN_INPUTS(b) */

7.4 并行

/*+ PARALLEL(table 4) */
/*+ PARALLEL(t1 4 t2 4) */
/*+ NOPARALLEL(table) */
/*+ PQ_DISTRIBUTE(b, NONE, BROADCAST) */

详细见：Oracle 优化器 Hint 详解。

8. SQL Monitor

8.1 实时

SELECT * FROM TABLE(DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => '&sql_id',
  type => 'TEXT'
));

8.2 HTML

SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => '&sql_id',
  type => 'ACTIVE'
) FROM dual;

详细见：Oracle SQL Monitor 详解。

9. 10046 事件

9.1 启用

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- 执行 SQL
ALTER SESSION SET EVENTS '10046 trace name context off';

9.2 分析

tkprof <trace> <output> explain=... sys=no

详细见：Oracle 10046 事件与 SQL Trace。

10. 性能问题诊断

10.1 找 TOP SQL

SELECT sql_id, elapsed_time, executions, elapsed_time/executions AS avg_elapsed
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

10.2 AWR

@?/rdbms/admin/awrrpt.sql
-- Top SQL by elapsed

详细见：Oracle AWR 性能报告深度。

10.3 ASH

@?/rdbms/admin/ashrpt.sql
-- 实时

详细见：Oracle ASH 详解。

11. 常见问题

11.1 全表扫描

-- 1. 索引存在？
-- 2. 统计信息？
-- 3. 谓词过滤？
-- 4. 函数阻止？
-- 5. 优化器模式？

11.2 NL vs Hash

- 小结果集：NL
- 大结果集：Hash
- 排序：Merge

11.3 计划不稳定

- SQL Plan Baseline
- 绑定变量窥视
- Adaptive Features

12. 最佳实践

  1. 统计信息准确：基础
  2. 直方图倾斜：优化
  3. Extended Stats：相关性
  4. SQL Profile：调优
  5. SQL Plan Baseline：稳定
  6. Hint 谨慎：必要
  7. SQL Monitor：实时
  8. 10046 深度：诊断
  9. 测试验证：效果
  10. 文档化：方案

13. 参考资料

[1] Oracle Database SQL Tuning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/