テンプレート

データベース運用におけるパフォーマンスダウンの原因の一つには、意図しないブロックアクセスの増加による負荷高騰があります。

今回はオブジェクト単位でのブロックアクセスを監視とQlik Senseを使用したグラフ化の方法を紹介します。

Enterprise Edition ＋ Diagnostics Packのライセンスをお持ちでリアルタイムSQL監視が利用できる環境であればEnterprise Managerから高負荷SQLを調べられます。"EMトップ画面"→"パフォーマンス／パフォーマンス・ハブ"からI/O負荷の高いSQLを選択すればブロックアクセスの多いオブジェクトの特定も容易です。

オプションなしのEnterprise Edition環境、Standard Edition環境ではDBA_HIST_SNAPSHOT、DBA_HIST_SEG_STAT、DBA_HIST_SEG_STAT_OBJを利用します。DBA_HIST_SEG_STATにはセグメントレベルの統計値が格納されており、*_DELTA列からはDBA_HIST_SNAPSHOTのBEGIN_INTERVAL_TIMEからEND_INTERVAL_TIMEまでの差分が確認できます。

次のようなSQLを実行することで時間帯ごとにオブジェクトのI/O推移を確認できます。

　
　select 
　  sn.BEGIN_INTERVAL_TIME
　  ,sn.END_INTERVAL_TIME
　  ,so.OBJECT_NAME           --オブジェクト名
　  ,so.TABLESPACE_NAME       --表領域名
　  ,ss.LOGICAL_READS_DELTA   --BEGIN-END間の論理読み込み
　  ,ss.PHYSICAL_READS_DELTA  --BEGIN-END間の物理読み込み
　  ,ss.PHYSICAL_WRITES_DELTA --BEGIN-END間の物理書き込み
　  from 
　  DBA_HIST_SEG_STAT ss, 
　  DBA_HIST_SNAPSHOT sn, 
　  DBA_HIST_SEG_STAT_OBJ so 
　where  
　  so.OBJECT_TYPE in ('TABLE','INDEX')
　  and ss.OBJ# =so.OBJ#
　  and ss.DATAOBJ# =so.DATAOBJ#
　  and ss.snap_id = sn.snap_id 
　order by 
　  ss.snap_id;
　

この結果からはYY-MM-DD HH:MI:SS ～ YY-MM-DD HH:MI:SSの間でTESTUSERのTEST_TABにXXXブロックの物理読み込みがあったことがわかります。これらの結果からパフォーマンスダウンの発生した時間帯で特定のオブジェクトにアクセスが集中していないかなどの情報を得ることができます。

なお、ご利用の環境によっては相当量の結果が戻るため、ある程度当たりがついている場合は状況に応じてWHERE句に次のような条件を追加して絞ります。

・3日前まで
and TO_CHAR(sn.BEGIN_INTERVAL_TIME,'YYYY/MM/DD') >= TO_CHAR(SYSDATE-3,'YYYY/MM/DD')

・特定のユーザ
and so.OWNER = '<ユーザ名>'

また、以下のようにパイプを利用することでCSV形式での出力も可能です。※12.2以降であればSQL> set markup csv onで対応できます。

　
　select 
　'"'||sn.END_INTERVAL_TIME||'","'||so.OBJECT_NAME||'","'||so.TABLESPACE_NAME||'","'||ss.LOGICAL_READS_DELTA||'","'||ss.PHYSICAL_READS_DELTA||'","'||ss.PHYSICAL_WRITES_DELTA||'"'
　  from 
　  DBA_HIST_SEG_STAT ss, 
　  DBA_HIST_SNAPSHOT sn, 
　  DBA_HIST_SEG_STAT_OBJ so 
　where  
　  so.OBJECT_TYPE in ('TABLE','INDEX')
　  and ss.OBJ# =so.OBJ#
　  and ss.DATAOBJ# =so.DATAOBJ#
　  and ss.snap_id = sn.snap_id 
　order by 
　  ss.snap_id;
　

STATSPACKをLEVEL 7で取得することでオブジェクト統計を取得することも可能ですが、この場合STATSPACK SNAPSHOTに格納される情報が増えることでの情報取得負荷や格納表領域のサイジングも考慮が必要になります。

また、テキストファイルで出力されるSTATSPACKレポートを時間帯ごとに比較して調査するには多少のコツと根気が必要です。

ブロックアクセスの状況がディクショナリビューで確認できることを覚えておくと、パフォーマンスダウンの調査で役に立つこともあるかもしれません。

DBA_HIST_SNAPSHOTなどを利用することで時間帯ごとのブロックアクセスを見ることはできますが、SELECT結果の数字を見比べてオブジェクトごとのI/O推移を追うのはかなり辛い作業です。

前回のブログで紹介したQlik Senseを利用することで上述のSELECT結果からアクセスブロックの多いオブジェクトをより直感的に調べることができます。

Qlik Senseでは特にユーザが操作しなくてもグラフ同士が自動で関連付けされます。たとえば、IO_STATSのPHYSICAL_READS_DELTAがスパイクしている時間帯をクリックすると、OBJECT_STATSにはその時間帯でアクセスの多いオブジェクトが表示されます。

他の日の同一時間帯もクリックしてみると、前日の同じ時間帯にはTEST_TABにはアクセスがないことがわかりますので怪しいのはこのオブジェクトに対するSQLの実行といった具合にあたりをつけることができます。

STATSPACKのLevel 5や6と併せて確認できるようにしておけば、オブジェクト名を検索キーに該当時間帯のSQL Ordered by Readsなどの情報からSQL文まで確認できるかもしれません。

パフォーマンスダウンに関するお問い合わせの中には「バッチ処理が遅くなった」などの復数実行している処理のうちどれが遅くなったのかが不明なこともあります。

このようなケースでは平常時と問題発生時のAWR／STATSPACKレポートをご提供いただき、Elapsed Timeに対するDB Time/DB CPUの割合、ロードプロファイルやTOP X を見てボトルネックを推測し、SQL Ordered by XXXXを見て...といった調査を行い、「実行計画の変化」や「本来バッチ処理時間に実行されるべきでない他の処理とのI/O競合」の可能性を考慮します。

しかし、ボトルネックの当たりがついていない状態で復数のテキスト形式のレポートを見比べながらの調査は難航することもあります。今回ご紹介したような方法で特定のオブジェクトに対するI/Oが増加しているなどの調査の手がかりを早い段階で掴むことができれば、早期解決に繋がるかもしれません。

筆者情報

• 大野 高志

サービス事業部 付加価値創造部

2007年アシスト入社。Oracle Databaseのサポート業務を経て、サポートセンターに蓄積されたナレッジを使用したサービス開発の立ち上げに従事。現在は「アシストの超サポ」を広め、カスタマーエンゲージメントの構築を実現するための活動を行っている。