Oracle SQL 性能分析案例集

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

---

1. 概述

本文档汇集 Oracle SQL 性能优化实战案例[1]：

---

2. 案例 1：全表扫描优化

2.1 现象

SELECT * FROM employees WHERE dept_id = 10;
-- 执行计划：TABLE ACCESS FULL
-- 5 秒

2.2 分析

EXPLAIN PLAN FOR SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 全表扫描

2.3 优化

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

-- 2. 收集统计
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', cascade => TRUE);

-- 3. 验证
-- 0.01 秒

---

3. 案例 2：函数阻止索引

3.1 现象

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

3.2 优化

-- 函数索引
CREATE INDEX idx_emp_upper ON employees(UPPER(name));

-- 或改写
SELECT * FROM employees WHERE name = 'SMITH' OR name = 'smith';

---

4. 案例 3：IN 子查询慢

4.1 现象

SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'NY');
-- 30 秒

4.2 优化

-- 改 JOIN
SELECT o.* FROM orders o, customers c
WHERE o.customer_id = c.id AND c.region = 'NY';

-- 或 EXISTS
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c 
  WHERE c.id = o.customer_id AND c.region = 'NY');

---

5. 案例 4：大表 JOIN

5.1 现象

SELECT * FROM sales s, products p WHERE s.product_id = p.id;
-- 慢，Hash Join 内存不够

5.2 优化

-- 1. 增大 PGA
ALTER SYSTEM SET pga_aggregate_target = 16G;

-- 2. 并行
SELECT /*+ PARALLEL(s 8) PARALLEL(p 4) USE_HASH(s p) */ *
FROM sales s, products p WHERE s.product_id = p.id;

---

6. 案例 5：排序慢

6.1 现象

SELECT * FROM employees ORDER BY salary DESC;
-- 磁盘排序

6.2 优化

-- 1. 索引排序
CREATE INDEX idx_emp_sal_desc ON employees(salary DESC);

-- 2. 增大 PGA
ALTER SYSTEM SET pga_aggregate_target = 8G;

-- 3. LIMIT 减少
SELECT * FROM (
  SELECT * FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 100;

---

7. 案例 6：COUNT(*) 慢

7.1 现象

SELECT COUNT(*) FROM big_table WHERE status = 'ACTIVE';
-- 1 分钟

7.2 优化

-- 1. 索引
CREATE INDEX idx_big_status ON big_table(status);

-- 2. 物化视图
CREATE MATERIALIZED VIEW mv_count_status
  REFRESH COMPLETE ON COMMIT
  ENABLE QUERY REWRITE
AS
SELECT status, COUNT(*) AS cnt FROM big_table GROUP BY status;

---

8. 案例 7：分页慢

8.1 现象

SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM big_table t WHERE ROWNUM <= 10000
) WHERE rn > 9990;
-- 慢

8.2 优化

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

-- 索引
CREATE INDEX idx_big_id ON big_table(id);

---

9. 案例 8：绑定变量窥视

9.1 现象

SELECT * FROM orders WHERE status = :status;
-- 不同 status 选择性不同，但同一计划

9.2 优化

-- 1. 自适应游标
ALTER SYSTEM SET optimizer_adaptive_cursor_sharing = TRUE;

-- 2. SQL Plan Baseline

详细见：Oracle Adaptive Features。

---

10. 案例 9：UPDATE 慢

10.1 现象

UPDATE big_table SET status = 'PROCESSED' WHERE date_col < SYSDATE - 30;
-- 1 小时

10.2 优化

-- 1. 索引
CREATE INDEX idx_big_date ON big_table(date_col);

-- 2. 并行
ALTER SESSION ENABLE PARALLEL DML;
UPDATE /*+ PARALLEL(t 8) */ big_table t
SET status = 'PROCESSED'
WHERE date_col < SYSDATE - 30;
COMMIT;

-- 3. 分批
BEGIN
  FOR i IN 1..10 LOOP
    UPDATE big_table SET status = 'PROCESSED'
    WHERE date_col < SYSDATE - 30
      AND MOD(id, 10) = i;
    COMMIT;
  END LOOP;
END;
/

---

11. 案例 10：DELETE 慢

11.1 现象

DELETE FROM big_table WHERE date_col < SYSDATE - 365;
-- 2 小时

11.2 优化

-- 1. 分区 TRUNCATE
ALTER TABLE big_table TRUNCATE PARTITION p_old UPDATE GLOBAL INDEXES;

-- 2. 分批 DELETE
BEGIN
  LOOP
    DELETE FROM big_table 
    WHERE date_col < SYSDATE - 365 
      AND ROWNUM <= 10000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/

---

12. 案例 11：JOIN 多表

12.1 现象

SELECT * FROM a, b, c, d, e
WHERE a.id = b.a_id AND b.id = c.b_id AND ...;
-- 慢

12.2 优化

-- 1. LEADING + JOIN 方法
SELECT /*+ LEADING(a b c d e) 
           USE_NL(b c) USE_HASH(d e) */ *
FROM a, b, c, d, e
WHERE ...;

-- 2. 物化视图预聚合

---

13. 案例 12：聚合查询慢

13.1 现象

SELECT dept_id, COUNT(*), SUM(salary), AVG(salary)
FROM employees
GROUP BY dept_id;
-- 大表慢

13.2 优化

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

-- 2. 并行
SELECT /*+ PARALLEL(8) */ ...;

-- 3. In-Memory
ALTER TABLE employees INMEMORY;

---

14. 案例 13：DISTINCT 慢

14.1 现象

SELECT DISTINCT dept_id, region FROM big_table;
-- 排序慢

14.2 优化

-- 1. GROUP BY
SELECT dept_id, region FROM big_table GROUP BY dept_id, region;

-- 2. HASH GROUP BY（10g+）
SELECT /*+ USE_HASH_AGGREGATION */ DISTINCT ...;

---

15. 案例 14：UNION 慢

15.1 现象

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

15.2 优化

-- 1. UNION ALL（如确定无重复）
SELECT id FROM a
UNION ALL
SELECT id FROM b;

-- 2. 索引

---

16. 最佳实践

1. 执行计划优先：根因
2. 索引优化：常用
3. 重写 SQL：避免陷阱
4. 绑定变量：减少解析
5. 并行：大数据
6. 物化视图：聚合
7. In-Memory：分析
8. PGA 调优：排序
9. SQL Plan Baseline：稳定
10. 持续监控：优化

---

17. 参考资料

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