Oracle SQL 调优最佳实践

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

1. 概述

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

原则：

减少 I/O
减少解析
减少排序
减少网络

2. 索引最佳实践

2.1 选择性高的列

-- 高选择性
CREATE INDEX idx_emp_id ON employees(id);  -- 主键
CREATE INDEX idx_emp_email ON employees(email);  -- 唯一

-- 复合索引
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);
-- 列顺序：高选择性在前

2.2 函数索引

-- 函数查询
CREATE INDEX idx_emp_upper ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

-- 表达式
CREATE INDEX idx_emp_sal ON employees(salary * 1.1);

2.3 监控使用

ALTER INDEX idx_name MONITORING USAGE;
-- 业务运行
SELECT * FROM v$object_usage;
ALTER INDEX idx_name NOMONITORING USAGE;

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

3. SQL 编写

3.1 避免 SELECT *

-- 慢
SELECT * FROM employees;

-- 快
SELECT id, name FROM employees;

3.2 WHERE 条件

-- 避免函数
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';  -- 索引失效
SELECT * FROM employees WHERE name = 'SMITH' OR name = 'smith';  -- 索引有效

-- 避免隐式转换
SELECT * FROM employees WHERE id = '100';  -- 字符串转数字
SELECT * FROM employees WHERE id = 100;  -- 推荐

3.3 JOIN 顺序

-- 小表驱动大表
SELECT * FROM small_table s, big_table b
WHERE s.id = b.id AND s.col = ...;

-- 或 HINT
SELECT /*+ LEADING(s b) USE_NL(b) */ *
FROM small_table s, big_table b
WHERE s.id = b.id;

3.4 EXISTS vs IN

-- 子查询结果集大：EXISTS
SELECT * FROM big_table b
WHERE EXISTS (SELECT 1 FROM small_table s WHERE s.id = b.id);

-- 子查询结果集小：IN
SELECT * FROM big_table b
WHERE b.id IN (SELECT id FROM small_table);

3.5 UNION ALL

-- 确定无重复：UNION ALL
SELECT id FROM a UNION ALL SELECT id FROM b;

-- 需要去重：UNION
SELECT id FROM a UNION SELECT id FROM b;

4. 绑定变量

4.1 使用

-- 减少 hard parse
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = :1' USING v_id;

-- PL/SQL 自动绑定
PROCEDURE get_emp(p_id IN NUMBER) AS
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = p_id;
END;

4.2 监控

SELECT name, value FROM v$sysstat 
WHERE name IN ('parse count (hard)', 'parse count (total)');

-- hard / total 应 < 5%

5. 分页

5.1 12c+ FETCH

SELECT * FROM employees
ORDER BY id
OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

5.2 旧版 ROWNUM

SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT * FROM employees ORDER BY id
  ) t WHERE ROWNUM <= 110
) WHERE rn > 100;

6. 批量操作

6.1 BULK COLLECT

DECLARE
  TYPE t_emp IS TABLE OF employees%ROWTYPE;
  v_emp t_emp;
BEGIN
  SELECT * BULK COLLECT INTO v_emp FROM employees LIMIT 1000;
  -- 处理
END;
/

6.2 FORALL

FORALL i IN 1..v_ids.COUNT
  INSERT INTO target VALUES v_ids(i);

FORALL i IN 1..v_ids.COUNT
  UPDATE target SET ... WHERE id = v_ids(i);

详细见：Oracle PL/SQL 性能优化。

7. 统计信息

-- 定期收集
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', 
  cascade => TRUE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO');

-- 自动任务
SELECT * FROM dba_autotask_client 
WHERE client_name = 'auto optimizer stats collection';

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

8. 执行计划

8.1 查看

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

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

8.2 关注

COST
Cardinality
估算 vs 实际
I/O

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

9. HINT 使用

9.1 谨慎使用

-- 仅在 CBO 选错时
SELECT /*+ INDEX(e idx_name) PARALLEL(e 4) */ *
FROM employees e WHERE ...;

9.2 常用

/*+ INDEX(table idx) */          -- 强制索引
/*+ FULL(table) */               -- 全表
/*+ PARALLEL(table n) */         -- 并行
/*+ USE_HASH(a b) */             -- Hash Join
/*+ LEADING(a b) */              -- 顺序
/*+ APPEND */                    -- 直接路径
/*+ FIRST_ROWS(n) */             -- 前 N 行

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

10. SQL Profile / Baseline

10.1 SQL Profile

-- STA 自动
DECLARE
  v_task VARCHAR2(100);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task);
END;
/

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => '&task', name => 'my_profile');

详细见：Oracle SQL Profile 详解。

10.2 SQL Plan Baseline

-- 固定计划
DECLARE
  v_count PLS_INTEGER;
BEGIN
  v_count := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');
END;
/

详细见：Oracle SQL Plan Baseline。

11. 分区表

11.1 分区裁剪

-- WHERE 包含分区键
SELECT * FROM sales 
WHERE sale_date BETWEEN '2026-01-01' AND '2026-12-31';
-- 仅扫描 2026 分区

11.2 本地索引

CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

详细见：Oracle 分区表性能优化。

12. 性能对比

12.1 优化前

SQL: SELECT * FROM big_table WHERE UPPER(name) = 'X'
执行时间: 30 秒
逻辑读: 100 万
物理读: 80 万

12.2 优化后

SQL: SELECT * FROM big_table WHERE name = 'X' OR name = 'x'
执行时间: 0.05 秒
逻辑读: 1000
物理读: 100

13. 监控

13.1 AWR Top SQL

SELECT sql_id, elapsed_time_total 
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 100 AND 110
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;

13.2 v$sql

SELECT sql_id, sql_text, elapsed_time, executions, buffer_gets
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

14. 常见坑与排错

14.1 优化无效

-- 1. SQL Profile/Baseline 影响
-- 2. 统计信息
-- 3. HINT 失效

14.2 计划不稳定

-- 1. SQL Plan Baseline
-- 2. 绑定变量窥视
-- 3. 统计信息

15. 最佳实践

索引优化：基础
SQL 重写：陷阱
绑定变量：解析
批量操作：性能
执行计划：根因
统计信息：基础
HINT 谨慎：仅必要
SQL Profile/Baseline：稳定
分区表：大数据
持续监控：优化

16. 参考资料

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