外观
Oracle 慢查询分析
慢查询是数据库性能问题的常见根源，会直接影响应用响应速度和用户体验。对于Oracle数据库，特别是19c和21c环境，建立完善的慢查询分析体系是DBA保障系统性能的核心工作之一。本文将从实际运维角度，详细介绍慢查询的定位、分析和优化方法，结合Oracle新版本特性，提供可落地的解决方案。
慢查询基础
慢查询的定义与影响
慢查询定义： 慢查询是指执行时间超过预定阈值的SQL语句。合理的阈值设定取决于业务场景：
- OLTP系统：通常设置为1秒
- OLAP/DW系统：可放宽至10-30秒
- 批处理任务：根据业务需求调整
慢查询的影响：
- 占用大量系统资源（CPU、内存、I/O）
- 导致其他查询响应缓慢
- 增加数据库负载，可能引发性能雪崩
- 影响用户体验，降低业务满意度
- 可能导致数据库崩溃或服务不可用
慢查询的常见原因
| 原因类别 | 具体表现 | 影响程度 |
|---|---|---|
| 索引问题 | 缺少合适索引、索引失效、过度索引 | 高 |
| SQL设计 | 不合理的JOIN、SELECT *、未使用绑定变量 | 高 |
| 执行计划 | 低效执行计划、执行计划不稳定 | 高 |
| 统计信息 | 统计信息过时、不准确或缺失 | 中 |
| 系统资源 | CPU/内存/存储I/O瓶颈 | 中 |
| 并发问题 | 锁等待、死锁、热块竞争 | 中 |
| 配置问题 | 不合适的参数设置 | 低 |
慢查询定位工具
1. AWR (Automatic Workload Repository)
AWR是Oracle的核心性能诊断工具，每小时自动收集一次性能数据，可用于分析历史慢查询。
关键视图与报告：
sql
-- 生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- 从AWR中查询慢查询
SELECT sql_id, plan_hash_value,
executions_total,
elapsed_time_total/1000000/executions_total avg_elapsed_seconds,
buffer_gets_total/executions_total avg_buffer_gets,
disk_reads_total/executions_total avg_disk_reads
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN &begin_snap AND &end_snap
ORDER BY elapsed_time_total DESC
FETCH FIRST 10 ROWS ONLY;2. ASH (Active Session History)
ASH提供近1小时的会话活动快照，适合分析实时慢查询和等待事件。
关键查询：
sql
-- 查找等待时间最长的SQL
SELECT sql_id, sql_text, wait_class, event,
COUNT(*) wait_count,
ROUND(COUNT(*) * 10 / 1000, 2) wait_seconds
FROM v$active_session_history
JOIN v$sql ON v$active_session_history.sql_id = v$sql.sql_id
WHERE session_state = 'WAITING'
AND wait_class != 'Idle'
AND sample_time > SYSDATE - 1/24
GROUP BY sql_id, sql_text, wait_class, event
ORDER BY wait_count DESC
FETCH FIRST 10 ROWS ONLY;3. SQL Monitor
SQL Monitor提供实时SQL执行监控，适合分析长时间运行的SQL语句。
使用方法：
sql
-- 生成SQL Monitor报告
SELECT dbms_sql_monitor.report_sql_monitor(sql_id => '&sql_id', type => 'TEXT') FROM dual;
-- 生成HTML格式报告
SELECT dbms_sql_monitor.report_sql_monitor(sql_id => '&sql_id', type => 'HTML') FROM dual;
-- 查看所有监控中的SQL
SELECT sql_id, status, username, sql_text
FROM v$sql_monitor
WHERE status = 'EXECUTING';4. SQL Trace与TKPROF
SQL Trace提供最详细的SQL执行信息，适合深入分析特定慢查询。
使用流程：
sql
-- 启用SQL Trace
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = 'slow_query_analysis';
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET max_dump_file_size = unlimited;
-- 执行慢查询
-- 禁用SQL Trace
ALTER SESSION SET sql_trace = false;
-- 查找Trace文件
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';使用TKPROF解析Trace文件：
bash
tkprof /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_12345_slow_query_analysis.trc \
/home/oracle/slow_query_analysis.out \
explain=sys/[password]@orcl \
sort=(prsela,exeela,fchela)5. 实时监控视图
V$SQL视图：
sql
-- 查找平均执行时间最长的SQL
SELECT sql_id, sql_text,
executions,
elapsed_time/1000000 total_elapsed_seconds,
elapsed_time/1000000/executions avg_elapsed_seconds,
buffer_gets, disk_reads
FROM v$sql
WHERE executions > 0
ORDER BY avg_elapsed_seconds DESC
FETCH FIRST 10 ROWS ONLY;执行计划深入分析
1. 执行计划获取方法
EXPLAIN PLAN：
sql
EXPLAIN PLAN FOR
SELECT /*+ gather_plan_statistics */ *
FROM employees WHERE department_id = 100;
-- 查看执行计划
SELECT * FROM TABLE(dbms_xplan.display());
-- 查看包含谓词和统计信息的执行计划
SELECT * FROM TABLE(dbms_xplan.display(format => 'ALL +OUTLINE +PEEKED_BINDS'));DBMS_XPLAN.DISPLAY_CURSOR：
sql
-- 查看实际执行计划
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id',
plan_hash_value => &plan_hash_value,
format => 'ALLSTATS LAST +ADAPTIVE +PEEKED_BINDS +OUTLINE'));2. 执行计划关键操作解读
| 操作类型 | 性能影响 | 优化建议 |
|---|---|---|
| TABLE ACCESS FULL | 高I/O消耗 | 考虑添加索引、分区或优化WHERE条件 |
| TABLE ACCESS BY INDEX ROWID | 中等 | 检查索引覆盖性，考虑复合索引 |
| INDEX RANGE SCAN | 低到中等 | 确保索引选择性高，避免大范围扫描 |
| INDEX UNIQUE SCAN | 低 | 理想情况，无需优化 |
| HASH JOIN | 高CPU消耗 | 适合大表连接，确保足够PGA内存 |
| NESTED LOOPS | 低到中等 | 适合小结果集连接，确保内层表有索引 |
| SORT MERGE JOIN | 高I/O和CPU | 适合有序数据，考虑添加排序索引 |
| SORT | 高CPU和内存 | 考虑索引避免排序，优化ORDER BY子句 |
| FILTER | 可变 | 检查过滤条件的选择性 |
3. 执行计划问题识别
常见执行计划问题：
- 全表扫描代替索引扫描
- 低效的连接顺序
- 不必要的排序操作
- 索引失效
- 执行计划不稳定
识别方法：
sql
-- 检查执行计划与预期是否一致
-- 查看谓词信息中的"Access Predicates"和"Filter Predicates"
-- 分析实际行数与估计行数的差异
-- 检查是否存在"Cardinality Feedback"或"Adaptive Plan"提示Oracle 19c与21c慢查询分析新特性
Oracle 19c增强特性
自动SQL计划管理增强：
- 自动捕获重复执行的SQL
- 自动演进执行计划基线
- 支持并行查询的执行计划管理
SQL Monitor增强：
- 支持实时查看SQL执行进度
- 提供更详细的操作统计信息
- 支持ADDM SQL分析
自适应查询优化增强：
- 自适应JOIN方法
- 自适应并行度
- 实时统计信息收集
Oracle 21c增强特性
SQL Performance Analyzer (SPA)增强：
- 支持机器学习驱动的SQL优化
- 增强的SQL比较报告
- 支持在线SQL性能评估
实时执行计划：
- 支持查看SQL执行过程中的实时执行计划
- 提供更详细的操作级统计信息
- 支持实时调整执行计划
机器学习优化：
- 机器学习驱动的执行计划选择
- 自适应统计信息收集
- 智能索引建议
增强的ASH分析：
- 支持更长的ASH历史保留
- 增强的ASH报告
- 支持ASH数据的机器学习分析
慢查询优化实战案例
案例1：缺少合适索引导致的慢查询
问题现象：
SELECT * FROM orders WHERE customer_id = 12345 AND order_date > '2023-01-01';
-- 执行时间：5.2秒分析过程：
- 查看执行计划：发现使用了TABLE ACCESS FULL
- 检查表结构：customer_id和order_date列单独有索引，但没有复合索引
- 分析数据分布：customer_id有高选择性，order_date有中选择性
优化方案：
sql
-- 创建复合索引
CREATE INDEX orders_cust_date_idx ON orders(customer_id, order_date);
-- 优化后执行时间：0.02秒案例2：执行计划不稳定导致的慢查询
问题现象： 同一SQL在不同时间执行，执行时间从0.1秒到10秒不等
分析过程：
- 查看AWR历史：发现有多个不同的plan_hash_value
- 分析执行计划：一个使用高效索引，另一个使用低效全表扫描
- 检查统计信息：表统计信息最近更新，但数据分布不均匀
优化方案：
sql
-- 使用SQL计划基线锁定高效执行计划
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id', plan_hash_value => &good_plan_hash_value);
-- 或使用SQL Profile
EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_id => '&sql_id', plan_hash_value => &good_plan_hash_value);案例3：未使用绑定变量导致的慢查询
问题现象： 应用程序执行大量类似SQL，但每次参数不同，导致硬解析过多
分析过程：
- 查看V$SQL：发现大量相似SQL，仅参数不同
- 检查解析统计：硬解析率高达90%
- 查看等待事件：大量"library cache pin"和"library cache lock"等待
优化方案：
sql
-- 修改应用使用绑定变量
-- 示例：从
SELECT * FROM employees WHERE employee_id = 123;
-- 改为
SELECT * FROM employees WHERE employee_id = :emp_id;
-- 或使用游标共享提示
SELECT /*+ cursor_sharing_exact */ * FROM employees WHERE employee_id = 123;案例4：统计信息过时导致的慢查询
问题现象：
SELECT * FROM large_table WHERE status = 'ACTIVE';
-- 执行时间：8.5秒分析过程：
- 查看执行计划：估计行数为1000，实际行数为100万
- 检查统计信息：最后收集时间为3个月前
- 查看表增长：表数据量增长了10倍
优化方案：
sql
-- 收集表和索引统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCHEMA_NAME',
tabname => 'LARGE_TABLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => 4,
cascade => TRUE
);
-- 优化后执行时间：0.8秒慢查询优化最佳实践
1. 建立慢查询监控体系
- 实时监控：使用SQL Monitor和ASH监控正在运行的慢查询
- 定期分析：每周生成AWR报告，分析Top SQL
- 告警机制：配置慢查询告警，及时通知DBA
- 历史趋势：建立慢查询历史数据库，分析性能趋势
2. 优化方法优先级
- 添加/优化索引：最高优先级，效果最显著
- 优化SQL设计：修正不合理的SQL写法
- 更新统计信息：确保执行计划准确
- 调整执行计划：使用SQL计划基线或SQL Profile
- 系统资源优化：解决CPU/内存/I/O瓶颈
- 配置参数调整：调整相关数据库参数
3. 索引优化最佳实践
- 遵循索引设计原则：选择高选择性列、考虑查询模式
- 避免过度索引：索引数量不宜超过表列数的20%
- 定期维护索引：重建碎片化索引，收集统计信息
- 使用复合索引：将常用查询条件组合成复合索引
- 考虑索引覆盖：包含查询所需的所有列，避免回表
4. SQL设计最佳实践
- **避免SELECT ***：只选择需要的列
- 使用绑定变量：减少硬解析
- 优化WHERE子句：将高选择性条件放在前面
- 避免在WHERE子句中使用函数：会导致索引失效
- 合理使用JOIN：限制JOIN表数量，优化JOIN顺序
- 避免使用NOT IN和OR：考虑使用NOT EXISTS或UNION
5. 执行计划管理最佳实践
- 建立执行计划基线：锁定高效执行计划
- 定期审查执行计划：确保执行计划稳定
- 使用SQL Profile：微调执行计划
- 监控执行计划变化：使用AWR跟踪plan_hash_value变化
- 利用自适应查询优化：充分利用Oracle的自适应特性
常见问题（FAQ）
Q1: 如何快速定位生产环境中的慢查询？
A1: 推荐使用以下方法：
- 首先查看SQL Monitor，了解当前正在运行的慢查询
- 生成最近1小时的ASH报告，分析等待事件和慢查询
- 查看V$SQL视图，按平均执行时间排序
- 对于历史慢查询，生成AWR报告查看Top SQL
Q2: 如何判断是否需要添加索引？
A2: 考虑以下因素：
- 查询的WHERE子句和JOIN条件
- 列的选择性（不同值的数量/总行数）
- 查询的执行频率
- 表的DML操作频率（索引会影响DML性能）
- 实际测试添加索引前后的性能差异
Q3: 执行计划不稳定怎么办？
A3: 解决方法包括：
- 使用SQL计划基线锁定高效执行计划
- 收集准确的统计信息
- 使用绑定变量
- 考虑使用SQL Profile
- 调整optimizer_dynamic_sampling参数
- 对于复杂查询，使用OUTLINE提示
Q4: 如何优化高并发下的慢查询？
A4: 建议：
- 优化SQL和执行计划，减少资源消耗
- 考虑使用缓存机制，减少数据库访问
- 优化锁机制，减少锁等待
- 考虑读写分离，减轻主库压力
- 垂直或水平拆分表，减少单表数据量
Q5: 如何处理全表扫描的慢查询？
A5: 处理方法：
- 检查是否可以添加索引
- 考虑分区表，减少扫描范围
- 优化WHERE条件，提高过滤效果
- 考虑使用并行查询（仅适合大数据量查询）
- 检查统计信息是否准确
Q6: 如何监控慢查询的优化效果？
A6: 监控指标包括：
- SQL执行时间（总时间和平均时间）
- 资源消耗（CPU、内存、I/O）
- 执行计划变化
- 应用响应时间变化
- 用户反馈
Q7: 如何优化复杂的JOIN查询？
A7: 优化建议：
- 确保JOIN列有合适的索引
- 优化JOIN顺序，将小结果集的表放在前面
- 考虑使用临时表或物化视图
- 限制JOIN的表数量（建议不超过5个）
- 考虑使用HASH JOIN代替SORT MERGE JOIN
Q8: 如何利用机器学习优化慢查询？
A8: Oracle 21c支持：
- 机器学习驱动的执行计划选择
- 自适应统计信息收集
- 智能索引建议
- 自动SQL调优
使用方法：
sql
-- 启用机器学习优化
ALTER SYSTEM SET optimizer_learning = TRUE;
-- 使用SQL Performance Analyzer进行机器学习分析
EXEC DBMS_SQLPA.ANALYZE_SQL_PERFORMANCE(sql_id => '&sql_id', task_name => 'ML_SQL_ANALYSIS');总结
慢查询分析是Oracle数据库性能优化的核心工作之一，需要DBA掌握多种工具和方法，结合实际生产环境进行综合分析。Oracle 19c和21c提供了丰富的性能监控和优化工具，DBA应充分利用这些特性，建立完善的慢查询监控和优化体系。
在实际运维中，DBA应：
- 定期监控和分析慢查询
- 优先优化影响最大的SQL
- 结合多种工具进行深入分析
- 采用科学的优化方法和优先级
- 持续跟踪优化效果
- 关注Oracle新版本的性能特性
通过持续的监控、分析和优化，可以确保Oracle数据库系统的高效稳定运行，为业务提供可靠的支持。