Oracle 高负载 SQL 优化实战
Oracle 高负载 SQL 优化实战
适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07
1. 概述
高负载 SQL 是性能问题主因[1]：
症状：
- CPU 高
- I/O 高
- 等待长
- 业务慢
2. 定位高负载 SQL
2.1 AWR Top SQL
SELECT
sql_id,
elapsed_time_total / 1000000 AS elapsed_sec,
executions,
buffer_gets_total,
disk_reads_total
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 100 AND 110
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;2.2 V$SQL
SELECT
sql_id,
sql_text,
elapsed_time / 1000000 AS elapsed_sec,
executions,
buffer_gets,
disk_reads
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;2.3 ASH
SELECT
sql_id,
COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;2.4 SQL Monitoring
SELECT sql_id, elapsed_time / 1000000 AS sec, status
FROM v$sql_monitor
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;3. 分析 SQL
3.1 执行计划
EXPLAIN PLAN FOR <SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-- 或
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));3.2 关注点
- 高 COST
- 全表扫描
- 不当 JOIN
- 高基数估算
- 缺失索引
3.3 统计信息
SELECT
object_name,
object_type,
num_rows,
last_analyzed
FROM user_tab_statistics
WHERE object_name IN (...);4. 优化策略
4.1 索引优化
-- 加索引
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);
-- 函数索引
CREATE INDEX idx_emp_upper ON employees(UPPER(name));4.2 SQL 重写
-- 避免 SELECT *
SELECT id, name FROM employees WHERE ...;
-- 使用 JOIN 替代子查询
SELECT e.* FROM employees e, departments d
WHERE e.dept_id = d.id AND d.location = 'NY';
-- EXISTS 替代 IN
SELECT * FROM e WHERE EXISTS (SELECT 1 FROM d WHERE d.id = e.dept_id);4.3 绑定变量
-- 减少 hard parse
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = :1' USING v_id;4.4 HINT
SELECT /*+ INDEX(e idx_name) PARALLEL(e 4) */ * FROM employees e WHERE ...;4.5 收集统计
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', cascade => TRUE);5. 实战案例
5.1 全表扫描
问题：
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- 全表扫描分析：函数阻止索引。
优化：
-- 函数索引
CREATE INDEX idx_emp_upper ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';5.2 子查询慢
问题：
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY');
-- 慢优化：
-- 改 JOIN
SELECT e.* FROM employees e, departments d
WHERE e.dept_id = d.id AND d.location = 'NY';5.3 大表 JOIN
问题：
SELECT * FROM sales s, products p WHERE s.product_id = p.id;
-- 慢，Hash Join 内存不够优化：
-- 1. 并行
SELECT /*+ PARALLEL(s 8) PARALLEL(p 4) */ * FROM ...;
-- 2. 增大 PGA
ALTER SYSTEM SET pga_aggregate_target = 16G;5.4 排序慢
问题：
SELECT * FROM employees ORDER BY salary DESC;
-- 磁盘排序优化：
-- 1. 索引
CREATE INDEX idx_emp_sal ON employees(salary DESC);
-- 2. 增大 PGA5.5 绑定变量窥视
问题：
-- 数据倾斜，不同值需要不同计划
SELECT * FROM orders WHERE status = :status;优化：
-- 1. 自适应游标
ALTER SYSTEM SET optimizer_adaptive_cursor_sharing = TRUE;
-- 2. SQL Plan Baseline6. SQL Profile
6.1 SQL Tuning Advisor
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;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task') FROM dual;6.2 接受
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => '&task',
name => 'my_profile'
);详细见：Oracle SQL 调优顾问。
7. SQL Plan Baseline
-- 固定计划
DECLARE
v_count PLS_INTEGER;
BEGIN
v_count := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');
END;
/8. 监控验证
8.1 性能对比
-- 优化前
SELECT elapsed_time FROM v$sql WHERE sql_id = '...';
-- 优化后
SELECT elapsed_time FROM v$sql WHERE sql_id = '...';8.2 持续监控
-- AWR 对比
SELECT snap_id, elapsed_time_total
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id'
ORDER BY snap_id;9. 常见坑与排错
9.1 优化无效
-- 1. 检查执行计划
-- 2. 检查统计信息
-- 3. 检查 HINT 语法
-- 4. SQL Plan Baseline 影响9.2 优化后变差
-- 1. 监控性能
-- 2. 回滚 HINT
-- 3. 检查副作用9.3 持续性能差
-- 1. AWR 历史对比
-- 2. 数据增长
-- 3. 索引碎片
-- 4. 统计信息过期10. 最佳实践
- AWR 定位 Top SQL：优先
- 执行计划分析：根因
- 加索引：常用
- 重写 SQL：避免陷阱
- 绑定变量：减少解析
- HINT 谨慎：仅必要
- SQL Profile：自动
- SQL Plan Baseline：稳定
- 监控验证：效果
- 持续优化：循环
11. 参考资料
[1] Oracle Database SQL Tuning Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/