DB / Oracle / 性能诊断

Oracle 数据库慢查询定位

2026.09.06

Oracle 数据库慢查询定位

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

1. 概述

慢查询定位方法[1]：

工具：

  • AWR
  • ASH
  • SQL Monitoring
  • v$ 视图

2. 实时定位

2.1 活跃会话

SELECT 
  sid, 
  serial#, 
  username, 
  program, 
  event,
  sql_id, 
  seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE'
  AND username IS NOT NULL
ORDER BY seconds_in_wait DESC;

2.2 长操作

SELECT 
  sid, 
  serial#, 
  opname, 
  sofar, 
  totalwork, 
  ROUND(sofar / totalwork * 100, 2) AS pct,
  time_remaining
FROM v$session_longops
WHERE time_remaining > 0
ORDER BY time_remaining DESC;

2.3 SQL Monitoring

SELECT 
  sql_id, 
  status, 
  elapsed_time / 1000000 AS sec
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY elapsed_time DESC;

3. 历史定位

3.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;

3.2 ASH 历史慢 SQL

SELECT 
  sql_id,
  COUNT(*) AS samples,
  ROUND(COUNT(*) * 10 / 60, 2) AS active_sec
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 100 AND 110
  AND sql_id IS NOT NULL
GROUP BY sql_id
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

3.3 执行历史

SELECT 
  snap_id,
  elapsed_time_total / 1000000 AS sec,
  executions
FROM dba_hist_sqlstat
WHERE sql_id = '&sql_id'
ORDER BY snap_id;

4. 慢 SQL 分析

4.1 执行计划

-- 当前
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

-- 历史
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

4.2 等待事件

SELECT 
  event, 
  wait_class,
  COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_id = '&sql_id'
  AND sample_time > SYSDATE - 1/24
GROUP BY event, wait_class
ORDER BY samples DESC;

4.3 SQL 文本

SELECT sql_text FROM v$sql WHERE sql_id = '&sql_id';
-- 或
SELECT sql_text FROM dba_hist_sqltext WHERE sql_id = '&sql_id';

5. 常见慢 SQL 类型

5.1 全表扫描

-- 大表无索引或索引失效
EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- TABLE ACCESS FULL

5.2 高逻辑读

SELECT sql_id, buffer_gets, executions 
FROM v$sql 
WHERE buffer_gets > 1000000
ORDER BY buffer_gets DESC;

5.3 高物理读

SELECT sql_id, disk_reads, executions
FROM v$sql
WHERE disk_reads > 100000
ORDER BY disk_reads DESC;

5.4 高解析

SELECT sql_id, parse_calls, executions
FROM v$sql
WHERE parse_calls > executions
ORDER BY parse_calls DESC;

5.5 长执行

SELECT sql_id, elapsed_time / 1000000 AS sec
FROM v$sql
WHERE elapsed_time > 60 * 1000000
ORDER BY elapsed_time DESC;

6. 排查步骤

6.1 收集信息

1. 慢的业务
2. 时间段
3. SQL 文本
4. 表大小
5. 用户行为

6.2 定位 SQL

-- 1. ASH 找时段慢 SQL
SELECT sql_id, COUNT(*) FROM v$active_session_history
WHERE sample_time BETWEEN :t1 AND :t2
GROUP BY sql_id ORDER BY COUNT(*) DESC;

-- 2. 找 SQL 文本
SELECT sql_text FROM v$sql WHERE sql_id = '&sql_id';

-- 3. AWR 报告

6.3 分析计划

-- 1. 执行计划
EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. 等待事件
SELECT event, COUNT(*) FROM v$active_session_history 
WHERE sql_id = '...' GROUP BY event;

-- 3. 统计信息
SELECT last_analyzed, num_rows FROM user_tables WHERE ...;

6.4 优化

-- 1. 索引
-- 2. 重写
-- 3. HINT
-- 4. SQL Profile
-- 5. SQL Plan Baseline

7. 工具

7.1 AWR

@?/rdbms/admin/awrrpt.sql

详细见：Oracle AWR 报告深度分析。

7.2 ASH

@?/rdbms/admin/ashrpt.sql

详细见：Oracle ASH 报告深度分析。

7.3 SQL Monitor

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

详细见：Oracle SQL Monitoring 实时监控。

7.4 10046 事件

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- 执行
ALTER SESSION SET EVENTS '10046 trace name context off';

详细见：Oracle 10046 事件与 SQL Trace。

8. 实战案例

8.1 案例：业务卡

1. 用户报告业务卡
2. ASH 15:00-15:30 报告
3. Top SQL: SELECT * FROM orders WHERE customer_id = 123
4. 执行计划：TABLE ACCESS FULL
5. 检查：无索引
6. 加索引
7. 性能：3 秒 → 0.05 秒

8.2 案例：定时慢

1. 每日 02:00 慢
2. AWR 02:00-03:00
3. Top SQL: UPDATE ... WHERE date_col < ...
4. 全表更新
5. 优化：分批更新 + 索引
6. 性能提升

9. 常见坑与排错

9.1 找不到慢 SQL

-- 1. 检查时段
-- 2. ASH 采样
-- 3. 应用 SQL 文本

9.2 计划不一致

-- 1. 立即 vs 历史
-- 2. 绑定变量值
-- 3. 统计信息

9.3 优化无效

-- 1. SQL Profile/Baseline 干扰
-- 2. 绑定变量窥视
-- 3. 重新分析

10. 最佳实践

  1. AWR 定期：日常监控
  2. ASH 短期：实时
  3. 执行计划：根因
  4. 等待事件：瓶颈
  5. 历史对比：趋势
  6. 统计信息：基础
  7. 索引优化：常用
  8. SQL 重写：陷阱
  9. SQL Profile/Baseline：稳定
  10. 持续监控：循环

11. 参考资料

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