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

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

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

4.高速索引スキャンを利用してバッファキャッシュを満杯にする

　バッファキャッシュのサイズよりも大きな、TAB39_BIG表のBツリー索引IDX_TAB39_BIG_COL2に対して、INDEX FAST FULL SCANが行われるクエリを実行した後、バッファキャッシュ上にキャッシュされているオブジェクトとサイズを確認します。

$ sqlplus /nolog
SQL>
/* 索引高速スキャンのヒント句を指定したクエリの実行計画の確認と実行 */
connect TRY/TRY
set autotrace on explain
select /*+ INDEX_FFS(TAB39_BIG, IDX_TAB39_BIG_COL2) */ count(COL2) from TAB39_BIG ;
COUNT(COL2)
-----------
     179200
Execution Plan
----------------------------------------------------------
Plan hash value: 80225013
--------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |  1001 |  7102   (1)| 00:01:26 |
|   1 |  SORT AGGREGATE       |                    |     1 |  1001 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_TAB39_BIG_COL2 |   179K|   171M|  7102   (1)| 00:01:26 |
--------------------------------------------------------------------------------------------
/* バッファキャッシュ上にキャッシュされているオブジェクトとサイズの確認 */
connect / as sysdba
col OWNER for a8
col OBJECT_NAME for a24
select OWNER, OBJECT_NAME, count(*) "BUFFERS", count(*)*8/1024 "MB"
  from V$BH, DBA_OBJECTS
 where OBJD = DATA_OBJECT_ID and OWNER = 'TRY' and V$BH.STATUS != 'free'
 group by OWNER, rollup(OBJECT_NAME)
 order by 4 ;
OWNER    OBJECT_NAME                 BUFFERS         MB
-------- ------------------------ ---------- ----------
TRY      IDX_TAB39_SMALL_COL1              1   .0078125
TRY      IDX_TAB39_BIG_COL1              375  2.9296875
TRY      IDX_TAB39_BIG_COL2            16318 127.484375
TRY                                    16694 130.421875

　バッファキャッシュが満杯な状況を作り出す際に、私が良く使用するテクニックです。今更ですが、今回の私の検証環境のバッファキャッシュのサイズは140MB程度と、非常に小さくしてあります。前回記事の演習2における、確認索引IDX_TAB39_BIG_COL2のサイズは208MBであり、バッファキャッシュの140MBよりも大きいです。この索引を索引高速スキャンすることで、バッファキャッシュがこの索引のブロックで満杯になっていることが確認いただけるかと思います。

　上記の回答例では、TRYスキーマだけで約130MBをキャッシュしていることが分かります。これ以外にも、SYSTEMユーザーのオブジェクトがキャッシュされているので、140MBのバッファキャッシュが満杯になっていました。OWNER列の条件を解放すれば、皆さんの環境でもご確認いただけます。

　ちなみに、この索引高速スキャンの実行は通常の索引スキャンと異なり高速な点も、検証環境を準備する上でとても便利です。理由は、全ての索引リーフブロックをマルチブロックでディスクから読み込めるためです。そうです。索引高速スキャンではdb file scattered read待機イベントが発生しているでしょう。ぜひ確認してみてください。

　さてさて、これで、「TAB39_SMALL表に対して索引を使用した表アクセス（TABLE ACCESS BY INDEX ROWID）を行うクエリを実行した際に、db file sequential read待機イベントが発生するのか」を確認できる準備が整いました。

5.索引を使用した表アクセスを行うクエリを実行した際のパフォーマンス統計情報と待機イベントの確認（成功例）

　バッファキャッシュをフラッシュせずに、演習3で作成したSQLファイルを再度実行した後、パフォーマンス統計情報と発生した待機イベントについて確認します。

$ sqlplus TRY/TRY
SQL> @sql_loop.sql
NAME                                                  VALUE
------------------------------------------------ ----------
physical read IO requests                               909
physical read bytes                                 7446528
physical read flash cache hits                            0
physical read partial requests                            0
physical read requests optimized                          0
physical read total IO requests                         909
physical read total bytes                           7446528
physical read total bytes optimized                       0
physical read total multi block requests                  0
physical reads                                          909
physical reads cache                                    909
physical reads direct                                     0
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

　まずは、実行後のパフォーマンス統計情報を確認してみると、「physical reads prefetch warmup」がカウントアップしていないことから、Pre-Warming機能が動作していなかったことが理解できます。次に、前回同様にマルチブロック読み込みを示す「physical read total multi block requests」もカウントアップしていませんので、この演習のクエリ実行ではシングルブロック読み込みしか発生していません。

　以下は、events 10046のSQLトレースの結果をgrepしたものですが、待機イベントdb file sequential readだけが発生していることが確認できます。

$ cat orcl_ora_7888.trc | grep "db file scattered read" | wc -l
 0
$ cat orcl_ora_7888.trc | grep "db file sequential read" | wc -l
909

　少し遠回りしてしまいましたが、索引を使用した表アクセス（TABLE ACCESS BY INDEX ROWID）、つまりは、「Bツリー索引の各ブロックを1つずつアクセスして該当のリーフブロックにたどりつき、そこで特定されたROWIDを使用して表内の特定の1つのブロックを読み込む」というような、「順番（sequential）にシングルブロックをバッファキャッシュへ読み込む」場合には、db file sequential read待機イベントが発生することをご理解いただけたかと思います。

　ちなみに、「待機イベント」という名前を耳にすると、待機するのだからパフォーマンスに対して悪影響があるものと捉えられがちなので、「待機イベントの発生回数をゼロにするにはどうしたらよいですか？」とご質問を頂くことがあります。この質問は誤りではないですが、正確でもありません。待機イベントには、回数と時間の2つの評価軸が存在します。回数が多くても、合計待機時間や平均待機時間が小さければ、問題のないケースが多いので、「待機イベントの平均待機時間を小さくするにはどうしたらよいですか？」がより正しいご質問となります。

　次回は、バッファキャッシュよりも大きな表にアクセスした際に発生する待機イベントやカウントアップするパフォーマンス統計情報を確認し、最後にAWRレポートの読み方へ戻っていきましょう。それではまた次回お会いしましょう！

筆者紹介

柴田長（しばた つかさ）

日本オラクル データベーススペシャリスト。Oracle GRID Centerの設立当初からオラクルの持つ最新技術をパートナー各社と共同で検証し、これまでにリアルなパフォーマンスに裏付けられた数多くのホワイトペーパーを執筆。2017年現在は、大規模案件の現場を訪問し、お客さまのシステムに最適なソリューションデザインの提案やパフォーマンストラブルの問題解決に従事している