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：書き込みブロック数（＝バッファ数）

totalの有無
- total IO requests
  read/writeの後がtotalの場合は2種類のブロック要求の合計であり、それぞれの内訳を持つ。
  - total multi block requests
    マルチ・ブロック要求
  - total single block requests*
    シングル・ブロック要求（この統計情報名は存在しないので、totalとtotal multiの差分として導出する。）
- IO requests|bytes
  read/writeの後にtotalがない場合は、アプリケーションとアプリケーション以外のIO要求の合計となり、内訳を持つ。
  - IO requests|bytes (with application*)
    アプリケーション（ユーザSQL）による。（）内は便宜的に付けた名称
  - IO requests|bytes except application*
    アプリケーション以外のバックアップとリカバリおよびその他のユーティリティによる。（この統計情報名も存在しないので、差分を導出する。）

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」というのはどんなファイル・タイプなのだろう。謎は深まるばかりだ。

今回はここまで

このエントリは「JPOUG Advent Calendar 2016」の13日目です。
昨日はcharade_oo4oさんの「Oracle on Hyper-V 2016」でした。

複数のAWRレポートを作成するのは面倒

AWRスナップショットはデフォルトで1時間に1回取得されるので、レポート期間1時間のAWRレポートは1日分で24個になる。
1日分のレポートを1個ずつ作成するのは面倒なので、一度に作成する方法を考えてみた。

考慮する仕様は以下の３つ

本日からn日前以降のレポートを全て出力する。（ただし本日分は含まない）
レポート期間は1時間で連続するスナップショットIDを指定する。
スナップショットはMMONプロセスで自動的に取得されたものを対象とする。（定常運用をイメージしているので、負荷テスト時のようにスナップショットをアドホックに取得していないことを前提。ちなみに手動で取得すると毎正時の自動取得がスキップされる場合があるので注意。）

作成用スクリプト

以下のPL/SQLスクリプトに適当な名前を付けて保存する。（例では「mkscr.sql」）

set echo off
set feedback off
set verify off
set trimspool on
set serveroutput on
spool getawrr.sql replace
DECLARE
  num_day NUMBER := &1;
  CURSOR sid_cur IS
    select
    -- es.DBID
     es.INSTANCE_NUMBER
    ,to_char(round(bs.END_INTERVAL_TIME,'mi'),'yyyy/mm/dd hh24') BEGIN_HOUR
    ,bs.SNAP_ID BEGIN_SNAP
    ,es.SNAP_ID END_SNAP
    from
     DBA_HIST_SNAPSHOT bs
    ,DBA_HIST_SNAPSHOT es
    where 1=1
    and bs.END_INTERVAL_TIME >= trunc(sysdate-num_day)
    and bs.END_INTERVAL_TIME <  trunc(sysdate)
--  and trunc(es.END_INTERVAL_TIME,'mi') = trunc(bs.END_INTERVAL_TIME,'mi') + 1/24
    and abs(round(es.END_INTERVAL_TIME,'mi') - round(bs.END_INTERVAL_TIME,'mi') < (1/24)*1.1
    and bs.SNAP_ID < es.SNAP_ID
    and bs.DBID = es.DBID
    and bs.INSTANCE_NUMBER = es.INSTANCE_NUMBER
    and bs.SNAP_FLAG = 0
    and es.SNAP_FLAG = 0
    order by
     bs.END_INTERVAL_TIME;
BEGIN
  FOR sid_rec IN sid_cur LOOP
    dbms_output.put_line('-- '||sid_rec.BEGIN_HOUR);
    dbms_output.put_line('define report_type=html');
    dbms_output.put_line('define num_days='||num_day);
    dbms_output.put_line('define begin_snap='||sid_rec.BEGIN_SNAP);
    dbms_output.put_line('define end_snap='||sid_rec.END_SNAP);
    dbms_output.put_line('define report_name=awrrpt_'||sid_rec.INSTANCE_NUMBER||'_'||sid_rec.BEGIN_SNAP||'_'||sid_rec.END_SNAP||'.html');
    dbms_output.put_line('@?/rdbms/admin/awrrpt.sql');
  END LOOP;
END;
/
spool off
set echo on
set feedback on
set verify on

補足説明

8行目：n日前の「n」は実行時に引数で置換変数に渡す。
20行目：n日前以降の条件。AWRリポジトリに残っている以上の日数も指定できるが、データがないので残っている分しかレポートはできない。
~~22行目：1時間ごとにスナップショットを取得している場合。30分間隔であれば「1/48」に書き換える。~~
23,24行目：22行目の条件だとスナップショット間隔がきっかり1時間でない場合のレポートが欠損してしまうため、10%（1時間であれば6分）未満の誤差にも対応した。
27,28行目：PL/SQLパッケージ（DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT）により手動で取得されたスナップショットを除外している。（この条件はなくてもよいかも。）
34行目：レポートファイル名は対話型で実行した場合のデフォルト名で出力するようにした。

実行例

実行方法は簡単。作成用スクリプトを実行（1行目）し、SQL*Plusを起動したカレント・ディレクトリに出力されたスクリプト（例では「getawrr.sql」、内容は上書きされるので最後に実行した内容が保存される。）を実行（35行目）するだけでよい。
注意点としては、日数を引数で必ず指定することである。（未指定の場合のハンドリングは特に考慮していない。）

SQL> @mkscr 1   <=== 必ず日数を引数で指定する SQL> set echo off
-- 2016/12/06 00
define report_type=html
define num_days=1
define begin_snap=4686
define end_snap=4687
define report_name=awrrpt_1_4686_4687.html
@?/rdbms/admin/awrrpt.sql
-- 2016/12/06 01
define report_type=html
define num_days=1
define begin_snap=4687
define end_snap=4688
define report_name=awrrpt_1_4687_4688.html
.................................................
-- 2016/12/06 22
define report_type=html
define num_days=1
define begin_snap=4708
define end_snap=4709
define report_name=awrrpt_1_4708_4709.html
@?/rdbms/admin/awrrpt.sql
-- 2016/12/06 23
define report_type=html
define num_days=1
define begin_snap=4709
define end_snap=4710
define report_name=awrrpt_1_4709_4710.html
@?/rdbms/admin/awrrpt.sql
SQL> set feedback on
SQL> set verify on
SQL> !ls -l getawrr.sql
-rw-r--r--. 1 oracle oinstall 4128 12月 7 17:24 2016 getawrr.sql
SQL> @getawrr.sql    <== 生成されたスクリプトを実行する
（以下、AWRレポート作成）

これで複数（1日分であれば24個）のAWRレポートが一気に作成される。（レポート内容によっては時間がかかるので、実行タイミングはDBサーバの負荷状況に留意したほうがよいだろう。）

特定の時間帯だけが必要であれば、該当部分をコピペで選択して実行してもよい。

カレントディレクトリに出力されるので、実行するディレクトリをどこにするかを考慮した方がよいかもしれない。

もっと手抜きして、作成用スクリプトの最終行に「@生成スクリプト名」を追記すれば、一気に作成まで行うことができる。（今回は生成スクリプトの内容を一度確認することも考慮して別に実行することとした。）

明日はYousuke Yadaさんです。

サイクル＆オラクル

サイクリングとデータベースを楽しむサイト

「AWR」タグアーカイブ

物理I/O関連統計情報について