日別アーカイブ: 2015/08/02

STATSPACKデータをExcelでグラフ化する②〜DBバッファ編1〜

今週の名言

「自分が多数派の側にいると気付いたら、もう意見を変えてもいい頃だ。」
マーク・トウェイン

最近、マーク・トウェインの名言が多いですが、偶然です。

DBバッファの統計を調査する。

前回は、stats$snapshot表からスナップショット一覧を取得するSQL文を紹介しました。
今回から、応用編としてこの表とパフォーマンス・データが格納された表を結合して、1日の統計情報の推移を見てみます。
最初は取っ付き易いDBバッファについて調べてみましょう。

DBバッファヒット率の計算式

DBバッファの目的は、頻繁にアクセスされるDBブロックをなるべくメモリ(DBバッファ)に保持しておくことで、低速なディスクI/Oを回避することです。DBバッファ・ヒット率とは、要求されたDBブロックがバッファ内に存在していた割合を示すもので、以下の計算式によります。

DBバッファヒット率
=((CONSISTENT_GETS + DB_BLOCK_GETS)-(PHYSICAL_READS))➗(CONSISTENT_GETS + DB_BLOCK_GETS)✖️100

データ取得SQL文

DBバッファヒット率を取得する方法はいくつかありますが、ここではSTATS$BUFFER_POOL_STATISTICS表を使用する方法を紹介します。
この表はV$BUFFER_POOL_STATISTICSのスナップショットを保持しているものです。
また、DBバッファヒット率だけではなくBuffer Busy Wait統計情報も一緒に取得します。

今回のSTATSPACK環境は前回とは違って1時間ごとにスナップショットを取得している環境です。

select
os.INSTANCE_NUMBER
,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
--,os.SNAP_ID SNAP_ID1
--,ns.SNAP_ID SNAP_ID2
,nb.NAME buffer_pool_name
,decode(( (nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)),0,0,  -- 0除算回避
(trunc((((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
         )
         -(nb.PHYSICAL_READS  - ob.PHYSICAL_READS)
        )
        /((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
         +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
         )*100,1
       )
)
) bhr
,nb.BUFFER_BUSY_WAIT - ob.BUFFER_BUSY_WAIT bbw
from
STATS$SNAPSHOT os
,STATS$SNAPSHOT ns
,STATS$BUFFER_POOL_STATISTICS ob
,STATS$BUFFER_POOL_STATISTICS nb
where 1=1
and trunc(os.SNAP_TIME,'mi') between to_date('xxxx/xx/xx 00','yyyy/mm/dd hh24')
                                 and to_date('xxxx/xx/xx 23','yyyy/mm/dd hh24')
and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/24
and os.SNAP_ID               = ob.SNAP_ID
and os.DBID                  = ob.DBID
and os.INSTANCE_NUMBER       = ob.INSTANCE_NUMBER
and ns.SNAP_ID               = nb.SNAP_ID
and ns.DBID                  = nb.DBID
and os.INSTANCE_NUMBER       = ns.INSTANCE_NUMBER
and os.INSTANCE_NUMBER       = nb.INSTANCE_NUMBER
and os.INSTANCE_NUMBER       = 1
order by
os.instance_number
,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

実行例

実行例は以下のようになります。

SQL> set pages 50
SQL> col BHR for 990.0
SQL> select
  2   os.INSTANCE_NUMBER
  3  ,to_char(trunc(os.SNAP_TIME,'mi'),'yyyy/mm/dd hh24:mi') sdate
  4  --,os.SNAP_ID SNAP_ID1
  5  --,ns.SNAP_ID SNAP_ID2
  6  ,nb.NAME buffer_pool_name
  7  ,decode(( (nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
  8           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)),0,0,  -- 0除算回避
  9   (trunc((((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
10           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
11           )
12           -(nb.PHYSICAL_READS  - ob.PHYSICAL_READS)
13          )
14          /((nb.CONSISTENT_GETS - ob.CONSISTENT_GETS)
15           +(nb.DB_BLOCK_GETS   - nb.DB_BLOCK_GETS)
16           )*100,1
17         )
18   )
19  ) bhr
20  ,nb.BUFFER_BUSY_WAIT - ob.BUFFER_BUSY_WAIT bbw
21  from
22   STATS$SNAPSHOT os
23  ,STATS$SNAPSHOT ns
24  ,STATS$BUFFER_POOL_STATISTICS ob
25  ,STATS$BUFFER_POOL_STATISTICS nb
26  where 1=1
27  and trunc(os.SNAP_TIME,'mi') between to_date('2015/07/28 00','yyyy/mm/dd hh24')
28                                   and to_date('2015/07/28 23','yyyy/mm/dd hh24')
29  and trunc(ns.SNAP_TIME,'mi') = trunc(os.SNAP_TIME,'mi') + 1/24
30  and os.SNAP_ID               = ob.SNAP_ID
31  and os.DBID                  = ob.DBID
32  and os.INSTANCE_NUMBER       = ob.INSTANCE_NUMBER
33  and ns.SNAP_ID               = nb.SNAP_ID
34  and ns.DBID                  = nb.DBID
35  and os.INSTANCE_NUMBER       = ns.INSTANCE_NUMBER
36  and os.INSTANCE_NUMBER       = nb.INSTANCE_NUMBER
37  and os.INSTANCE_NUMBER       = 1
38  order by
39   os.instance_number
40  ,to_char(os.SNAP_TIME,'yyyy/mm/dd hh24mi');

INSTANCE_NUMBER SDATE            BUFFER_POOL_NAME        BHR        BBW
--------------- ---------------- -------------------- ------ ----------
              1 2015/07/28 00:00 DEFAULT               100.0          0
              1 2015/07/28 01:00 DEFAULT               100.0          1
              1 2015/07/28 02:00 DEFAULT               100.0          0
              1 2015/07/28 03:00 DEFAULT               100.0          0
              1 2015/07/28 04:00 DEFAULT               100.0          0
              1 2015/07/28 05:00 DEFAULT               100.0          0
              1 2015/07/28 06:00 DEFAULT               100.0          1
              1 2015/07/28 07:00 DEFAULT               100.0          0
              1 2015/07/28 08:00 DEFAULT               100.0          0
              1 2015/07/28 09:00 DEFAULT               100.0          0
              1 2015/07/28 10:00 DEFAULT               100.0          0
              1 2015/07/28 11:00 DEFAULT               100.0          0
              1 2015/07/28 12:00 DEFAULT               100.0          0
              1 2015/07/28 13:00 DEFAULT               100.0          0
              1 2015/07/28 14:00 DEFAULT               100.0          1
              1 2015/07/28 15:00 DEFAULT               100.0          0
              1 2015/07/28 16:00 DEFAULT               100.0          2
              1 2015/07/28 17:00 DEFAULT                99.9          5
              1 2015/07/28 18:00 DEFAULT               100.0          0
              1 2015/07/28 19:00 DEFAULT               100.0          0
              1 2015/07/28 20:00 DEFAULT               100.0          0
              1 2015/07/28 21:00 DEFAULT               100.0          0
              1 2015/07/28 22:00 DEFAULT               100.0          0
              1 2015/07/28 23:00 DEFAULT               100.0          0

24行が選択されました。

この環境はアクティビティがほとんどないので、DBバッファヒット率はほぼ100%ですが、お手元のSTATSPACKではどのような結果が得られるでしょうか?
この結果を、MS Excelのシートにコピー&ペーストしてグラフにしていくのですが、その要領は次回で紹介します。

テーブルの結合列について

STATS$SNAPSHOT表の主キーを構成する列を確認すると「SNAP_ID,DBID,INSTANCE_NUMBER」の3つの列から成っていることがわかります。

SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  2  from USER_CONS_COLUMNS
  3  where CONSTRAINT_NAME = 'STATS$SNAPSHOT_PK'
  4  order by POSITION;

TABLE_NAME      CONSTRAINT_NAME    COLUMN_NAME       POSITION
--------------- ------------------ --------------- ----------
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  SNAP_ID                  1
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  DBID                     2
STATS$SNAPSHOT  STATS$SNAPSHOT_PK  INSTANCE_NUMBER          3

一方、結合表であるSTATS$BUFFER_POOL_STATISTICS表の外部キーを確認すると、同様に「SNAP_ID,DBID,INSTANCE_NUMBER」となっていることがわかります。

SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION
  2  from USER_CONS_COLUMNS
  3  where CONSTRAINT_NAME = 'STATS$BUFFER_POOL_STATS_FK'
  4  order by POSITION;

TABLE_NAME                    CONSTRAINT_NAME              COLUMN_NAME       POSITION
----------------------------- ---------------------------- --------------- ----------
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   SNAP_ID                  1
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   DBID                     2
STATS$BUFFER_POOL_STATISTICS  STATS$BUFFER_POOL_STATS_FK   INSTANCE_NUMBER          3

STATSPACKデータのグラフ化は、基本的にSTATS$SNAPSHOT表と関連する表を結合してデータを取得しますが、結合列はこれら3つの列を必ず指定するようにしましょう。

続く