Oracle SQL 性能调优案例

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

---

1. 概述

SQL 性能调优实战案例[1]：

内容：

• 全表扫描
• 索引选择
• JOIN 优化
• 绑定变量
• 子查询

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

---

2. 案例 1：全表扫描

2.1 问题

SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- 全表扫描

2.2 分析

• 函数阻止索引
• 统计信息旧

2.3 优化

-- 1. 函数索引
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));

-- 2. 重新统计
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

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

---

3. 案例 2：JOIN 优化

3.1 问题

SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;
-- Hash Join，慢

3.2 分析

• 大表 JOIN
• 无索引
• 小结果集

3.3 优化

-- 1. 索引
CREATE INDEX idx_emp_dept ON employees(dept_id);

-- 2. Nested Loop
SELECT /*+ USE_NL(e d) INDEX(e idx_emp_dept) */ 
  e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id AND e.id = 100;

-- 3. 验证
EXPLAIN PLAN FOR ...;

详细见：Oracle JOIN 连接方式。

---

4. 案例 3：子查询

4.1 问题

SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY');
-- 慢

4.2 优化

-- 1. EXISTS（大表）
SELECT * FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d 
  WHERE d.id = e.dept_id AND d.location = 'NY'
);

-- 2. JOIN
SELECT e.* FROM employees e, departments d
WHERE e.dept_id = d.id AND d.location = 'NY';

详细见：Oracle 子查询与 EXISTS。

---

5. 案例 4：绑定变量

5.1 问题

-- 字符串拼接
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || v_id;
-- 每次 hard parse

5.2 优化

-- 绑定变量
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

-- 减少解析
-- Shared Pool 高效

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

---

6. 案例 5：LIKE 查询

6.1 问题

SELECT * FROM employees WHERE name LIKE '%Smith%';
-- 全表扫描

6.2 优化

-- 1. 前缀匹配
SELECT * FROM employees WHERE name LIKE 'Smith%';
-- 索引有效

-- 2. 全文索引
CREATE INDEX idx_emp_text ON employees(name) INDEXTYPE IS CTXSYS.CONTEXT;
SELECT * FROM employees WHERE CONTAINS(name, 'Smith') > 0;

-- 3. 反向
SELECT * FROM employees WHERE name LIKE '%Smith';
-- REVERSE 索引
CREATE INDEX idx_emp_rev ON employees(REVERSE(name));
SELECT * FROM employees WHERE REVERSE(name) LIKE REVERSE('Smith%');

详细见：Oracle 全文检索。

---

7. 案例 6：OR 优化

7.1 问题

SELECT * FROM employees WHERE id = 1 OR salary > 10000;
-- 可能全表

7.2 优化

-- 1. UNION ALL
SELECT * FROM employees WHERE id = 1
UNION ALL
SELECT * FROM employees WHERE salary > 10000 AND id != 1;

-- 2. IN
SELECT * FROM employees WHERE id IN (1, 2, 3);

-- 3. 索引合并（自动）

---

8. 案例 7：分页优化

8.1 问题

-- 旧方式
SELECT * FROM (
  SELECT ROWNUM rn, t.* FROM (
    SELECT * FROM employees ORDER BY id
  ) t WHERE ROWNUM <= 100010
) WHERE rn > 100000;
-- 大偏移慢

8.2 优化

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

-- 键集分页
SELECT * FROM employees 
WHERE id > :last_id 
ORDER BY id 
FETCH FIRST 10 ROWS ONLY;

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

---

9. 案例 8：DISTINCT

9.1 问题

SELECT DISTINCT dept_id FROM employees;
-- 排序去重

9.2 优化

-- 1. GROUP BY（有时更优）
SELECT dept_id FROM employees GROUP BY dept_id;

-- 2. EXISTS
SELECT d.id FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

---

10. 案例 9：UNION

10.1 问题

SELECT id FROM a UNION SELECT id FROM b;
-- 排序去重

10.2 优化

-- UNION ALL（如无重复）
SELECT id FROM a UNION ALL SELECT id FROM b;

详细见：Oracle SQL 集合操作。

---

11. 案例 10：统计信息

11.1 问题

-- 执行计划不准
SELECT * FROM employees WHERE dept_id = 10;
-- 估算 100 行，实际 10000 行

11.2 优化

-- 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
);

-- 2. 直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  'SCOTT', 'EMPLOYEES',
  method_opt => 'FOR COLUMNS dept_id SIZE 254'
);

-- 3. 验证
EXPLAIN PLAN FOR ...;

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

---

12. 案例 11：SQL Profile

12.1 问题

• 统计正确
• 但执行计划仍差

12.2 优化

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

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

详细见：Oracle SQL 调优顾问。

---

13. 案例 12：SQL Plan Baseline

13.1 问题

• 计划不稳定
• 时好时坏

13.2 优化

-- 1. 捕获
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines = TRUE;

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

-- 3. 固定
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(...);

详细见：Oracle SQL Plan Baseline 基线。

---

14. 案例 13：并行

14.1 问题

-- 大表查询慢
SELECT COUNT(*) FROM big_table;

14.2 优化

-- 并行
SELECT /*+ PARALLEL(t 8) */ COUNT(*) FROM big_table t;

-- 表并行
ALTER TABLE big_table PARALLEL 8;

详细见：Oracle 并行查询。

---

15. 案例 14：物化视图

15.1 问题

-- 复杂聚合
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;
-- 每次计算

15.2 优化

-- 1. 物化视图
CREATE MATERIALIZED VIEW mv_dept_avg
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
  AS
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id;

-- 2. 自动重写
ALTER SESSION SET query_rewrite_enabled = TRUE;

详细见：Oracle 物化视图与查询重写。

---

16. 案例 15：分区

16.1 问题

-- 大表查询
SELECT * FROM sales WHERE sale_date = DATE '2026-07-21';
-- 全表

16.2 优化

-- 1. 分区表
CREATE TABLE sales (...) 
PARTITION BY RANGE (sale_date) (...);

-- 2. 分区裁剪
SELECT * FROM sales WHERE sale_date = DATE '2026-07-21';
-- 仅扫一个分区

详细见：Oracle 分区表设计。

---

17. 调优流程

17.1 识别

- AWR TOP SQL
- ASH 实时
- 监控告警

17.2 分析

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

17.3 优化

- 索引
- 统计
- Hint
- Profile
- Baseline

17.4 验证

- 性能对比
- 业务测试

---

18. 最佳实践

1. 统计信息：基础
2. 索引合理：覆盖
3. 绑定变量：减少解析
4. EXISTS/IN 选择：数据量
5. 分页 FETCH：12c+
6. 物化视图：聚合
7. 分区：大表
8. 并行：大查询
9. Profile/Baseline：稳定
10. 测试：验证

---

19. 参考资料

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