第7回 続々・バッファキャッシュ関連の待機イベントとパフォーマンス統計情報を読み解く：しばちょう先生の試して納得！DBAへの道（改）（7）（1/3 ページ）

データベース技術や知識は、座学だけでなく「実際に手を動かして、実際に試して」理解を深めよう──。日本オラクルのデータベーススペシャリストが「新人をDBAに育てる際に使用する課題」をベースに、DBAがすぐ実践できる即効テクニックを紹介。今回は「バッファキャッシュ関連の待機イベントとパフォーマンス統計情報の読み解き方」について、前々回、前回に引き続き解説します。

[柴田長，日本オラクル株式会社]

※本連載は、日本オラクルの技術解説ブログ「Oracle Technology Network：しばちょう先生の試して納得！DBAへの道」より、提供者の許可の下、最新情報の追記とともに一部改訂して転載するものです。

しばちょう先生の試して納得！DBAへの道（改）

　皆さんこんにちは。日本オラクルの“しばちょう”こと、柴田長（しばたつかさ）です。

　「データベースのパフォーマンス問題が発生しました！」――そのような際に活躍するのがAWRレポートやStatspackレポートです。しかし、そこに絶妙なタッチで描かれている待機イベントやパフォーマンス統計情報の意味が分からなければ、問題を解決することはできません。

　今回は、第5回、第6回の記事に続き、バッファキャッシュ関連の待機イベントやパフォーマンス統計情報の読み解き方を体験してみましょう。

　「索引を使用した表アクセス（TABLE ACCESS BY INDEX ROWID）」、つまり、「Bツリー索引の各ブロックに1つずつアクセスして該当のリーフブロックにたどり着き、そこで特定されたROWIDを使用して表内の特定の1つのブロックを読み込む」といった、順番（sequential）にシングルブロックをバッファキャッシュへ読み込む場合には、「db file sequential read」待機イベントが発生します。また、全表スキャンのように大量のレコードにアクセスする処理において、一度のディスク読み込みで複数のブロックをバッファキャッシュへ読み込む場合には、「db file scattered read」待機イベントが発生していましたね。

　前回までの説明で、このことをご理解いただけたかと思います。今回は、全表スキャンのように、大量のレコードにアクセスする処理は同じでも、「対象となる表のサイズがバッファキャッシュよりも大きな場合に発生する、待機イベントとカウントアップするパフォーマンス統計情報」について体験してみましょう。使用するスキーマは前回と同様に、前々回に作成したものを利用します。

演習1：バッファキャッシュのサイズと表のセグメントサイズの確認

　「SYS」ユーザーでshow parameterコマンドを実行してバッファキャッシュのサイズを確認後、「TRY」ユーザーでUSER_SEGMENTビューに問い合わせを行い、TAB39_BIG表のセグメントサイズを確認します。

$ sqlplus /nolog
SQL>
/* バッファキャッシュのサイズ確認 */
connect / as sysdba
show parameter db_cache_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
db_cache_size                        big integer 140M
/* TRYスキーマ内のTAB39_BIG表のセグメントサイズを確認 */
connect TRY/TRY
set linesize 120 pages 5000
col SEGMENT_NAME for a24
select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024
from USER_SEGMENTS where SEGMENT_NAME='TAB39_BIG' ;
SEGMENT_NAME             SEGMENT_TYPE       BYTES/1024/1024
------------------------ ------------------ ---------------
TAB39_BIG                TABLE                          208

　バッファキャッシュのサイズは、初期化パラメータの「db_cache_size」で設定している値ですから、「show parameter」コマンドで確認することができますね。また、次の表セグメントのサイズを確認するために、USER_SEGMENTビューへアクセスする方法は、問題なく実行できると思います。

　少しだけ、バッファキャッシュのサイズ確認について解説させていただくと、最近はシステムグローバル領域（System Global Area：SGA）内のバッファキャッシュや共有プールなどのサイズが自動調整される自動共有メモリ管理（Automatic Shared-Memory Management：ASMM）や、SGAとプログラムグローバル領域（Program Global Area：PGA）の間で自動的にサイズ調整が行われる自動メモリ管理（Automatic Memory Management：AMM）で、メモリ関連のパラメータ設定が簡素化されています。その場合、明示的にdb_cache_size初期化パラメータを指定していないケースもあるので、V$MEMORY_DYNAMIC_COMPONENTSビューやV$SGA_DYNAMIC_COMPONENTSビューのCURRENT_SIZE列の値でバッファキャッシュのサイズを確認することをお勧めします。

　ちなみに、別の回で解説しますが、ASMMやAMMを利用した場合は、バッファキャッシュのサイズが自動的に調整されます。しかし、極端に小さくなり過ぎてパフォーマンス問題が発生することを避けるため、明示的にdb_cache_size初期化パラメータを設定し、最低限確保すべきバッファキャッシュのサイズを指定することを、私はお勧めします。

　ということで、今回の検証環境では、表TBS39_BIGのサイズはバッファキャッシュのサイズよりも大きいことが確認できましたね。この状態で、表TBS39_BIGの全レコードへアクセスする全表スキャンを実行した際に、どのような動作となるのかを次の演習で見ていきましょう。

演習2：バッファキャッシュのサイズよりも大きな表の全レコードを読み込んだ際のパフォーマンス統計情報の確認

　バッファキャッシュのサイズよりも大きなTAB39_BIG表に対して、TABLE ACCESS FULLが行われるクエリを実行した後、パフォーマンス統計情報について確認します。

$ sqlplus / as sysdba
SQL>
/* テストの都合上、バッファキャッシュ上のデータをフラッシュ */
alter system flush buffer_cache ;
/* 全表スキャンのヒント句を指定したクエリの実行計画の確認と実行 */
connect TRY/TRY
set autotrace on explain
select /*+FULL(t) */ sum(ora_hash(COL2, 10)) from TAB39_BIG t ;
(実行結果と実行計画は省略)
set lines 150 pages 5000
col NAME for a48
select S.NAME, M.VALUE
  from V$MYSTAT M, V$STATNAME S
 where M.STATISTIC# = S.STATISTIC#
   and S.NAME like 'physical read%'
 order by 1 ;
NAME                                                  VALUE
------------------------------------------------ ----------
physical read IO requests                               420
physical read bytes                               209797120
physical read flash cache hits                            0
physical read partial requests                            0
physical read requests optimized                          0
physical read total IO requests                         420
physical read total bytes                         209797120
physical read total bytes optimized                       0
physical read total multi block requests                401
physical reads                                        25610
physical reads cache                                     10
physical reads direct                                 25600
physical reads direct (lob)                               0
physical reads direct temporary tablespace                0
physical reads for flashback new                          0
physical reads prefetch warmup                            0
physical reads retry corrupt                              0

　前回までは、バッファキャッシュのサイズと比較して非常に小さな表を検索するという演習を行ってきました。シングルブロック読み込み時の待機イベント「db file sequential read」、マルチブロック読み込み時の待機イベント「db file scattered read」は、いずれもディスクからバッファキャッシュ上への読み込みを示していましたね。

　一方、この演習は、「バッファキャッシュのサイズよりも大きな表TAB39_BIGをTable Full Scanする」というものです。物理読み込み関連のパフォーマンス統計情報で特徴的な傾向が出ていますね。これまでの演習では一度もカウントアップしていなかった、パフォーマンス統計情報「physical reads direct」に大きな値が入っています。ということで、この統計情報の説明をリファレンスマニュアルから原文のまま抜き出したものが次です。

physical reads direct　バッファキャッシュをバイパスしてディスクから直接読み込んだ読み取りの数。例えば、高帯域幅での集中的なデータ操作（パラレル実行）などでは、ディスク・ブロックを読み取るとき、バッファキャッシュがバイパスされる。これによって、転送率が最大化され、バッファキャッシュ内に存在する共有データブロックの早期エージングが防止される。

　「physical reads cache」はバッファキャッシュ上へディスクから読み込んだ合計数でしたが、「physical reads direct」は「バッファキャッシュのバイパス」＝「バッファキャッシュ上へキャッシュしないディスク読み込み」です。つまりは、プロセスごとのPGA領域へブロックを読み込んでいると理解してください。そのメリットもしっかりと記載されているあたり、素敵なマニュアルですね！　とはいえ、私なりに噛み砕いて説明しておきますね。

　バッファキャッシュ上へデータブロックをキャッシュするということは、バッファキャッシュ上の空きを探したり確保したりしなければなりません。複数のサーバプロセスが同じバッファキャッシュ領域を操作していることを考慮すれば、その制御にCPUコストを消費したり、ラッチ競合（Oracle Databaseにおいて内部的に処理をシリアル化するためのロック）が発生したりしますが、バッファキャッシュをバイパスすると、それらのコストが不要となりますよね。上記でいう「転送率が最大化」に該当します。

　次に、「バッファキャッシュ上へ大きな表をキャッシュするとどうなるのか」を考えてみましょう。大きな表をキャッシュするためには、それ以前にキャッシュされているデータブロックをキャッシュ上から追い出さなくてはなりません。追い出すにためには、先ほどと同じようにCPUコストやラッチ競合が発生します。また、もし追い出して大きな表だけがキャッシュされている状況になったら、他のオブジェクトにアクセスするクエリの性能はどうなるでしょうか？　キャッシュヒット率が低下して、パフォーマンス劣化が引き起こされるでしょう。全くもって良いことはありませんよね。

　ということで、上記の「バッファキャッシュ内に存在する共有データブロックの早期エージングが防止される」は絶対的な効果があるのです。よって、データベースシステム全体のパフォーマンスの効率化を考えた場合、バッファキャッシュのサイズと比較して大きな表は、バッファキャッシュをバイパスしてディスクから読み込んだ方が良いということが腹に落ちますよね。

　さて、ここで気になるのが、「本当にバッファキャッシュ上にブロックがキャッシュされていないのか？」ですよね。ということで、次の演習で確認をしてみましょう。