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

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

演習2：実演習前の環境準備

　以下のSQLを実行し、演習前の環境準備を実施してください。

$ sqlplus TRY/TRY
SQL>
/* TBS39表領域と、この表領域をデフォルト表領域とするTRYユーザーの作成 */
create tablespace TBS39 datafile '+DATA(DATAFILE)' size 512m ;
create user TRY identified by TRY default tablespace TBS39 ;
grant connect, resource, dba to TRY ;
　
/* TAB39_SMALL表の作成 */
connect TRY/TRY
create table TAB39_SMALL (COL1 number NOT NULL, COL2 char(1000)) ;
　
/* TAB39_SMALL表へ8MB分のレコードを挿入 */
insert /*+append */ into TAB39_SMALL
select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <=7*128*7 ;
commit ;
　
/* TAB39_SMALL表のCOL1列に主キーを作成 */
create unique index IDX_TAB39_SMALL_COL1 on TAB39_SMALL(COL1) ;
alter table TAB39_SMALL add primary key (COL1) using index ;
　
/* TAB39_BIG表の作成 */
create table TAB39_BIG (COL1 number NOT NULL, COL2 char(1000)) ;
　
/* TAB39_BIG表へ約200MB分のレコードを挿入 */
insert /*+append */ into TAB39_BIG
select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <=7*128*200 ;
commit ;
　
/* TAB39_BIG表のCOL1列に主キーを作成 */
create unique index IDX_TAB39_BIG_COL1 on TAB39_BIG(COL1) ;
alter table TAB39_BIG add primary key (COL1) using index ;
　
/* TAB39_BIG表のCOL2列にBツリーを作成 */
create index IDX_TAB39_BIG_COL2 on TAB39_BIG(COL2) ;
　
/* TRYスキーマ内の全オブジェクトのサイズを確認 */
set linesize 120 pages 5000
col SEGMENT_NAME for a24
select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024
from USER_SEGMENTS order by 2, 1 ;
　
SEGMENT_NAME             SEGMENT_TYPE       BYTES/1024/1024
------------------------ ------------------ ---------------
IDX_TAB39_BIG_COL1       INDEX                            4
IDX_TAB39_BIG_COL2       INDEX                          208
IDX_TAB39_SMALL_COL1     INDEX                        .1875
TAB39_BIG                TABLE                          208
TAB39_SMALL              TABLE                            8
　
　
/* TRYスキーマ内の全オブジェクトのオプティマイザ統計情報を取得 */
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRY');

　ここでは、8MBの表「TAB39_SMALL」と208MBの表「TAB39_BIG」の2つの表を作成しています。各表には主キーを作成していますが、表TAB39_BIG側には主キー以外にもBツリー索引IDX_TAB39_BIG_COL2を作成していることだけを覚えておいてください。

　ちなみに、このような検証用データを生成する際に役立つテクニックとして、LEVEL疑似列を使用したINSERT+SELECT文を使っています。こちらも参考にしてみてください。

演習3：「V$MYSTAT」ビューでパフォーマンス統計情報を確認する

　「SYS」ユーザーでバッファキャッシュ上のデータブロックをフラッシュしたあと、「TRY」ユーザーにてTAB39_SMALL表に対してTABLE ACCESS FULLを行うクエリを実行し、「V$MYSTAT」に問い合わせます。

$ sqlplus /nolog
SQL>
/* テストの都合上、バッファキャッシュ上のデータをフラッシュ */
connect / as sysdba
alter system flush buffer_cache ;
　
/* クエリ実行 */
connect TRY/TRY
set autotrace on explain
select /*+FULL(t) */ sum(ora_hash(COL2, 10)) from TAB39_SMALL t ;
　
SUM(ORA_HASH(COL2,10))
----------------------
                 31333
　
Execution Plan
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |  1001 |   253   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |             |     1 |  1001 |            |          |
|   2 |   TABLE ACCESS FULL| TAB39_SMALL |  6272 |  6131K|   253   (1)| 00:00:04 |
----------------------------------------------------------------------------------
　
　
/* パフォーマンス統計情報の確認 */
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                                39
physical read bytes                                 7421952
physical read flash cache hits                            0
physical read partial requests                            0
physical read requests optimized                          0
physical read total IO requests                          39
physical read total bytes                           7421952
physical read total bytes optimized                       0
physical read total multi block requests                 13
physical reads                                          906
physical reads cache                                    906
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

　クエリ実行前に設定した「set autotrace on explain」によって出力された実行計画から、TAB39_SMALL表に対して「TABLE ACCESS FULL 」（= Table Full Scan）が実行されていることを確認できます。そして、このクエリを実行した際のデータブロックの物理読み込みを示す統計情報（"physical read"で始まる統計）をV$MYSTATビューで確認しています。

　ここでは、中でも特徴的な「physical reads」「physical reads cache」「physical read bytes」「physical read total multi block requests」の4つに限定して説明しておきましょう。詳細は「Oracle Database リファレンスマニュアル」を参照してください。

• physical reads：ディスクから読み込まれたデータブロックの合計数
• physical reads cache：ディスクからバッファキャッシュに読み取られたデータブロックの合計数
• physical read bytes：アプリケーションのアクティビティーのみによるディスク読取りの合計サイズ（バイト）
• physical read total multi block requests：1つの要求で2つ以上のデータベースブロックを読み取る要求の合計数

　今回は「physical reads = 906」および「physical reads cache = 906」であることから、ディスクから読み込まれた906個のデータブロックは全てバッファキャッシュ上にキャッシュされたことを示しています。さらに、ブロックサイズが8KBの環境なので、906ブロックは7421952バイト（7MBと少々）で、統計情報physical read bytesの数に合致しますし、また、TAB39_SMALL表のセグメントサイズにも合います。

　この他に、physical read total multi block requests統計情報が「13」へカウントアップしています。これは1回のI/O要求で複数ブロックをバッファキャッシュへ読み込んだマルチブロック読み込みが行われたことを意味しています。

　実はこれが非常に大切です。Table Full Scanのように連続した大量データを読み込む時には、1つのブロック単位で連続に読み込むよりもディスク上に連続配置されている複数ブロックをまとめて読み込む方が効率的です。HDDの性能特性も考慮した理にかなったディスクアクセス方法です。

　これは、『「Table Full Scan」をしたからこの統計情報がカウントアップした』ということなのですが、AWRレポートを読み説く際には逆に考えます。この統計情報がカウントアップしていたということは、「Table Full Scanのような連続した大量データの読み込みが行われた可能性がありそうだ」と推定するのですね。

　次回は待機イベントの詳細を解説しつつ、より大きなTAB39_BIG表を使用してバッファキャッシュ関連の待機イベントやパフォーマンス統計情報の理解を深められる内容を展開します。それではまた次回お会いしましょう！

筆者紹介

柴田長（しばた つかさ）

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