AWRレポートでTop 5（最近はTop 10）Wait Eventを見て、User I/Oクラスの待機イベントが上位に来ている場合（特にDB CPUの比率が相対的に低い場合）、次のアクションとしてSegment Statisticsセクションの「Segments by Physical ～」あたりを見て物理I/O負荷の高いセグメントを特定し、原因となっている高負荷SQLを特定するという流れはよくあると思う。
ただし、インスタンス全体のI/O傾向を把握するにはInstance Activity Statsセクションにも注目した方がよい。これによりSegment Statisticsセクションの内容もより深く理解できるようになる。
システム統計情報
Instance Activity Statsセクションの情報ソースはV$SYSSTATビューであり、存在するシステム統計情報（の種類）を確認するためには以下のようにV$STATNAMEビューにアクセスする。
ここでは、物理I/O関連の統計情報のみを確認したいので17行目で絞り込んでいる。
すべての統計情報（11gR2は600個程度）を確認するには17行目のWHERE句をコメントアウトして実行する。
SQL> select
2 decode(CLASS, 1,'User'
3 , 2,'REDO'
4 , 4,'Enqueue'
5 , 8,'Cache'
6 , 16,'OS'
7 , 32,'RAC'
8 , 33,'RAC+User'
9 , 40,'RAC+Cache'
10 , 64,'SQL'
11 , 72,'SQL+Cache'
12 ,128,'Debug'
13 ,192,'Debug+SQL') CLASS_NAME
14 --,STATISTIC#
15 ,NAME
16 from v$statname
17 where NAME like 'physical %'
18 order by
19 CLASS
20 --,STATISTIC#
21 ,NAME;
CLASS_NAME NAME
---------- ----------------------------------------------------------------
Cache physical read IO requests
physical read bytes
physical read flash cache hits
physical read requests optimized
physical read total IO requests
physical read total bytes
physical read total multi block requests
physical reads
physical reads cache
physical reads cache prefetch
physical reads direct
physical reads direct (lob)
physical reads direct temporary tablespace
physical reads for flashback new
physical reads prefetch warmup
physical reads retry corrupt
physical write IO requests
physical write bytes
physical write total IO requests
physical write total bytes
physical write total multi block requests
physical writes
physical writes direct
physical writes direct (lob)
physical writes direct temporary tablespace
physical writes from cache
physical writes non checkpoint
********** ----------------------------------------------------------------
count 27
27 rows selected.
物理I/O関連の統計情報は上のように27個存在していることがわかる。（11gR2）
NAME列でソートはしているが、ある統計情報は別の統計情報のサブセットとなっていたりしていて、この一覧を見ても各統計情報の関係を把握するのは難しい。
そこで、各統計情報の順番を並び替えリファレンス・マニュアルの解説を追記した一覧を作成したので、以下のリンクから参照されたい。（イメージしやすいように実際のAWRレポートでの値を「例」カラムに表示してある。）
物理I/O関連統計
薄黄色網掛けの行は、補足欄に示すようにSegment Statisticsセクションと関連している。
従ってアプリケーション（ユーザSQL）か、それともバックアップ等によるもののどちらが物理I/O負荷の主な要因なのかを特定するためには、Segment Statisticsセクションを確認することに加えて、Instance Activity Statsセクションも参照した方がよい。
また、薄青色網掛けの行は理解しやすいように便宜的に設けたもので、他の統計情報値から導出されるものである。（計算式は補足欄に記述してある。）
#16,17はExadata、さらに#18もフラッシュバック・データベースに関する統計情報であり、手元の環境では具体的な数値を取得できていないので「例」は空欄にしてある。
#34のphysical reads retry corruptは再読込された破損ブロック数と推察されるが詳細は不明である。
以下は、物理関連IO各統計情報の関係を把握するためのポイントを簡単にまとめたものである。
- read/writeの区別
Physicalの次の単語がreadかwriteかで２分される。- read
単数形のreadは次にIO requests、bytesを伴う。単位はそれぞれ異なる。- read requests：読取り要求数
- read bytes：ディスク読取りの合計サイズ(バイト)
- reads：読取りブロック数（＝バッファ数）
複数形のreadsはブロック数を表す。説明の中でバッファ数という箇所はブロック数に読み替えられる。
- write
writeの分類も基本的にreadと同じである。- write requests：書込み要求数
- write bytes：ディスク書込みの合計サイズ(バイト)
- writes：書き込みブロック数（＝バッファ数）
- read
- totalの有無
- total IO requests
read/writeの後がtotalの場合は2種類のブロック要求の合計であり、それぞれの内訳を持つ。- total multi block requests
マルチ・ブロック要求 - total single block requests*
シングル・ブロック要求（この統計情報名は存在しないので、totalとtotal multiの差分として導出する。）
- total multi block requests
- IO requests|bytes
read/writeの後にtotalがない場合は、アプリケーションとアプリケーション以外のIO要求の合計となり、内訳を持つ。- IO requests|bytes (with application*)
アプリケーション（ユーザSQL）による。（）内は便宜的に付けた名称 - IO requests|bytes except application*
アプリケーション以外のバックアップとリカバリおよびその他のユーティリティによる。（この統計情報名も存在しないので、差分を導出する。）
- IO requests|bytes (with application*)
- total IO requests
physical write total multi block requestsとは？
読み込みの場合、Index ScanはSingle Block Read、Full ScanはMulti Block Readとなるが、書き込みの場合も、Single / Multi Blockでの操作が存在する。
これらに関しては以下のマニュアルに記述がある。
Oracle® Database概要 12c リリース1 (12.1) B71299-08
データベース・ライター・プロセス(DBW) 抜粋
「多くの場合、DBWによって書き込まれるブロックは、ディスク内に分散されます。このため、この書込みは、LGWRが実行する順次書込みよりも遅くなる傾向があります。効率を向上させるために、可能であればDBWは、マルチブロック書込みを実行します。マルチブロック書込みで書き込まれるブロックの数は、オペレーティング・システムによって異なります。」
Oracle® Databaseパフォーマンス・チューニング・ガイド 12cリリース1 (12.1) B71276-04
V$ビューを使用したI/Oの問題の識別 抜粋
「単一ブロックと複数ブロックの読取り/書込み操作のI/O統計が含まれます。単一ブロック操作は、128KB以下の小規模なI/Oです。複数ブロック操作は、128KBを超える大規模なI/Oです。」
これ以上の情報は見当たらないのだが、書き込むデータ量により単一ブロック／複数ブロック書き込みを効率的に切り替えているように思われる。
ただし、この「ブロック」がOracleブロックなのか別の単位なのかはよくわからない。
V$IOSTAT_FUNCTION*ビューを確認する
ディスクI/O統計を確認するためにはV$IOSTAT_FUNCTION / V$IOSTAT_FUNCTION_DETAILビューにアクセスする。
ここでは、データ・ファイルに特定して確認するのでV$IOSTAT_FUNCTION_DETAILビューを使用する。（説明ではDBWRやLGWRを「データベース関数」と書いてあるがこれは「機能」のことだろう。）
SQL> SELECT 2 FILETYPE_NAME 3 ,FUNCTION_NAME 4 ,SMALL_READ_REQS SGL_RD_RQ 5 ,LARGE_READ_REQS MLT_RD_RQ 6 ,SMALL_READ_MEGABYTES SGL_RD_MB 7 ,LARGE_READ_MEGABYTES MLT_RD_MB 8 ,SMALL_WRITE_REQS SGL_WR_RQ 9 ,LARGE_WRITE_REQS MLT_WR_RQ 10 ,SMALL_WRITE_MEGABYTES SGL_WR_MB 11 ,LARGE_WRITE_MEGABYTES MLT_WR_MB 12 ,NUMBER_OF_WAITS NUM_WAITS 13 ,WAIT_TIME WAIT_TIME 14 FROM 15 V$IOSTAT_FUNCTION_DETAIL 16 WHERE FILETYPE_ID = 2 -- 「2」はData File 17 ORDER BY FUNCTION_ID; FILETYPE_NAME FUNCTION_NAME SGL_RD_RQ MLT_RD_RQ SGL_RD_MB MLT_RD_MB SGL_WR_RQ MLT_WR_RQ SGL_WR_MB MLT_WR_MB NUM_WAITS WAIT_TIME -------------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- Data File DBWR 0 0 0 0 2488687 42582 26074 5323 0 0 Data File Streams AQ 86 0 1 0 0 0 0 0 86 0 Data File Buffer Cache Reads 174148 2668 1616 1167 0 0 0 0 172894 10507 Data File Direct Reads 4704 0 37 0 0 0 0 0 0 0 Data File Direct Writes 0 0 0 0 5159 4423 127 1025 0 0 Data File Others 22178 0 173 0 5977 33 47 30 28153 1691 6 rows selected.
- SGL_RD_RQ：シングル・ブロックの読取りリクエスト数
- MLT_RD_RQ：マルチブロックの読取りリクエスト数
- SGL_RD_MB：シングル・ブロックの読取り要求により読み取られたMB数
- MLT_RD_MB：マルチブロックの読取り要求により読み取られたMB数
- SGL_WR_RQ：シングル・ブロックの書き込みリクエスト数
- MLT_WR_RQ：マルチブロックの書き込みリクエスト数
- SGL_WR_MB：シングル・ブロックの書込み要求により書き込まれたMB数
- MLT_WR_MB：マルチブロックの書込み要求により書き込まれたMB数
データ・ファイルに対するI/Oは（otherを含め）６つの機能が関わっていることがわかる。
さらにDBWRに限定して確認すると以下のようになる。
SQL> SELECT 2 FILETYPE_NAME 3 ,FUNCTION_NAME 4 ,SMALL_READ_REQS SGL_RD_RQ 5 ,LARGE_READ_REQS MLT_RD_RQ 6 ,SMALL_READ_MEGABYTES SGL_RD_MB 7 ,LARGE_READ_MEGABYTES MLT_RD_MB 8 ,SMALL_WRITE_REQS SGL_WR_RQ 9 ,LARGE_WRITE_REQS MLT_WR_RQ 10 ,SMALL_WRITE_MEGABYTES SGL_WR_MB 11 ,LARGE_WRITE_MEGABYTES MLT_WR_MB 12 ,NUMBER_OF_WAITS NUM_WAITS 13 ,WAIT_TIME WAIT_TIME 14 FROM 15 V$IOSTAT_FUNCTION_DETAIL 16 WHERE FUNCTION_ID = 1 -- 「1」はDBWR 17 ORDER BY FILETYPE_ID; FILETYPE_NAME FUNCTION_NAME SGL_RD_RQ MLT_RD_RQ SGL_RD_MB MLT_RD_MB SGL_WR_RQ MLT_WR_RQ SGL_WR_MB MLT_WR_MB NUM_WAITS WAIT_TIME -------------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- Control File DBWR 25 0 0 0 0 0 0 0 25 0 Data File DBWR 0 0 0 0 2489306 42588 26081 5324 0 0 Other DBWR 0 0 0 0 0 0 0 0 2344677 353294
DBWRはデータ・ファイルへの書き込みを行うプロセスなので、読み込み側の数値が0であることは辻褄が合う。
しかし、230万回以上（累積値）の待機が発生している「Other」というのはどんなファイル・タイプなのだろう。謎は深まるばかりだ。
今回はここまで