【Oracle 19c 及云 RDS 环境的 I/O 问题排查与归档】

Oracle 19c 数据库 I/O 压力排查与诊断实录

本文整理自一次完整的 Oracle 19c（RDS）I/O 性能问题排查过程，涵盖：诊断 SQL、指标解读、根因分析（含云盘 burst credit 限速）、以及常见疑问解答。

一、Oracle 19c I/O 压力排查 SQL 汇总

以下 SQL 可在客户端连接数据库后直接执行，用于排查数据库 I/O 压力。多数视图需要 SELECT_CATALOG_ROLE 或更高权限；AWR 相关需诊断包授权。

1. 实时 I/O 指标（全库 IOPS / 吞吐 / 延迟）

SELECT metric_name,
       ROUND(value, 3) AS value,
       metric_unit
  FROM v$sysmetric
 WHERE group_id = 2
   AND metric_name IN (
     'Physical Read Total IO Requests Per Sec',
     'Physical Write Total IO Requests Per Sec',
     'Physical Read Total Bytes Per Sec',
     'Physical Write Total Bytes Per Sec',
     'Average Synchronous Single-Block Read Latency',
     'Average Synchronous Multi-Block Read Latency',
     'Average Synchronous Write Latency',
     'Host CPU Utilization (%)'
   )
 ORDER BY metric_name;

2. 实时 I/O 等待事件（近 60 秒）

SELECT event,
       ROUND(time_waited/100, 3) AS time_waited_s,
       ROUND(average_wait, 3)    AS avg_wait_ms,
       waits,
       wait_class
  FROM v$eventmetric
 WHERE wait_class IN ('User I/O','System I/O','Commit','Concurrency')
 ORDER BY time_waited DESC FETCH FIRST 20 ROWS ONLY;

3. 当前正在等待 I/O 的会话

SELECT s.sid, s.serial#, s.username, s.machine, s.program,
       s.sql_id, s.event, s.wait_class, s.state,
       s.seconds_in_wait,
       s.p1text, s.p1, s.p2text, s.p2, s.p3text, s.p3
  FROM v$session s
 WHERE s.type = 'USER'
   AND s.state = 'WAITING'
   AND s.wait_class IN ('User I/O','System I/O','Commit')
 ORDER BY s.seconds_in_wait DESC;

根据上面结果的 sql_id 查看 SQL 文本：

SELECT sql_id, child_number, executions,
       ROUND(elapsed_time/1e6,3) AS elapsed_s,
       buffer_gets, disk_reads, direct_writes,
       sql_text
  FROM v$sql
 WHERE sql_id = :sql_id
 ORDER BY child_number;

4. 产生大量 I/O 的 SQL（按读写量排序）

SELECT sql_id,
       executions,
       ROUND(elapsed_time/1e6,2) AS elapsed_s,
       disk_reads,
       direct_writes,
       buffer_gets,
       rows_processed,
       substr(sql_text,1,120) AS sql_text_120
  FROM v$sqlarea
 ORDER BY (disk_reads + direct_writes) DESC
 FETCH FIRST 30 ROWS ONLY;

5. 热点数据文件 I/O（IOPS / 吞吐 / 平均延迟）

v$filestat 中 readtim/writetim 单位为 1/100 秒，平均延迟(ms) = time/ops×10。

SELECT df.tablespace_name,
       df.file_name,
       fs.phyrds,
       fs.phywrts,
       ROUND(fs.phyblkrd*8/1024,2)  AS read_mb,
       ROUND(fs.phyblkwrt*8/1024,2) AS write_mb,
       CASE WHEN fs.phyrds  = 0 THEN 0 ELSE ROUND((fs.readtim  / fs.phyrds )*10,3) END AS avg_read_ms,
       CASE WHEN fs.phywrts = 0 THEN 0 ELSE ROUND((fs.writetim / fs.phywrts)*10,3) END AS avg_write_ms
  FROM v$filestat fs
  JOIN dba_data_files df ON df.file_id = fs.file#
 ORDER BY (fs.phyrds + fs.phywrts) DESC
 FETCH FIRST 50 ROWS ONLY;

6. Redo 写压力（提交慢时重点）

SELECT event,
       total_waits,
       ROUND(time_waited/100,2) AS time_waited_s,
       ROUND(CASE WHEN total_waits=0 THEN 0 ELSE (time_waited/total_waits)*10 END,3) AS avg_wait_ms
  FROM v$system_event
 WHERE event IN ('log file sync','log file parallel write')
 ORDER BY time_waited DESC;

7. TEMP 表空间 I/O 压力

SELECT ts.name AS tablespace_name,
       ROUND(SUM(t.phyblkrd)*8/1024,2)  AS temp_read_mb,
       ROUND(SUM(t.phyblkwrt)*8/1024,2) AS temp_write_mb,
       SUM(t.phyrds)  AS temp_reads,
       SUM(t.phywrts) AS temp_writes
  FROM v$tempstat t
  JOIN v$tablespace ts ON ts.ts# = t.ts#
 GROUP BY ts.name
 ORDER BY (SUM(t.phyrds)+SUM(t.phywrts)) DESC;

8. ASM 磁盘组使用情况（如使用 ASM）

SELECT name,
       state,
       type,
       total_mb,
       free_mb,
       usable_file_mb,
       ROUND((total_mb-free_mb)/NULLIF(total_mb,0)*100,2) AS used_pct
  FROM v$asm_diskgroup_stat
 ORDER BY used_pct DESC;

9. AWR 历史 I/O 指标趋势（需 AWR 权限）

SELECT begin_time,
       metric_name,
       ROUND(average,3) AS avg_value,
       metric_unit
  FROM dba_hist_sysmetric_summary
 WHERE metric_name IN (
   'Physical Read Total IO Requests Per Sec',
   'Physical Write Total IO Requests Per Sec',
   'Physical Read Total Bytes Per Sec',
   'Physical Write Total Bytes Per Sec',
   'Average Synchronous Single-Block Read Latency',
   'Average Synchronous Write Latency'
 )
 ORDER BY begin_time DESC, metric_name;

二、本次案例：指标解读与结论

2.1 实时 sysmetric 与现象

案例中观测到的典型值：

结论：数据库处于明显的 I/O 绑定（I/O-bound）状态，瓶颈在存储侧，而非 CPU。

2.2 AWR 历史趋势对比

• 3 月 3 日及更早：Average Synchronous Single-Block Read Latency 约 0.4–0.6 ms，读吞吐 ~41–43 MB/s，属于健康水平。
• 3 月 4 日起：同一类指标下，单块读延迟持续在 45–70 ms，吞吐与 IOPS 变化不大。

说明：负载量未明显增加，但每次 I/O 的响应时间大幅变长 → 指向存储/卷性能退化或限速。

2.3 Redo 写等待

• log file sync 平均约 8.4 ms，log file parallel write 约 6.4 ms。
• 相比数据文件读的 60–70 ms，Redo 写是次要矛盾，但 UNDO/Redo 所在盘若与业务数据同卷，也会被同一存储问题拖累。

2.4 高 I/O SQL

案例中 sql_id = a2rfgdanzdgbf 的 MERGE INTO GTT_QUERY_EXT_CALC ... 使用窗口函数、大量扫描，disk_reads/buffer_gets/elapsed_time 很高。该 SQL 是“放大镜”：在存储变慢后，其执行时间被成倍放大；根因仍是底层 I/O 延迟升高。

三、数据文件级分析：RTCIP 表空间异常

按 v$filestat + dba_data_files 查询后，得到类似下表（节选）：

结论：

• RTCIP 业务表空间几乎所有数据文件读延迟在 18–74 ms，部分写延迟达 150–240 ms，异常明显。
• SYSTEM / SYSAUX / USERS / RDSADMIN / CNRTCIOP 仅 7–15 ms，说明并非“全库所有盘都慢”，而是 承载 RTCIP（及 UNDO）的那组存储性能退化。
• 路径形如 /rdsdbdata/db/ORCL_A/...，可判断为云上 Oracle（如 AWS RDS），需结合云监控与存储配置排查。

四、大事务与“能否慢慢等 SQL 跑完”

4.1 场景描述

• 3 月 3 日晚起：约 200 万行新增/修改，分批事务，每批约 5000–20000 条。

4.2 大事务的影响

• 会增加：数据+索引+UNDO+REDO 的写入、UNDO/TEMP 使用、可能的文件/卷扩展。
• 会暂时拉高 I/O 压力，但正常情况下批处理结束或间歇期负载下降后，延迟应回落。
• 本案例中 3 月 4 日后延迟全天维持 45–70 ms，说明环境基线已变差，而非单纯“某条 SQL 在跑所以暂时慢”。

4.3 “磁盘空间不足”会导致 I/O 延迟吗？

• 仅 Oracle 表空间快满、底层卷仍充裕：主要导致扩展失败（如 ORA-01653），不会单纯因“剩余空间少”就把单次 I/O 从 0.5 ms 拉到 60 ms。
• 底层卷也快满（尤其云上精简配置、over-provision）：分配/扩容/回收可能变慢，再叠加大事务，可能拉高延迟。
• 更典型的是下面一种情况：卷配置偏低 + burst credit 用光后被限速。

4.4 能否慢慢等 SQL 跑完？

• 功能上：若没有严重锁阻塞，可以等其跑完，但每个 I/O 都 60–70 ms，总时间会非常长。
• 根本问题：当前现象是“环境基线改变”，不是“这条 SQL 一结束就恢复”。因此优先建议：
  • 与 DBA/云运维确认存储配置与监控（见下文 burst credit 章节）；
  • 批处理可适当减小每批量、错峰执行，减轻峰值 I/O；
  • 真正解决依赖把底层 I/O 延迟降回 < 5 ms，而不是单纯等待。

五、“底层卷配置太低 + 瞬时大事务 + burst credit 用光/被限速”详解

5.1 一句话解释

磁盘（卷）的基础性能配置不高，再叠加一次性很重的大事务，把磁盘能“短时间加速”的额度（burst credit）用完了，云平台开始强制限速（throttle），结果就是 I/O 延迟长期维持在几十毫秒。

5.2 云盘的“基础性能”与“突发额度”

以 AWS 等云为例，块存储常有两个层面：

• 基础性能（baseline）：如 3000 IOPS、125 MB/s，表示长期稳定可用的最低性能。
• 突发性能（burst）与 burst credit：
  • 允许在短时间内高于 baseline 运行；
  • 用“积分池”（burst credit）计量：积分多时可高速跑，积分用光后只能按 baseline 运行，甚至被限速。

类比：baseline 是“匀速 60 码”，burst 是“加速到 120 码”，credit 是油量；油用完后只能回到低速，且恢复较慢。

5.3 与本案例的对应关系

• 3 月 3 日晚：200 万行大批量 DML → 持续高随机读+写。
• 若卷的 baseline 不高，这波负载会快速消耗 burst credit。
• Credit 耗尽后：后续 I/O 被限速，延迟从亚毫秒级升到 45–70 ms，并因 credit 恢复慢、库持续有负载而整天维持高位。
• 现象与观测一致：吞吐 ~43 MB/s、IOPS ~70，变化不大，但延迟从 0.5 ms 跳到 60+ ms。

5.4 为何表现为“负载不大，延迟却很高”

限速后，云盘以“排队”方式服务 I/O。在低配卷上，几十 MB/s、几十 IOPS 可能已接近或用满其能力，请求排队 → 平均延迟从 0.5 ms 升到 60–70 ms。因此：

• CPU 空闲（~7%）；
• IOPS/吞吐数值不高；
• 但磁盘在拖慢业务，所以整体响应变差。

5.5 如何验证

在云控制台（如 AWS RDS/CloudWatch）查看：

1. ReadLatency / WriteLatency：3 月 3 日前是否 < 1 ms，3 月 4 日后是否升至几十 ms。
2. ReadIOPS / WriteIOPS、ReadThroughput / WriteThroughput：是否触及卷配置上限。
3. BurstBalance / BurstCredit（若有）：大事务期间是否从 100% 跌至 0%，且与延迟抬升时间吻合。
4. 卷类型与配置：gp2/gp3/io1、配置的 IOPS/吞吐上限、是否有 I/O 限速或空间告警。

5.6 处理方向

• 存储侧：升级卷类型或提高 IOPS/吞吐配置，使 baseline 满足日常+批处理需求，减少对 burst 的依赖；保证空间充足。
• 应用侧：批处理更小批次、错峰执行；优化高 I/O SQL（如 MERGE），减少全表扫描与随机读。

六、为什么“SELECT * FROM query”仍然很快

6.1 原因概览

• Buffer cache：小表、配置表、维表、最近被批处理或查询用过的数据，往往已在内存；SELECT * FROM query 多为逻辑读（consistent gets），physical reads 很少甚至为 0，因此几乎不碰磁盘，响应很快。
• 表所在表空间：若 query 在 SYSTEM/SYSAUX/USERS 等延迟仅 7–15 ms 的表空间，或数据量小，即使有少量物理读，体感也不明显。
• 慢的是“大量随机 I/O”的 SQL：如 MERGE 扫大表、窗口函数、大批量 DML，需要反复读盘，会把 60 ms 的延迟成倍放大；小表简单 SELECT * 则不会。

6.2 如何验证

对“快”的查询执行（替换为实际表名）：

SET AUTOT TRACEONLY STATISTICS
SELECT * FROM your_query_table;

关注：

• physical reads：若很少或为 0，说明主要在内存完成。
• elapsed time：若很短，且多为 CPU，说明未受当前磁盘高延迟影响。

七、总结

文档由对话内容整理而成，适用于 Oracle 19c 及云 RDS 环境的 I/O 问题排查与归档。