db file sequential read

概要

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

Oracleでは 従来型パスI/Oが発生した場合、2つの待機イベントを通じて、マルチ・ブロック読取りが発生したのか、それともシングル・ブロック読取りが発生したのかを分かるようにしてあります。この2つの待機イベントがdb file sequential read/db file scattered readイベントです。これらの待機イベントはOracleで最も一般的に発生する待機イベントです。データ・ファイルからブロックを読込もうとするとマルチ・ブロック読取りやシングル・ブロック読取りを必ず実行するためです。

db file sequential read待機イベントはシングル・ブロック読み取り時に発生する待機イベントです。1回シングル・ブロック読取りが発生すると、 1回のdb file sequential readイベントが発生します。シングル・ブロック読取りはファイルから1つのブロックを読込む全ての作業で発生します。通常、索引スキャンやROWIDによる表スキャン、制御ファイル、ファイル・ヘッダーを読込む時に発生します。

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

待機パラメータ

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

  • P1 : ファイル番号
  • P2 : ブロック番号
  • P3 : ブロック数(ほとんどは1。Oracle7では 一時セグメントを読込む時、1以上の場合がある。しかしダイレクト・パス I/O機能が導入されてからは常に1となる。)

待機時間

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

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

OracleのI/Oレイヤー別db file sequential read解決策

SGAへデータをロードすることは極一般的な作業なので、db file sequential read待機は正常な動作です。db file sequential read待機によって性能問題が発生する場合は、ほとんどは非効率な索引スキャンや行連鎖、行移行によって無駄なI/Oが発生する時です。db file sequential readによる待機が索引を利用しているからといって、db file scattered readによる待機に比べたらそれほど深刻な問題ではないと思う傾向がありますが、索引を利用することが常に全表走査より良い方法ではないということは覚えておくべきです。

それでは、OracleのI/Oレイヤー別にdb file sequential readによる待機問題が発生するケースとその解決策について見て行きましょう。

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

非効率なSQL文や非効率な索引スキャンが頻繁に実行される場合、不要な物理I/Oによってdb file sequential read待機が増加します。選択効率が良くない索引の使用は、db file sequential read待機の主な原因となります。また、非効率な索引の使用はI/Oだけでなくバッファ・キャッシュの競合にもつながります。SQL文が最適化されていて、索引を効率よく使うことだけでもほとんどの問題を事前に防ぐことができます。

Oracleで利用可能な索引は、B索引、関数索引、ビットマップ索引、ドメインイン索引などに分けられます。それぞれの索引は特徴があるので、特定な状況においては性能改善に有効になります。これらの索引の仕組みを理解した上で索引を作成することが重要です。

統計情報を常に最新の状態にしておくことも大事です。DBMS_STATSパッケージを利用すればデータベース内のすべてのオブジェクトに対して最適な方法で統計情報を作成することができます。

たまに、SQL*Plusのような環境で実行した時には正常な索引を使っていたSQL文が、実際のアプリケーションで実行した時には誤った索引を使用し、db file sequential read待機によって性能が大きく低下してしまう場合があります。同じバインド変数を使っているにもかかわらずこのような問題が発生したら、バインド変数の先読み(bind peek) 機能の使用を確認する必要があります。 Oracle9iからはデフォルトでバインド変数の先読みが有効になっています。この機能が有効になるとバインド変数を使ったSQL文は、最初に実行された時の変数の値を利用して実行計画が立てられます。したがって、テスト環境で正常に動作したSQL文でも実運用の環境で実行される時は、最初にどんな値で実行されたかによって全く異なった実行計画になる場合があります。もし、このような現象が発生し、db file sequential read待機が増加するようであれば _OPTIM_PEEK_USER_BINDS隠しパラメータの値をFALSEに変更することを検討します。この値をFALSEと設定することによってバインド変数の先読みは使われなくなります。

Oracleメモリーレイヤー

バッファ・キャッシュの大きさがあまりにも小さすぎる場合には、慢性的に物理I/Oが発生し、これによりdb file sequential read待機が増加することになります。この場合、free buffer waits待機が同時に発生する確率も高くなります。free buffer waits待機が多い場合は、バッファ・キャッシュの拡張を検討します。また、マルチ・バッファを利用し、バッファ・キャッシュを効率的に使うことも検討します。 マルチ・バッファでdb file sequential read待機を減少させることは、db file scattered read待機を減少させることと同じ原理です。

索引が効率的に作成されたにもかかわらず、db file sequential read待機が思った以上に高い場合は、次のようなことを疑ってみる必要があります。

  • クラスタ化係数(Clustering Factor、以降CF)が高すぎないか?
  •  
  • 行連鎖や行移行が多発していないか?

CFは、索引の表に対するデータの集中度を表します。CFはメモリー上に一つのブロックだけをキャッシュできる領域があると仮定した場合、索引スキャンによって表を何回スキャンしなければならないのかを計算した値です。正確に言えば、索引のリーフ・ブロックに格納されているROWID値でブロック番号に当たる1~15番目の値が以前のROWIDと比べて変わる回数を表します。 この概念を理解するために一つの例を上げてみましょう。5個のブロックで構成されている索引と5個のブロックで構成されている表があります。1つのブロックにはそれぞれ4件のレコードが格納されているとします。従って全レコード数は 5×4 = 20レコードとなります。索引を順にスキャンし、索引につながっている表を読込んでくる場合、下記のような2つの極端なケースがありえます。

①CFが最も低い場合: 一つの索引ブロックに含まれた4レコードのROWIDが一つの表ブロックに全て含まれているとすると、索引を通じて表をスキャンする時、索引5回 + 表5回となり、合計10回のスキャンだけで該当するデータを得ることができます。この場合、CFは5(表ブロックのスキャン回数)になります。CFの最小値は表ブロック数と同じです。

②CFが最も高い場合: 一つの索引ブロックに含まれた4レコードのROWIDが全て別々の表ブロックだった場合、索引を通じて表をスキャンする時、5(索引ブロック数) + 5(索引ブロック数)×4(各索引ブロックごとにスキャンしなければならない表ブロック数) = 25回のスキャンで該当するデータを得ることができます。この場合、CFは20(表ブロックのスキャン回数)になります。CFの最大値はレコード件数と同じです。

もし、メモリーI/Oがないと仮定すると、CF値が高いほど表ブロックを読む回数が増加してこれにより物理I/Oが増加するようになります。つまり、CF値が高い索引により表ブロックを読込む回数が増え、それだけdb file sequential read待機が増加するようになります。実際にはバッファ・キャッシュの大きさが十分であれば、一度読込んだブロックは物理I/Oが発生しないため、CF値が高いといっても必ずしもSQL問合わせ時の性能が低下する訳ではありません。しかし、CF値が高い索引を広範囲にスキャンするようになると、その分読込まなければならない表ブロック数が増え、性能に致命的な影響を与える場合があります。

ANALYZE文やDBMS_STASパッケージを利用すると、索引のCF値を求めることができます。索引に対して統計情報を生成すると、DBA_INDEXES.CLUSTERING_FACTORにCF値が記録されます。もし、CF値が表のブロック数に近い値だった場合には良好で、レコード数に近い値だった場合には良くない徴兆となります。 SQL問合せの性能問題の原因がCFにあると判断された場合、索引スキャンの代わりに 全表走査を使用することが代案となります。もしくは、他の索引を利用することも検討します。これらの方法でも改善が見られない場合には、表を索引順で再作成することで解決できます。(CREATE TABLE NEW_TABLE AS SELECT …FROM OLD_TABLE ORDER BY INDEXED_COLUMNのようなSQL文を利用) しかし、表の再作成はあくまでも最後の選択肢とすべきです。

繰り返しになりますが、CF値が悪いとしても必ずしも性能が悪くなるということではないため、問題の原因を正確に把握することが非常に重要になります。また、ASSMのような管理方法を使う場合、以前と比較してCF値が高くなる傾向があるため、慎重に判断すべきです。

行連鎖/行移行

索引のROWIDで表をスキャンする際に、該当するレコードが行連鎖や行移行することによって追加のI/Oが発生します。これによりdb file sequential read待機が増加するようになります。ANALYZE文で統計情報を生成すると、DBA_TABLESのCHAIN_CNTカラムに行連鎖や行移行が発生したレコード数が記録されます。 V$SYSSTATビューや V$SESSTATビューを利用すれば間接的に行連鎖や行移行の発生を確認することができます。table fetch by rowid 統計値はROWIDを通じて表をスキャンした回数ですが、索引を経由して表をフェッチする場合に増加します。table fetch continued row統計値は行連鎖や行移行によって追加のフェッチが発生した回数です。行連鎖や行移行によってdb file sequential read待機が増加した場合には、該当の現象を解消することが解決策になります。

行連鎖はレコードの長さがブロックより大きい場合に発生します。従って、表定義を変更したりPCTFREE値を小さくしてから表を再作成するか、もしくはより大きいブロックを使うか、これ以外に行連鎖を回避する方法はありません。行連鎖に対してただエクスポート/インポートなどを利用して表を再構成しても意味はありません。また、PCTFREE値を小さくするとした場合、他の性能問題を引き起こす可能性があるため、慎重に考慮する必要があります。行連鎖が発生したとしても必ず追加のI/Oが発生する訳ではありません。SELECT文に記述された全てのカラムが最初にアクセスしたブロックの中にあれば一回のI/Oだけで結果を得ることができます。この場合、table fetch continued row統計値は増加しません。従って、SELECT文で不要なカラムはフェッチしないように習慣付けることをお勧めします。

  • エクスポート後インポートする。
  •  
  • alter table xxx moveを実行する。
  •  
  • analyze table xxx list chained rows into yyyy を実行して行移行が発行したレコードを抽出し、 該当のレコードに対してDELETE/INSERTを実行する。

上記の作業により行移行を取り除いたとしても問題の根本原因が解決されたことではないということに注意して下さい。アプリケーションのロジックが同じであれば時間の経過とともに全く同じ問題が再現されるからです。従って表の再構成のような一時的な対処よりは、PCTFREEの調整やアプリケーションの改修などの根本的な解決策を適用することを検討しましょう。

IOデバイスレイヤー

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,-- Single block IO 平均待機時間(cs) 

 round((s.readtim-s.singleblkrdtim)/(s.phyblkrd-s.singleblkrds),3) 

 as multiblk_avgtim -- Multi block IO 平均待機時間(cs) 

 from v$filestat s,v$datafile f 

 where s.file# = f.file#; 

もし、特定ファイルで平均待機時間がかなり高い場合、該当ファイルが存在するI/Oシステムの性能を上げることで性能改善が可能です。

豆知識

物理I/O 分類

物理I/O分類をご参照下さい。

行連鎖 VS 行移行

マルチ・ブロック読取りの場合には行連鎖と行移行のスキャン方法が異なります。マルチ・ブロック読取り処理時、行連鎖は追加のシングル・ブロック読取りが発生しますが、行移行はマルチ・ブロック読取り処理時には追加のシングル・ブロック読取りは発生しません。

大量のデータを処理するジョブを実行中のデータベースをモニタリングしてみると、db file scattered read待機が発生している間、db file sequential read待機が発生することも確認できます。これは全表走査を実行中の表に行連鎖があり、シングル・ブロック読取りが追加で発生していることを意味します。従って、該当の表の行連鎖の割合が表の全レコード数と比べて通常10%以上の場合には、行連鎖を取り除くことで表にアクセスするSQLの性能を改善することができます。

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

何故物理I/Oコストは高いのか?をご参照下さい

分析事例

クラスタ化係数の改善によるdb file sequential read待機の減少

 

db file sequential read待機イベント発生のシナリオは以下の通りです。

  • 160万件のレコードを持つt_db_file_sequential_read(id,name)表を作成します。レコード作成時、idカラムに対してランダムにデータが生成されるようにします。
  • t_db_file_sequential_read(id)に対してidx_db_file_sequential_read 索引を作成します。t_db_file_sequential_read.idカラムのデータはランダムに生成されている一方、idx_db_file_sequential_read索引はidカラムに対してソートされています。従ってCF値が非常に高くなります。
  • idx_db_file_sequential_read 索引を経由してt_db_file_sequential_read 表をスキャンするSQLを実行します。idx_db_file_sequential_read 索引のCF値が高いため多くの表データ・ブロックを読込まなければなりませんし、この過程で物理読取りが増加します。索引を経由した表ブロック・スキャンはシングル・ブロック読取りが発生し、db file sequential readイベントにより待機することになります。
  

上記のシナリオを図で表すと以下のようになります。

シナリオ

SQLを実行した結果は下図のようになります。db file sequential read待機イベントがかなり多く発生していることが分かります。

シナリオ2

MaxGaugeの「セッション・リスト」では下図のように表示されます。あるアクティブセッションがdb file sequential read待機イベントで待機していることが確認できます。

マックスゲージ画面

該当のセッション(SID=125)を確認した結果は下図のようになります。SQL実行中ほとんどの時間をdb file sequential read待機イベントで待機していることが分かります。

マックスゲージ画面

上記のような状況でdb file sequential read待機イベントが発生する理由は次の通りです。

  • セッションは索引を経由してROWIDに該当するデータ・ブロックを読込みます。
  • 索引のCFが良くない、つまり表ブロックが索引の順序とは関係なくランダムに散らばっているため、連続して同じデータ・ブロックを読込むことができず、毎回他のデータ・ブロックを読込む作業が頻繁に発生します。
  • この過程で大量の表ブロックを読込むようになり、以前に読込だ表ブロックがメモリー(バッファ・キャッシュ)から溢れ、追加の物理I/Oが発生するようになります。これによりdb file sequential read待機イベントが増加します。

このようなケースで物理読取りが増加する場合は、CF値を改善させることで解決できます。CF値を改善させる一番良い方法は、表を索引の並び順に再構成することです。以下のような方法により再構成することができます。

CREATE TABLE t_db_file_sequential_read 
AS SELECT id,name FROM t_old_db ORDER BY id;   ---IDカラム順にソート

CREATE INDEX idx_db_file_sequential_read 
ON t_db_file_sequential_read(id);

表を索引の並び順で再構成した後のシナリオは下図のようになります。

シナリオ2

性能改善後のモニタリング結果は下図の通りです。db file sequential read待機イベントの待機時間が31(cs)で、性能改善の前が3772(cs)だったのに比べれば1/100以上大幅減ったことが確認できます。

Sequential06

過度なシングル・ブロック読取りによる性能低下分析事例

選択効率の悪い索引を利用した過度なシングル・ブロック読取り要求はシステムの性能を低下させる主な要因です。Oracle DBMSの性能診断/分析ツールのMaxGaugeを利用して、過度なシングル・ブロック読取り要求による性能低下問題の原因を究明してみましょう。

性能低下区間の確認

MaxGaugeの画面で8時30分ごろから「CPU」使用率と「Active Session」数、待機時間が大きく増加していることが確認できます。

■「CPU」使用率の推移グラフ

マックスゲージ画面

■「Active Session」の推移グラフ

マックスゲージ画面

■「Wait Events」の推移グラフ

マックスゲージ画面

待機イベントの検出および確認

問題区間の待機イベントリストは下図の通りです。db file sequential read待機イベントが多く発生していることが確認できます。

マックスゲージ画面

待機イベント発生原因の調査

db file sequential read待機イベントの待機時間とphysical reads統計値を比較したものが下図になります。2つの値はほとんど同じパターンで推移しています。これはシングル・ブロック読取りによる物理I/Oの増加が性能低下現象の根本原因であることを表しています。

マックスゲージ画面

セッションおよびSQL分析による問題原因の究明

下図は、 MaxGaugeの「SQLリスト」 画面を利用して、問題区間で物理I/Oを実行したSQL文を抽出した結果になります。1つのSQL文がほとんどの物理I/Oを実行していることが確認できます。つまり、このSQL文が性能低下の原因になります。該当するSQL文を適切にチューニングし、シングル・ブロック読取りを減らせば性能低下現象は解消されます。

マックスゲージ画面

結論

性能低下現象(db file sequential read待機イベント)

          ↓

非効率な SQLによる過度なシングル・ブロック読取り

          ↓

SQLチューニングによるI/O要求の削減

日中慢性的に発生するdb file sequential readの分析

監視対象のインスタンスでdb file sequential read待機の待機時間がもっとも多い状況です。

マックスゲージ画面

db file sequential read待機イベントはログを取った区間で平均6secs/sec程度発生しています。

マックスゲージ画面

db file sequential read待機イベントは日中慢性的に発生しています。

マックスゲージ画面

問題のインスタンスのシングル・ブロック読取りに対するディスクI/Oパフォーマンスは次のように測定することができます。

single block read performance
  =24時間のsingle block read合計待機時間/24時間のsingle block read合計発生回数
  =time waited (diff) / total waits(diff)
  =536,712 / 211,805,699
  =0.0025 sec
    1つのシングル・ブロック読取り待機時間は 0.0025秒(一般的な平均は 0.01~0.004秒)

シングル・ブロック読取りのディスクI/O性能は 0.0025秒と平均以上なので、特に問題はありません。

チューニングによりシングル・ブロック読取りを要求する絶対量を減らす方法としては、以下のアクションが考えられます。

1)SQLチューニングによるブロックI/O量の削減
MaxGaugeの「SQLリスト」や「待機SQLリスト」で、該当の待機イベントの待機時間が長いSQL順にチューニングを検討します。

マックスゲージ画面

2)件数と比較してBLEVEL(Bツリーの高さ)が大きい索引のチェック
BLEVEL3以上の索引の中で、件数が1000万件以下のものを主にチェックします。

- DBA_INDEXES.NUM_ROWSとDBA_INDEXES.BLEVELをチェック

3)行連鎖のチェック
table fetch by continued row指標で確認を行います。

マックスゲージ画面
平均100件/秒程度発生しているので、行連鎖が多く発生している表をチェックした後に
行連鎖を解消する必要になります。 
(チェック方法: DBA_TABLES.CHAIN_CNT <--該当の情報はDBMS_STATS実行時には生成
されず、Analyze実行時のみ生成されます)