db file scattered read

目次

概要

Oracleはユーザーが要求したデータがSGAのバッファ・キャッシュに存在しない場合、サーバープロセスがデータファイルから該当するデータブロックをバッファ・キャッシュにロードします。これを従来型パスI/O(Conventional Path I/O)と言います。従来型パスI/Oはマルチ・ブロック読取り方式とシングル・ブロック読取り方式に分けられます。マルチ・ブロック読取り方式は1回にいくつかの連続したブロックを読込むI/Oで、シングル・ブロック読取り方式は1回に一つのブロックだけを読込むI/Oです。

Oracleは全表走査(Full Table Scan)や高速全索引スキャン(Fast Full Index scans)を実行する場合、性能保障のために複数ブロックを一度に読込むマルチ・ブロック読取りを実行します。マルチ・ブロック読取りを1回実行する度に物理I/Oが終了するまでdb file scattered read待機イベントで待機します。

マルチ・ブロック読取りは、DB_FILE_MULTIBLOCK_READ_COUNT(以降MBRC)パラメータで指定された値で行われます。この値は、OSごとに最大値が異なるので、次のような方法で最大値を確認することができます。

SQL> alter system set db_file_multiblock_read_count=10000;

システムが変更されました。

SQL> show parameter db_file_multiblock_read_count

NAME                           TYPE      VALUE
------------------------------ --------- --------------------------
db_file_multiblock_read_count  integer   128

Oracleは全表走査を実行する場合でもシングル・ブロック読取りを行う場合があります。この場合には 全表走査と言ってもdb file sequential read待機が発生することになります。全表走査でシングル・ブロック読取りをするケースと、MBRCより小さな数のブロックを読み込むケースは次のようです。

  • エクステントの最後尾に到逹した場合 : 例えば1つのエクステントが 9ブロックで構成され、1回のマルチ・ブロック読取りで8ブロックを読込むとすると、最初にマルチ・ブロック読取りで8ブロックを読込み、残った1ブロックはシングル・ブロック読取りで読込むことになります。もし、残ったブロックが 2個だった場合はマルチ・ブロック読取りが実行されますが、2個のブロックだけを読込みます。
  • スキャン途中にキャッシュされたブロックがある場合 : 例えば8個のブロックを読込むのにその中の3番目のブロックがキャッシュされていたら、Oracleは前の 2個をマルチ・ブロック読取りで読込み、3番目のブロックに対しては1回の論理 I/Oを実行し、残った5個のブロックを再度マルチ・ブロック読取りで読込むようになります。このようなケースが頻繁に発生する場合、不要なI/Oが発生しますので、全表走査の速度を低下させる要因となります。
  • 行連鎖がある場合 : 全表走査を実行している途中で行連鎖に遭遇すると、Oracleは残りのレコードを読込むために追加で I/Oを行いますが、この時はシングル・ブロック読取りが実行されます。行連鎖と行移行の違いは、正確に理解しておく必要があります。行連鎖はレコード長がブロックサイズより大きい場合に発生します。従って更に大きいサイズのブロックを使用するとか、PCTFREEを減らすこと以外に行連鎖を解消する方法はありません。行移行は、あるブロックに入っていたレコードが更新処理によりレコード長が長くなり、元のブロックに書換える領域がない場合に発生します。この場合実際のレコードは他のブロックに移動し、元々のブロックには、移動先のブロックとレコードの位置を示す ROWIDが記録されます。

行移行は特に索引を通じて表をスキャンする場合に性能に大きな影響を与えます。1レコードを読込むのに2つ以上のブロックを読込まなければならないからです。

行移行が全表走査に与える影響に対しては少し注意が必要です。全表走査は最高水位標以下の全てのブロックを初めから順に読込む作業です。Oracleは全表走査実行中に行移行に遭遇したとしても追加のシングル・ブロック読取りは発生せずに読取り作業をそのまま続行します。いずれスキャンを実行する途中でまた読込むことが分かっているからです。従って最高水位標の位置が同じであれば、行移行があろうとなかろうと全表走査そのものの性能はほとんど変わりません。もちろん行移行の発生によりエクステントが追加され、最高水位標がもっと遠くに移動すれば全表走査の性能に影響を与えます。

待機パラメータと待機時間

待機パラメータ

db file scattered read待機イベントの待機パラメータは以下の通りです。

  • P1 : ファイル番号
  • P2 : ブロック番号
  • P3 : ブロック数

待機時間

I/O 関連待機イベントなのでタイムアウトは発生せず、セッションは要求したブロックの読込みが完了するまで待機します。

チェックポイントとソリューション

Oracleの I/O レイヤー別解決策

SGAに該当のデータをロードするI/Oは一般的な作業なので、db file scattered read待機イベントはdb file sequential read待機イベントとともにOracleで極一般的に発生する待機イベントです。データファイルからブロックを読込もうとすれば結局マルチ・ブロック読取りやシングル・ブロック読取りを実行するしかないからです。db file scattered read待機を人々が嫌がる理由はそれが物理I/Oと関連していること、そして全表走査と同時に発生するという理由のためです。これは結局全表走査は悪いことなのかという問題に繋がりますが、答えは悪い場合もあれば悪くない場合もあるということになります。全ての作業は適切に使われた時だけ良いことであり、そうではない場合は悪いことということになります。例えば広範囲の索引検索はバッファ・キャッシュ領域を急速に消費し致命的な性能問題が発生する場合があります。

Oracleの I/O レイヤー別にdb file scattered readによる待機問題が発生する場合と解決策を見て行きましょう。

アプリケーションレイヤー

db file scattered read待機が発生する主な SQL文を抽出しなければなりません。もし、不要に全表走査や高速全索引スキャンを実行しているようであればSQL文を修正するとか、より効率的な索引を作成すれば問題は解決されます。

広範囲のデータを読込む時は全表走査が有利になるケースが多く見られます。無理やり索引を作成するのではなく、該当のSQL文の特性を考慮して全表走査が有利なのか索引範囲スキャンが有利なのかの判断が必要になります。

Oracleメモリーレイヤー

バッファ・キャッシュの大きさがあまりにも小さすぎた場合には、物理I/Oが繰り返し必要となりそれと比例してdb file scattered read待機も増加します。この場合free buffer waits待機イベントも発生する確率が高くなります。全表走査によるdb file scattered read待機の致命的な面は、読取り作業の性能低下だけではなく、バッファ・キャッシュの効率性が低下してセッションの作業に影響を与えるというところにあります。このような観点から、全表走査を扱う効果的な方法の一つは複数バッファ・プールを使うことです。しかし、1回読込むだけで再利用しないデータを無理やりバッファ・キャッシュ領域にキャッシュして、他のユーザーの作業にまで影響を与える必要があるのか、と疑問を持つDBAの方も中にはいます。

このような背景もあり、複数バッファ・プールはバッファ・キャッシュを効果的に管理する有力な方法ですが、残念ながらあまり使われてはいません。

実際のテストを通じてこの機能が性能にどの程度多い影響を及ぼすのかを検証してみる事にしましょう。テストのシナリオは以下の通りです。

  • 大きさが16MB程度の表を5つ、READ_TEST1~READ_TEST5を作成します。
  • ケース1ではDB_CACHE_SIZE値を32MBとし、5つの表を全てDEFAULTバッファ・プールにあげます。
  • ケース2ではDB_CACHE_SIZE値を16MB、DB_RECYCLE_CACHE_SIZE値を16MBに設定し、READ_TEST1表はDEFAULTバッファ・プールに、READ_TEST2~READ_TEST5の4つの表はRECYCLEバッファ・プールにあげます。
  • ケース1とケース2でdb file scattered read待機がどのように発生するのかを確認します。

— テストの目的は同時に5つのセッションが、それぞれREAD_TEST1からREAD_TEST5に対して全表走査を実行した時(セッション1=READ_TEST1スキャン、セッション2=READ_TEST2スキャン、セッション3=READ_TEST3スキャン・・・)、READ_TEST1のスキャン速度を保障することにあります。READ_TEST1はよくアクセスされる表という前提です。

ケース1 : READ_TEST1~READ_TEST5 が全てDEFAULTバッファ・プールを使用した場合には全セッションで高いdb file scattered read待機時間が見られます。

セッション1 : READ_TEST1表を10回全表走査

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
db file scattered read                1038        7053
SQL*Net message from client             19         810
db file sequential read                 92         437
...
セッション2 : READ_TEST2表を10回全表走査

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
db file scattered read                1054        7075
SQL*Net message from client             19        2532
db file sequential read                 83         452
...

ケース2 : READ_TEST2~READ_TEST5表はRECYCLEバッファ・プールを使うため、DEFAULTバッファ・プールに対する競合はなくなります。従って、DEFAULTバッファ・プールを使うREAD_TEST1に対して全表走査を実行するセッション1は、db file scattered read待機時間が他のセッションに比べて非常に低くなります。一つ面白いのは、READ_TEST1表だけではなくREAD_TEST2~READ_TEST5表に対するdb file scattered read待機時間も減ったという事実です。バッファ・キャッシュに対する競合が減る分、その効果が2つのバッファに現われたと推測できます。

セッション1 : READ_TEST1表を10回全表走査

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
db file scattered read                 124         882 <-READ_TEST1に
db file sequential read                  7          14   対する待機は
log file sync                            2           4      大きく減る。
...
セッション2 : READ_TEST2表を10回全表走査

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
db file scattered read                1170        6552
db file sequential read                 78         381
read by other session                   13          33
..

上記のテスト結果で分かるようにバッファ・プールを効果的に使うことでかなりの性能改善効果が見込めるということを覚えておいて下さい。複数バッファ・プールは3つの点でバッファ・キャッシュの性能を改善させます。

  • よくアクセスされるオブジェクトをメモリーに常住させることで物理的なI/Oを最小化します。
  • アクセス頻度の低いデータは、メモリーで再利用することでメモリーの無駄使いを最小化します。
  • 各バッファごとに別々のcache buffers lru chainラッチを使うので、ラッチ競合を減少させる効果があります。

全表走査を効果的に実行するもう一つ方法は、DB_FILE_MULTIBLOCK_READ_COUNTパラメータの値を高くすることです。このパラメータはマルチ・ブロック読取り実行時に一度に読込むブロック数になります。従ってこの値が高ければそれだけ全表走査の速度が向上してdb file scattered read待機が減ることになります。この値をシステム全体レベルで高くすることは望ましくないので、ALTER SESSION SETコマンドで該当SQLを実行する間だけ値を高めた方が良いでしょう。この値が高くなれば全表走査に対するコストが低く計算され、SQLの実行計画が変更される可能性があるためです。

大きいサイズのブロックを使うこともまた全表走査の性能を高めます。大きいサイズのブロックは次のような2つの観点で全表走査の性能を改善させます。

  • 1ブロック当たりのレコード数が増加するので、同じ大きさの表を作成する時のブロック数が減ります。従ってその分マルチ・ブロック読取りの回数も減ります。
  • ブロック・サイズが大きければ、行連鎖や行移行が発生する確率が低くなります。それに伴い無駄なI/Oも減るようになります。

ほとんどの OLTPシステムではデフォルトのブロック・サイズ(8KB)を使うのが一般的です。しかし、大量のデータを頻繁にスキャンしなければならないDSSシステムの場合には、より大きいサイズのブロックを使うことで性能改善效果を得ることができます。

Oracleセグメントレイヤー

パーティション表を利用することで全表走査の範囲を削減できるかを検討する必要があります。例えば、100万件のデータ中10万件を得るために全表走査を実行しなければならない場合に、10万件にあたる範囲をパーティションで分けることができれば全表走査の範囲を1/10に減らすことができます。

OS/デバイスレイヤー

SQLの最適化やバッファ・キャッシュの最適化でも問題が解決されなかった場合は、I/Oシステムそのものの性能を疑ってみましょう。db file sequential read待機イベントの待機回数と待機時間を比べて平均待機時間が長ければ、遅いI/Oシステムが原因である可能性が高くなります。I/Oシステムの性能問題は非常に様々な状況で発生しますので要因を充分に調査する必要があります。

V$FILESTATビューを利用すればデータ・ファイル別にマルチ・ブロック読取りとシングル・ブロック読取りの活動性に関する情報を得ることができます。

SQL> select f.file#,
            f.name, 
            s.phyrds,
            s.phyblkrd,
            s.readtim,  -- 全体読み取り作業情報 
            s.singleblkrds,
            s.singleblkrdtim,  -- シングル・ブロック読取り
            (s.phyblkrd - s.singleblkrds) 
              as multiblkrd,   --マルチ・ブロック読取りの回数
            (s.readtim - s.singleblkrdtim) 
              as multiblkrdtim,  -- マルチ・ブロック読取りの時間
            round(s.singleblkrdtim/decode(s.singleblkrds,0,1,s.singleblkrds),3)
              as singeblk_avgtim, -- シングル・ブロック読取りの平均待機時間(cs)
            round((s.readtim-s.singleblkrdtim)/(s.phyblkrd-s.singleblkrds),3) 
              as multiblk_avgtim -- マルチ・ブロック読取りの平均待機時間(cs)
     from v$filestat s, v$datafile f 
     where s.file# = f.file#;

もし、特定ファイルで平均待機時間がかなり高い場合、該当ファイルが存在するI/Oシステムの性能を高めることで性能改善が可能です。マルチ・ブロック読取りの適切な平均待機時間については具体的な数値はありませんが、10ms程度の平均待機時間を維持するようにすることが一般的です。

豆知識

物理I/O分類

物理I/Oは、従来型パスI/Oとダイレクト・パスI/O に分けられます。従来型パスI/Oは一般的にバッファ・キャッシュを経由してブロックを読込む作業を意味します。ダイレクト・パスI/Oはデータ・ファイルにあるブロックがバッファ・キャッシュを経由しないでPGAであげます。 ダイレクト・パスI/Oが発生すると、I/O作業を行う前にチェックポイントが発生し、使用済バッファをデータ・ファイルに書き込みます。データ・ファイルとバッファ・キャッシュの内容を同期化した後でダイレクト・パスI/Oが行われます。

全索引スキャンVS高速全索引スキャン

索引検索で発生する性能問題は、広範囲の索引検索によるI/O発生量の増加に伴うケースがほとんどです。このように、広範囲の索引検索が発生する場合にはどうすれば良いでしょうか?

不可欠で多量の索引スキャンをしなければならない、かつソートの必要がない場合であれば、高速全索引スキャンを使ってシングル・ブロック読取りではなくマルチ・ブロック読取りで読込むようにするのが性能面で望ましいでしょう。全索引スキャンは、全表走査と同様にマルチ・ブロック読取りだと誤解されがちですが、全索引スキャンはシングル・ブロック読取りが発生し、I/Oの発生量を増加させる原因になるため、このような場合にはマルチ・ブロック読取りで実行される高速全索引スキャンを利用するようにしましょう。

高速全索引スキャンを制御するパラメータは、_FAST_FULL_SCAN_ENABLED=TRUEでデフォルトがTRUEです。

高速全索引スキャンで実行するヒントは、/*+ index_ffs(table_alias index_name)*/です。

DB_FILE_MULTIBLOCK_READ_COUNT(MBRC)の設定

高いMBRC値は、オプティマイザにより全表走査を選ぶように影響を与えます。MBRCの適切な値は、アプリケーション(DSSもしくはOLTP)環境によって異なります。高いMBRC値は、全表走査を素早く実行するため、DSS処理時に有利になります。 もし、データベースサーバーがDSSとOLTP、両方の処理を実行するのであれば、適切な値を見つける必要がります。デフォルトの設定値は、8ブロックで少々小さめです。

もし、全表走査が最善の方法である場合、システムで設定可能な最大値でオブジェクトをスキャンしたいと思うことでしょう。敢て小さな値で時間を無駄に使う必要はないからです。最大値を確認した後、大量の全表走査を実行するプロセスに該当の値を適用します。

MBRC値には限界があります。それはsstiomax、DB_BLOCK_SIZEおよびDB_BLOCK_BUFFERなどいくつかの要素によって左右されます。sstiomaxは、Oracleの内部的な限界値であり、これは読取りまたは書込み実行時、1回のI/Oで送信することができるデータ量を制限します。その値はOracleの内部で定義されていて、バージョンによって異なります。Oracleの初期バージョンでは128KBでしたが、Oracle8iからは1MBとなりました。DB_BLOCK_SIZEとMBRCを掛けた値は、sstiomaxを超えることはできません。また、MBRCはDB_BLOCK_BUFFERS/4の値より小さくしなければなりません。それにMBRC値は、Solarisのmaxphys、ファイルシステムのmaxcontigなどによっても制限されます。

ここでは、使用環境に合わせて容易に設定できる2つの方法を紹介することにします。

①MBRCの値を下の例のようにかなり大きな値に設定することです。それによりOracleがシステムで処理可能な最大値に設定します。その後全表走査を実行するSQLを実行し、V$SESSION_WAITビューを確認します。そうすると、db file scattered read待機イベントのP3パラメータの数値が現在のシステムでの最大値となります。

②10046トレースイベントを設定することです。この最大値はデータベースレベルで設定するよりは、全表走査の実行速度を早める必要があるセッションに対してだけ設定しましょう。

alter session set db_file_multiblock_read_count = 1000;
select /*+ full(a) */ count(*) from big_table a;

-- 下の例題は10046トレースファイルからの抜粋です。 
-- システムで設定することが最大 MBRC 値が 128 ブロックであることが分かります。
WAIT #1: nam='db file scattered read' ela= 17946 p1=6 p2=56617 p3=128
WAIT #1: nam='db file scattered read' ela= 21055 p1=6 p2=56745 p3=128
WAIT #1: nam='db file scattered read' ela= 17628 p1=6 p2=56873 p3=128
WAIT #1: nam='db file scattered read' ela= 29881 p1=6 p2=57001 p3=128
WAIT #1: nam='db file scattered read' ela= 33220 p1=6 p2=57129 p3=128
WAIT #1: nam='db file scattered read' ela= 33986 p1=6 p2=57257 p3=96
WAIT #1: nam='db file scattered read' ela= 46372 p1=6 p2=65577 p3=128
WAIT #1: nam='db file scattered read' ela= 33770 p1=6 p2=65705 p3=128
WAIT #1: nam='db file scattered read' ela= 41750 p1=6 p2=65833 p3=128
WAIT #1: nam='db file scattered read' ela= 34914 p1=6 p2=65961 p3=128
WAIT #1: nam='db file scattered read' ela= 33326 p1=6 p2=66089 p3=128

何故物理I/Oコストは高いのか?

多くのDBAはディスクI/Oのコストは高いという話を聞かされ、物理ディスクとI/O ザブシステムが重要であると認識してきました。勿論、ストレージレイヤーは一番遅いコンポーネントです。しかしこれが遅い全ての理由のではありません。他の原因はブロックをSGAにキャッシュする時にOracle内部で発生するためのものです。

ブロックをSGAにキャッシュする時には様々なことが発生します。ユーザ・プロセスは、まず空きバッファリストを検索します。もし、空きバッファが見つからなければ、ユーザー・プロセスはDBWRプロセスに空きバッファを作るように要求します。その後、ユーザー・プロセスは再び空きバッファを探します。そして空きバッファを見つけた後は、空きリスト・チェーンから該当するブロックを取り除いた後、該当のバッファをLRUリストの上位や中間に位置させます(これはOracle8iから採用された方式です)。その後該当のバッファヘッダーのポインタは適切に調整されます。少なくとも2個のポインタセットが変更する度にラッチを獲得しなければなりません。ブロックのヘッダー構造も初期化されて修正されなければなりません。バッファを割り当てたり、ブロックをバッファ・キャッシュにキャッシュしたり、ブロックをバッファ・キャッシュにキャッシュする作業が完了するまで他のプロセスが該当のブロックをアクセスしないようにブロックヘッダーの特定ビットを初期化しなければなりません。

結果的に、db file sequential readとdb file scattered read待機を解決する最善の方法は、メモリーI/OとディスクI/Oの要求を減らすことです。これはアプリケーションとSQLチューニングにより可能です。これでディスクI/Oのコストは高い、またメモリーI/Oのコストも高いということもご理解いただけたことでしょう。