DB / Oracle / SQL 开发

Oracle SQL 调优最佳实践

2026.09.06

Oracle SQL 调优最佳实践

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

1. 概述

SQL 调优最佳实践汇总[1]：

详细见：Oracle SQL 性能调优案例、Oracle SQL 查询优化技巧。

2. 调优流程

2.1 识别

- AWR TOP SQL
- ASH
- 监控告警
- 用户反馈

2.2 分析

- 执行计划
- 统计信息
- 等待事件
- SQL 文本

2.3 优化

- 索引
- SQL 重写
- 统计收集
- Hint
- Profile
- Baseline

2.4 验证

- 性能对比
- 业务测试
- 监控

3. 索引优化

3.1 创建

-- 高选择性
CREATE INDEX idx_emp_email ON employees(email);

-- 复合
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);

-- 函数
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));

详细见：Oracle 索引优化策略详解。

3.2 监控

ALTER INDEX idx_emp_name MONITORING USAGE;
-- 查看 v$object_usage

3.3 重建

ALTER INDEX idx_emp_name REBUILD ONLINE;

4. 统计信息

4.1 收集

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

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

4.2 直方图

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES',
  method_opt => 'FOR COLUMNS dept_id SIZE 254');

4.3 锁定

EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT', 'EMPLOYEES');

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

5. SQL 重写

5.1 避免函数

-- 差
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- 好
SELECT * FROM employees WHERE name = 'Smith';
-- 或函数索引

5.2 绑定变量

-- 差（拼接）
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || v_id;

-- 好（绑定）
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

详细见：Oracle PL/SQL 动态 SQL 详解。

5.3 分页

-- 12c+
SELECT * FROM employees ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

详细见：Oracle 12c 新 SQL 特性。

6. JOIN 优化

6.1 选择

- Nested Loop：小表驱动大表
- Hash Join：大表
- Sort Merge：有序

6.2 Hint

SELECT /*+ USE_NL(e d) */ ...
SELECT /*+ USE_HASH(e d) */ ...
SELECT /*+ USE_MERGE(e d) */ ...

详细见：Oracle JOIN 连接方式。

7. 子查询

7.1 IN vs EXISTS

-- IN（子查询小）
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE ...);

-- EXISTS（外查询小）
SELECT * FROM employees e WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.id = e.dept_id AND ...
);

详细见：Oracle 子查询与 EXISTS。

8. Hint

8.1 常用

-- 优化器
/*+ RULE */
/*+ FIRST_ROWS(100) */
/*+ ALL_ROWS */

-- 索引
/*+ INDEX(t idx_name) */
/*+ INDEX_FFS(t idx_name) */
/*+ FULL(t) */

-- JOIN
/*+ USE_NL(a b) */
/*+ USE_HASH(a b) */
/*+ LEADING(a b) */

-- 并行
/*+ PARALLEL(t 8) */

-- 其他
/*+ APPEND */
/*+ DRIVING_SITE(t) */

8.2 注意

- 谨慎使用
- 测试
- 注释更新
- 优化器变化

详细见：Oracle SQL Hint 详解。

9. SQL Profile

9.1 SQL Tuning Advisor

DECLARE
  v_task VARCHAR2(30);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task);
END;
/

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_NAME') FROM dual;

9.2 接受

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

-- 查看
SELECT name, status FROM dba_sql_profiles;

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

详细见：Oracle SQL 调优顾问。

10. SQL Plan Baseline

10.1 捕获

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

10.2 加载

DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&sql_id',
    plan_hash_value => 12345
  );
END;
/

10.3 管理

SELECT sql_handle, plan_name, enabled, accepted, fixed 
FROM dba_sql_plan_baselines;

-- 固定
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  sql_handle => '...',
  plan_name => '...',
  attribute_name => 'FIXED',
  attribute_value => 'YES'
);

详细见：Oracle SQL Plan Baseline 基线。

11. 执行计划

11.1 查看

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

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

-- 游标
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

11.2 关注

- TABLE ACCESS FULL：全表
- INDEX RANGE SCAN：索引
- HASH JOIN：大表
- NESTED LOOPS：小表
- SORT：排序
- BUFFER SORT：内存

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

12. 等待事件

12.1 主要

- db file sequential read：索引读
- db file scattered read：全表读
- log file sync：提交
- enq: TX - row lock：锁
- buffer busy waits：缓冲忙
- library cache lock：解析

12.2 查询

SELECT event, total_waits, time_waited 
FROM v$system_event 
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC FETCH FIRST 10 ROWS ONLY;

详细见：Oracle 等待事件详解。

13. AWR

13.1 报告

@?/rdbms/admin/awrrpt.sql

13.2 TOP SQL

- Elapsed Time
- CPU Time
- Buffer Gets
- Disk Reads
- Executions
- Parse Calls

详细见：Oracle AWR 详解。

14. ASH

14.1 实时

SELECT sample_time, session_id, sql_id, event 
FROM v$active_session_history 
WHERE sample_time > SYSDATE - 1/24;

14.2 报告

@?/rdbms/admin/ashrpt.sql

详细见：Oracle ASH 详解。

15. 10046 事件

15.1 启用

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- 业务
ALTER SESSION SET EVENTS '10046 trace name context off';

15.2 分析

tkprof trace.trc output.txt explain=user/pwd sys=no

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

16. 监控

16.1 SQL Monitor

SELECT * FROM v$sql_monitor WHERE sql_id = '&sql_id';

-- 报告
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

详细见：Oracle SQL Monitor。

16.2 长操作

SELECT sid, serial#, opname, sofar, totalwork 
FROM v$session_longops 
WHERE sofar < totalwork;

17. 常见优化清单

问题优化
全表扫描索引
函数阻止索引函数索引
统计旧GATHER_STATS
Hard Parse 多绑定变量
OR 性能UNION ALL
JOIN 慢Hint / 索引
排序大索引排序
分页慢FETCH
DISTINCT 慢GROUP BY
UNION 慢UNION ALL

18. 最佳实践

  1. 统计信息：基础
  2. 索引合理：覆盖
  3. 绑定变量：减少解析
  4. SQL 重写：避免函数
  5. 执行计划：验证
  6. AWR/ASH：监控
  7. SQL Profile：辅助
  8. Baseline：稳定
  9. Hint 谨慎：兜底
  10. 测试：验证

19. 参考资料

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