今週の名言
「自分が多数派の側にいると気付いたら、もう意見を変えてもいい頃だ。」
マーク・トウェイン
最近、マーク・トウェインの名言が多いですが、偶然です。
DBバッファの統計を調査する。
前回は、stats$snapshot表からスナップショット一覧を取得するSQL文を紹介しました。
今回から、応用編としてこの表とパフォーマンス・データが格納された表を結合して、１日の統計情報の推移を見てみます。
最初は取っ付き易い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環境は前回とは違って１時間ごとにスナップショットを取得している環境です。
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」の３つの列から成っていることがわかります。
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表と関連する表を結合してデータを取得しますが、結合列はこれら３つの列を必ず指定するようにしましょう。
続く
