高い IO 待機 / ディスクのレイテンシ
システムがディスク IO を待機している。まず負荷が読み取り中心か書き込み中心かを切り分け、その上で原因 (バッファキャッシュの不足、フルテーブルスキャン、checkpoint ストーム、IOPS の上限、一時領域へのスピル) まで掘り下げる。
IO は読み取り中心か、書き込み中心か?
ファイル IO 統計を使って、待機が読み取り側 (ディスクからのデータ読み取り) か書き込み側 (redo/checkpoint/データの書き込み) かを判断する。これでツリーが 2 つに分かれる。
SELECT function_name, small_read_reqs+large_read_reqs AS reads, small_write_reqs+large_write_reqs AS writes,
small_read_megabytes+large_read_megabytes+small_write_megabytes+large_write_megabytes AS mb
FROM v$iostat_function ORDER BY mb DESC;- 主に読み取り IO
- 主に書き込み IO
なぜ読み取り IO が高いのか?
過剰な物理読み取りには主に 3 つの原因がある: バッファキャッシュが小さすぎる (ワーキングセットが RAM に収まらない)、クエリがフルテーブルスキャンを行っている、または大規模なソート/ハッシュ操作が一時領域にスピルしている。
SELECT ROUND((1-(pr.value/(dbg.value+cg.value)))*100,2) AS buffer_hit_pct
FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg
WHERE pr.name='physical reads' AND dbg.name='db block gets' AND cg.name='consistent gets';
SELECT sql_id, disk_reads, executions FROM v$sql ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;- バッファキャッシュヒット率が低く、ワーキングセットが収まらない
- クエリがフルテーブルスキャンを行っている
- 大規模なソート/ハッシュが一時領域にスピルしている
なぜ書き込み IO が高いのか?
書き込み側のボトルネックは、定期的な checkpoint/フラッシュが一時的にディスクを飽和させる (checkpoint ストーム) か、ストレージに負荷を維持するだけの IOPS/スループットがないことに起因する。
SELECT event, total_waits, ROUND(time_waited/100,1) AS sec
FROM v$system_event
WHERE event IN ('log file parallel write','db file parallel write','checkpoint completed')
ORDER BY time_waited DESC;- 突発的な checkpoint/フラッシュの波 (checkpoint ストーム)
- ストレージが継続的な負荷に追いつけない (IOPS の上限)
根本原因: バッファキャッシュの不足
頻繁にアクセスされるデータセット (ワーキングセット) がバッファキャッシュに収まらないため、ブロックが常にディスクから再読み取りされる。ヒット率が低く、物理読み取りが多い。
根本原因: フルテーブルスキャン
適切なインデックスがないため、クエリが大きなテーブルを端から端までスキャンしている。これにより物理読み取りが多くなるだけでなく、不要なブロックでキャッシュが汚染される。
根本原因: 一時領域にスピルするソート/ハッシュ
大規模なソート、ハッシュ結合、group by の操作が作業メモリに収まらず一時領域 (temp/tempdb) に書き込み、大量の読み書き IO を発生させている。
根本原因: checkpoint ストーム
ダーティページが長時間蓄積され、短い間隔でまとめてディスクに書き込まれる。この突発的な書き込みの波が定期的に IO を飽和させる。
根本原因: IOPS / スループットの不足
ストレージに、ワークロードが必要とする読み取り+書き込み要求に応えるだけの IOPS または帯域幅がない。キューの深さとレイテンシが恒常的に高い。