Oracle 性能问题排查实战

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

1. 概述

性能问题排查方法论[1]：

步骤：

现象收集
数据采集
瓶颈定位
根因分析
优化实施
验证监控

2. 现象收集

2.1 关键信息

何时开始
持续时间
影响范围
业务表现
系统表现

2.2 问询

- 哪个业务慢？
- 何时开始？
- 数据量？
- 是否有变更？
- 服务器资源？

3. 数据采集

3.1 AWR 快照

-- 手动快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 查看快照
SELECT snap_id, begin_time, end_time 
FROM dba_hist_snapshot
ORDER BY snap_id DESC;

3.2 ASH

SELECT * FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24;

3.3 OS 监控

# CPU
top
vmstat 1

# 内存
free -m

# I/O
iostat -x 1

# 网络
sar -n DEV 1

4. 瓶颈定位

4.1 Top 5 等待

SELECT 
  event,
  total_waits,
  time_waited,
  average_wait,
  wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

4.2 等待类

SELECT 
  wait_class,
  SUM(time_waited) AS total_time
FROM v$system_event
WHERE wait_class != 'Idle'
GROUP BY wait_class
ORDER BY total_time DESC;

4.3 活跃会话

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

5. 根因分析

5.1 CPU 高

-- 1. Top SQL by CPU
SELECT sql_id, cpu_time / 1000000 AS cpu_sec
FROM v$sql
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

-- 2. 会话 CPU
SELECT sid, serial#, username, program, value / 100 AS cpu_sec
FROM v$sesstat s, v$statname n, v$session ss
WHERE s.statistic# = n.statistic#
  AND s.sid = ss.sid
  AND n.name = 'CPU used by this session'
ORDER BY value DESC;

5.2 I/O 高

-- 1. Top SQL by reads
SELECT sql_id, disk_reads
FROM v$sql
ORDER BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

-- 2. 数据文件 I/O
SELECT 
  df.file_name,
  fs.phyrds,
  fs.phywrts
FROM v$filestat fs, dba_data_files df
WHERE fs.file# = df.file_id
ORDER BY phyrds + phywrts DESC;

5.3 锁等待

-- 1. 阻塞会话
SELECT 
  blocking_session,
  sid,
  event,
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

-- 2. 锁详情
SELECT * FROM dba_waiters;

5.4 内存不足

-- 1. SGA
SELECT * FROM v$sgainfo;

-- 2. PGA
SELECT * FROM v$pgastat WHERE name LIKE '%allocated%';

-- 3. 命中率
-- Buffer Cache, Library Cache

6. 优化策略

6.1 SQL 优化

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

-- 2. 加索引
-- 3. 重写 SQL
-- 4. HINT
-- 5. SQL Profile

6.2 索引优化

-- 1. 索引使用
SELECT * FROM v$object_usage;

-- 2. 索引碎片
ANALYZE INDEX idx_name VALIDATE STRUCTURE;
SELECT * FROM index_stats;

6.3 统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP', cascade => TRUE);

6.4 参数调优

-- 内存
ALTER SYSTEM SET sga_target = 8G;
ALTER SYSTEM SET pga_aggregate_target = 4G;

-- 并行
ALTER SYSTEM SET parallel_max_servers = 32;

7. 常见问题处理

7.1 数据库慢

-- 1. AWR 报告
@?/rdbms/admin/awrrpt.sql

-- 2. Top SQL
-- 3. 等待事件
-- 4. 资源使用
-- 5. 优化

7.2 锁等待

-- 1. 找阻塞会话
SELECT blocking_session, sid FROM v$session WHERE blocking_session IS NOT NULL;

-- 2. 查看 SQL
SELECT sql_text FROM v$sql WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &blocking_sid);

-- 3. 杀会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

7.3 ORA-01555

-- 1. 增大 Undo
-- 2. 增大 undo_retention
-- 3. 优化长查询

7.4 内存不足

-- ORA-04031
-- 1. 增大 Shared Pool
-- 2. 优化 SQL
-- 3. FLUSH SHARED POOL（临时）

8. 排查案例

8.1 案例：业务突然慢

1. 询问：15:00 开始慢
2. AWR：15:00-16:00 报告
3. Top 等待：enq: TX - row lock contention
4. 阻塞会话：sid=123 长时间未提交
5. 杀掉会话
6. 业务恢复

8.2 案例：CPU 100%

1. top 找进程
2. ASH 找 SQL
3. 执行计划：全表扫描
4. 加索引
5. CPU 下降

8.3 案例：磁盘 I/O 满

1. iostat 找热点
2. 数据文件 I/O
3. 大表查询
4. 分区或缓存
5. I/O 下降

9. 监控告警

9.1 关键指标

AAS（平均活跃会话）
CPU 使用率
I/O 等待
锁等待
表空间使用

9.2 告警阈值

CPU > 80% 持续 5 分钟
I/O 等待 > 50 ms
锁等待 > 60 秒
表空间 > 85%

10. 常见坑与排错

10.1 误判

- 治标不治本
- 临时优化掩盖问题
- 未根因分析

10.2 过度优化

- 加太多索引
- 改太多参数
- HINT 滥用

10.3 测试不足

- 未验证效果
- 副作用未发现

11. 最佳实践

方法论：系统化
数据驱动：AWR/ASH
瓶颈定位：精准
根因分析：彻底
小步优化：可验证
监控验证：效果
文档化：积累
预防为主：监控
测试充分：避免副作用
持续改进：循环

12. 参考资料

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