門外不出のOracle現場ワザ

第１章　目からウロコのOracleパフォーマンス分析テクニック

日本オラクル株式会社コンサルティング統括本部テクノロジーコンサルティング本部小田圭二(おだけいじ)

Part3　OSとI/Oはパフォーマンス低下にどう影響するか

OracleとOS・I/O・ネットワークの関係

今まではDB内の動作だけを分析対象としていましたが、OSやI/OやネットワークとOracleとの関係はどうなのでしょうか？I/Oに関しては想像のつく方も多いと想いますが、OSやネットワークとなると細かいところまでは分からないという方がいらっしゃるかもしれませんね。

ディスクアクセスまで含めたOracleの動作モデル

パート1の冒頭で示したOracleの動作モデル図をディスクアクセスまで含めて描くと、図1のようになります。

図1 ディスクも含めた動作モデル

図1では、Oracleクライアント群からディスク群まで処理が2往復しています。最初の往復はバックグラウンドプロセスからディスクへアクセスしていますから、ログライター（LGWR）が働くコミット処理などのケースを表わしています。一方、後の往復はサーバープロセスが直接ディスクへアクセスしていますから、「db file sequetial read」などの処理に相当します。

また、図1の後の処理では、ディスクアクセスが複数回発生しています。これは、SQL文の処理をする際、一度のディスクアクセスで結果が求められるとは限らないことを表わしています。何十万回とアクセスしなければならないケースもあります。そのような場合には当然、処理に時間がかかります。

なお、今回ディスクをこのようにモデル化しているのは、RDBMS側の都合によるものです。ディスク（ストレージサブシステム）のアーキテクチャや動作は実際にはかなり複雑で、正確にパフォーマンスを見るにはこの図では不足です。

ネットワークまで含めたOracleの動作モデル

さて、ネットワーク（Oracleクライアントが別マシンにあるケース）を動作モデル図に加えると、どうなるのでしょうか。実は、ネットワークに相当する部分はすでに図に含まれています（図2）。

図2 ネットワークまで図示した動作モデル

図2の最初の処理では、1回のパケットの往復でSQL文のリクエストから結果の戻しまでが行なわれています。実際には、動作はそんなにシンプルではなく、TCP/IPの制御パケットや、SQL文のパース（解析）、複数回のフェッチ（データの取り出し）など、いくつかのパケットが飛びます。興味のある方は、パケットをキャプチャして確認してみてください。

現場において、ネットワークを意識しなければならない場合とは、主に以下の3つです。

取得データ件数（SQL文の結果の件数）が多い場合
WAN継由の場合
ネットワーク障害の場合

データ件数が多い場合には、図2の後の処理のように、結果をクライアントに送るためだけに何度もパケットを送信しなければなりません。最近よく使われるLOB（ラージオブジェクト）も、サイズが大きく複数パケットに分割されるため、これに該当します。

WAN継由の場合は、パケットが届くのに時間がかかるため、チューニングが必要になるかもしれません。ネットワーク障害の場合にもパケットの送受信に時間がかかるため、DBサーバーのパフォーマンス悪化のように見えることがあります。

OSまで含めたOracleの動作モデル

ではネットワークに続き、OSも動作モデル図に加えて……、と行きたいところですが、残念ながらOSは今までのような図にはできません。あえて図にすると図3のようになります。

図3 動作モデルにおけるOSの影響

Oracleのプロセス群はOS上で動いているため、OSで何か問題が起きると、その分余計に処理の時間がかかります。図3で言えば、到着したある矢印からほかへ向かう矢印までの間隔が長くなります（詳しくは後述）。

OSの稼動状態はOracleに大きく影響する

RDBMSは、一般にミドルウェアとして扱われます。しかし、OSから見ればただのアプリケーションにすぎません。RDBMSだってプロセスやスレッドで構成されていて、CPU上で実行され、システムコールも多数使っています。何を言いたいのかというと、「RDBMSはOSから大きく影響を受ける」ということです。

大きく影響を受けるといっても、パフォーマンスの観点で影響があるのは、CPUリソース不足でしょう。それ以外に関してはあまりお目にかかりませんし、本特集の範囲を越えるので、ここでは説明をCPUリソース不足だけに絞ります。

CPUリソース不足が原因のこともある

ご存知の方も多いと思いますが、まず簡単にOS上のプロセスの「ステータス」について説明します。“O”と“R”と“S”のステータスさえ理解していれば、ここでは十分です。これらを図示すると図4のようになります。

図4 プロセスのステータス

I/O中や自らSleep（実行を休止）した場合には、ステータスは“S”になります。ステータス“R”は、実行可能状態で、CPUをOSに割り当ててもらうのを待っている状態です。「runキュー注1に入っている」とも言います。ステータス“O”はプロセスがCPUを使用して処理を実行中という意味です。

注1：実行すべきプロセスを溜め、先頭から順に実行していくOSの機能。OSによっては「実行キュー」「プライオリティキュー」などと言います。本特集では「runキュー」と呼ぶことにします。

RDBMSにとって一番大事なOSの待ち行列といえば、I/Oのキューと、CPUの割り当て待ちであるこのrunキューです。runキューのイメージは図5のようになります。

図5 runキューのイメージ

このrunキューが常時長いとCPUリソースが不足していると言われるわけですが、このCPUリソース不足はRDBMSに図6のような影響を及ぼします。

図6 CPUリソース不足がRDBMSに与える影響

ここで注目したいのは、CPUリソース不足により、結果の返信までの時間が伸びることと、待機イベントの時間が大きくなることです。待機イベントを計測しているOracle（のプロセス）自体がCPUリソース不足により動けないわけですから、大きな値になってしまっても仕方ありません。待機イベントが大きくなったからといって、単純に「DB（もしくはSQL）チューニングが必要だ」と思わないでください。これも、パート1の冒頭で「待機イベントが大きければDBチューニングとは限らない」と書いた理由の1つです。

runキューのデータの見方

Oracleからは少し外れますが、Oracleのパフォーマンスにも大きく関わってきますので、あえてrunキューのデータの見方を説明します。ここではvmstatコマンド注2を例にとります。

注2：仮想メモリの統計情報を報告するコマンド。

LIST1は、Solaris上でvmstatコマンドを実行した結果の例です。“r”と表示されている列が、runキューの平均長です。この“r”の数がCPU数と同じ場合、実行されているプロセスと実行待ちのプロセスはだいたい同数で、実行時間と同じくらいCPU待ちの時間があります注3。

LIST1 Solarisのvmstatコマンドの実行例

注3：あくまで目安程度に考えてください。

▲ ページTOPに戻る

低使用率でもCPUがボトルネックのケース

DBに関するコンサルティングをしていると、一時的なCPUリソース不足の事例に多数遭遇します。CPUの性能監視をしていないケースは論外として、システムによっては性能監視をしていても一時的なCPU不足に気付かないことがあります。特に、OSの情報取得間隔を数分から数十分に設定していると、パート1で述べた「合計と平均のマジック」にはまります。

例えば、1分間隔でvmstatコマンドを実行し、OSの情報をとっているとします。前半の30秒間はCPUリソース不足でrunキューがCPUの枚数以上になり、後半の30秒はまったくCPUを使っていなかったとしましょう。このような状況では、CPU使用率は50％程度ですが、いままで15秒だった処理が30秒かかってしまっているかもしれません。OSの稼動状態を詳しく見てみると、実はこのように処理が均一でないことが多いです。
こうした現象があることを念頭に置いてOSを監視していないと、DBが遅延している原因を発見できないこともあります。

ただし、一時的なCPUリソース不足であればすぐにCPUパワーを増強すべきとは言い切れません。一時的なCPUリソース不足は、CPUを追加しても投資対効果が得られないからです。レスポンス悪化の原因がCPUリソース不足であることをきちんと理解し、それが許容できないのであれば対処するようにしましょう。

最後に、このようなCPUリソース不足による遅延を検出する方法をお教えします。可能であれば、検出したい遅延時間より短い間隔で情報を取得します。筆者の知る限り、vmstatコマンドなら10秒間隔で実行しても負荷はかなり小さいです。本番サーバーで10秒に設定するかどうかはともかく、テスト時であれば可能なはずです。

▲ ページTOPに戻る

CPUリソースを大量消費するSQL文

単純にCPUパワーが足りないだけでなく、SQL文の実行計画が悪いためにCPU時間を多量に使っていることもあります。1処理当たりに換算してもOracleがCPU時間を大量に消費している場合、たいていはアクセスしているブロックが多すぎています。ただし、データベース設計が悪ければ、実行計画を改善してもCPU消費量を抑えることはできません。

LIST2は、CPUリソースを大量に消費するSQL文を実行したときの統計例です。STATSPACKのSQL文情報から抜粋しました。インデックスが非効率なため、大量のブロック（9851ブロック）にアクセスしないと結果が求められなくなっています。大量のブロックにアクセスするということは、CPU時間も多く使用しています（1SQL実行あたり1秒強）。

また、この例では「Elapsed time」と「CPU Time」に差がありますが、実は待機イベントはありません。Elapsed timeとCPU Timeの差分は、OSのrunキューで待たされた時間を表わします。

LIST2 CPUリソースを大量に消費するSQL文を実行したときの統計例

▲ ページTOPに戻る

oraperf.com

元米オラクル社員である、有名なエンジニアが作った Webサイトを紹介します。このサイトに任せれば、データベースをかじった程度の知識しかないDBエンジニアが分析するより良いパフォーマンス分析レポートを自動的に作ってくれます。このパフォーマンス分析レポートでも、本特集と同様、CPU時間とアイドルではない待機イベントの時間に注目しています。効果予測とともに、チューニングのアドバイスも表示します。

なお、完全な分析レポートを見るには登録が必要ですし、そもそも実際に利用する際には、分析したいデータ（お客様もしくは自社のSTATSPACKレポートなど）をこのサイトにアップロードしなければならず、セキュリティ面で認められないケースもあるはずです。ご利用には十分注意の上、自己責任でお願いします。

データベースの性能とディスクI/O

RDBMSはディスクからデータを読み込み、処理を行ないます。また、コミット時にはデータの整合性保証のため、ディスクに変更を書き込まなければなりません。キャッシュなどをうまく使い、I/O を減らす（パフォーマンスを維持する）ということまで含めて、データベースはI/Oが命です。

特に最近は1つのディスクのサイズが大きくなり、データベースのために使われるディスクの本数は少なくなってきています。しかし、ディスクの I O P S（1秒あたりのI/O回数）はそれほど向上していません。つまり、RDBMSから見ると、物理ディスクのI/O性能はほとんど上がっていないのです。それどころか、ディスクの本数が減ると並行アクセスによるI/O性能の向上が図れなくなり、サイズ当たりのI/O性能をかえって落としかねません。

ストレージのキャッシュ技術の向上はあるものの、我々DBコンサルタントとしては悩ましい状況になっています。

I/Oの遅延によりOracleが待たされるケース

I/Oの遅延には、明らかにディスクの性能不足によるケースと、障害によるケースの2つがあります。それによりOracleが受ける影響は以下の3点です。

サーバープロセスの読み込みが待たされる
ログライター（LGWR）の書き込みが待たされる
データベースライター（DBWR）の書き込みが待たされる

サーバープロセスの読み込みが待たされると、データファイルからの読み込みが遅延し、待機イベント「db file sequentialread（ランダムアクセス）」もしくは「db filescattered read（シーケンシャルアクセス）」の時間が伸びます（図7）。

図7 データファイルからの読み込みが遅延した場合

ログライターの書き込みが待たされると、ログの書き込みが遅延し、バックグラウンドプロセスの待機イベント「log fileparallel write（ログファイルへログを書き込んでいる）」の時間が伸び、サーバープロセスのコミット待ちイベントである「logfile sync（LGWRへ書き出しを依頼して返事待ち）」の時間が伸びます（図8）。

図8 ログの書き込みが遅延した場合

なお、通常はバックグラウンドプロセスの情報を見る必要はないと先に述べましたが、このようなケースでは、サーバープロセスがバックグラウンドプロセスを待っている状態なので、例外的に見ます。

最後に、データベースライターの書き込みが待たされるケースです。Oracleは変更済みデータを、SQL文の処理とは非同期にデータベースファイルに書き出します。しかし、書き出しが間に合わないとOracleのバッファキャッシュが枯渇し、サーバープロセスが待機イベント「freebuffer wait（バッファに空き領域がないため、空くのを待機）」で待つことがあります（図9）。

図9 データファイルへの書き込みが遅延

なお、データベースライターの性能の見方は待ち行列が複雑に絡んで難しいため、本特集では割愛します。現在のSTATSPACKでもデータベースファイルからの平均読み込み時間は記載されていますが、平均書き込み時間は載っていません。

▲ ページTOPに戻る

I/O性能の計測方法

I/Oの性能は、OSの性能と異なりOracleの待機イベントからでも計測できます。

時折待たされる場合の計測方法

まず、時折しばらく（数秒以上）待たされるケースから説明します。このケースでは、V$SESSION_WAITビューを参照します。「db file sequential read」や「dbfile scattered read」の待機時間が長ければ（「SECONDS_IN_WAIT」の値が大きければ）、データファイルからの読み込みが遅れていることは明白です注4。

なお、「時折しばらく（数秒以上）待たされるケース」は、STATSPACKやsarコマンド注5で確認しようとしても困難です。前述した「合計と平均のマジック」により、情報が埋もれてしまうからです。

注4：「log file parallel write」および、9.2.0.3までのOracle9i R2における「db file sequential read」と「db file scattered read」は、同じイベントがV$SESSION_WAITに繰り返し表示されていて、表示される引数（P1、P2、P3）が同じであることから確認してください。

注5：UNIX系OSでシステム状況を報告するコマンド。

性能不足のディスクを計測する方法

次に、ディスクの性能不足について説明します。ランダムI/Oであれば、ディスクアクセスにかかる時間は約20ミリ秒程度と言われます。しかし、遅延すると100ミリ秒といった数値になります。時折起きる遅延とは異なり、こちらはSTATSPACKやsarコマンドなどから確認するしかありません。しかも、この数値の判定が簡単ではありません。理由の1つは、I/Oの処理が図10のような階層構造になっているためです。

図10 I/Oの階層

図10が示すように、読み込みといっても数値の異なるI/Oのミックスになります。そのため、しきい値は一概に言えません。具体例は「I/Oデータの例」節でお見せします。逆に、データベースの下位レイヤであるストレージは、上位であるRDBMSからのI/Oをさばくことが仕事なので、上位のRDBMSが困らない性能を備えていれば十分と言えるでしょう。

OSのコマンドによるシステムの計測

OSのコマンドによるシステム性能の計測方法も、簡単に説明しましょう。OS によってコマンドやオプション、得られるデータ項目が異なるために一概には言えませんが、I/O回数、レスポンスタイム、ビジー率（I/Oが処理中である時間がどれくらいか）ぐらいは確認できます。

LIST3は、Solarisの「iostat -x」コマンドの実行サンプルです。I/Oの待ち行列も見ることができて、とても重宝します。以前は、性能の限界をビジー率で判断していました。しかし、最近のディスクはいろいろな理由でビジー率が当てにならなくなってきているので、「レスポンスタイムが低負荷時に比べて大きく悪くなっていいか確認する」といった判定方法がお勧めです。

waitキューの見方はCPUのrunキューと同じです。ビジー率が低くても、waitキューが長いケースがあります。

LIST3 Solarisの「iostat -x」コマンドの実行サンプル

Oracle 10gが持つパフォーマンス分析の便利な機能

Oracle 10gになり、パフォーマンス面でも多くの機能が追加されています。ここでは、目立たないけれどもとても役立つ新機能を紹介します。本特集で説明したパフォーマンス分析が、さらに実践しやすくなっています。

STATSPACKのレポートが拡張された
- 待機イベントが待ち時間別でも分かるようになりました。これにより、「合計と平均のマジック」を見破りやすくなりました。
- 一部の待機イベントが分かりやすくなりました。例えば、Oracle9i R2までは行ロックは「enqueue」と表示されていて、行ロックなのかどうか一目では分かりませんでしたが、Oracle 10gでは行ロックの競合であれば、「enq: TX - row lock contention」と表示されます。
V$SESSIONビューが拡張され、V$SESSION_WAITビューの情報も含むようになった
- V$SESSIONビューとV$SESSION_WAITビューを結合したり別々に取得しなくても、瞬間の情報を一度に取得可能になりました。
V$ACTIVE_SESSION_HISTORYビューが新設された
- このビューは、直近の各セッションの待機イベントをすべて表示できます。今まで、ある時点の待機イベントを確認するために、V$SESSION_WAITビューを使って定期的にセッションごとの待機イベントを保存していましたが、このビューをうまく使えばそれが不要にできそうです。

▲ ページTOPに戻る

wait I/Oが大きい＝I/Oボトルネック？

世の中には「wait I/O（vmstatなどで表示されるCPUのI/O待ち）」が大きいとI/Oがボトルネックであり、これ以上負荷を増やしても、処理量は増えないという見方があります。OSのエンジニアがよく使っている見方ですが、実はこれがRDBMSには当てはまらないのです。

前述したようにRDBMSはI/Oが命です。逆に言えば、I/Oがとても多いのです。Oracleでは、できる限りI/O待ちがほかの処理（ほかのサーバープロセス）に影響を与えないように工夫されています。実際、筆者はSYSとUSRの合計が50％くらいで、残りほとんどがwait I/Oのシステム（掲題の見方に従えば、ボトルネックになっているはず）において、さらに負荷が増えても問題なくSYSとUSRが増えた（つまりボトルネックは無かった）という経験をしたことがあります。

TIPS パフォーマンス情報の取得は日頃から

システムである以上、トラブルは必ず起こります。エンジニアとして差が出るのは、トラブルが起きた後でしょう。しかし、「情報がないので原因が分からない」というエンジニアは多くいます。結果として特定できるかどうかはともあれ、原因追求のための情報はとっておくべきだと思います。特に、パフォーマンストラブルの対処に必要な情報は、ユーザーが設定しないと収集できないものばかりです。自動的に常時情報をとっておき、トラブルが起きたときに確認するのです。例えば、次のような取得方法もあります。

STATSPACK：レベル7、30分間隔、1日24時間

V$SESSION_WAIT：5秒間隔、1日24時間

vmstatなどのOS情報：1分間隔、1日24時間

I/Oの情報（iostat、sarなどのいずれか）：1分

間隔、1日24時間

注意：取得の負荷に関しては十分テストして確認してください。システムテスト時からこれらの取得も設定しておけばベストです。本番に入ってから情報取得の設定をすると負荷の確認作業が面倒ですが、システムテストから導入しておけば、特に問題になることはありません。

ログの保持期間は1週間程度で十分です。1週間を過ぎたら自動的に消去されるようにしておきましょう。得られたログは毎日見る必要はありません。トラブルが起きた後に確認します。

なお、日ごろからパフォーマンス情報をとっておくことは、問題が起きたときの比較対象（ベースライン）を確保する意味でも重要です。

▲ ページTOPに戻る

I/Oデータの例

I/Oにおいて、キャッシュの効果がどれほど大きなものかを簡単なテストで計測してみました。今回はSTATSPACKを用いてI/Oの時間を測っています。テストで実行したSQL文は、インデックスを使って1件だけデータを取ってくるシンプルなものです。それをOS起動直後に計測した場合と、Oracleのみを再起動した直後に計測しました。

LIST4がその結果です。OS起動直後は、OSのバッファキャッシュもOracleのバッファキャッシュも空です。それに対して、Oracleのみを再起動した場合は、OSのバッファキャッシュにはデータが載っていることが分かります。

LIST4 キャッシュによるI/O性能の変化

▲ ページTOPに戻る

少し難しいI/Oパフォーマンストラブル

ここでは、少し難易度の高いI/Oパフォーマンスの問題を説明します。読み飛ばしていただいても構いません。なお、「瞬間的な待ち行列のマジック」と「計測ポイントのマジック」を読み飛ばした方は、まずそちらをごらんください。

I/Oにおいて、特に注意しなければならないのが並列度です。Oracleに限らず、処理の並列度が高く「瞬間的な待ち行列のマジック」になって、レスポンスタイムが悪化していることがあります。逆に、「計測ポイントのマジック」により、RDBMSは困っているのに、I/Oのデータからは問題がない（レスポンスタイムは良い）ように見えることもあります。これらのようなトラブルになるとRDBMSとI/Oの双方の内部構造について詳しいエンジニアでないと解決は難しいでしょう。

また、OracleからでもI/Oの遅延はある程度分かるのに、なぜI/Oの情報をOS側でもとるのか、その理由を説明しておきましょう。1つは、Oracleとストレージの間のどこかで遅延することがあるためです。このOracleとストレージの間に原因があるケースも厄介です。もう1つの理由は（筆者だけかもしれませんが）、Oracleの情報だけだと、OSもしくはストレージに遅延の原因があっても、担当者が素直に認めてくれないケースがあるためです。

▲ ページTOPに戻る

おわりに

Oracleを適切に使えば、ブラックボックスになりがちなDBのパフォーマンスもかなりクリアになることが分かっていただけたと思います。なお、今回は一時的なパフォーマンス悪化も扱ったため、V$SESSIONビューやV$SESSION_WAITビューの説明が多くなっていますが、各種ツールも適材適所で使ってください。

最後にここまでの解説のまとめとして、パフォーマンストラブル分析の流れ（サンプル）をチャートにしました（図11）。これが唯一の正解ではありませんし、省略して書いてはありますが、「状況に応じていろいろな計測の仕方があることを理解できる」という意味で皆さんの参考になると思います。

図11 パフォーマンストラブル分析のチャート（拡大）

繰り返しになりますが、I/OやOS、ネットワークとの関わりも含めてアーキテクチャを正しく理解し、発生している問題のタイプに合わせて最適な方法（ツール）で切り分けを行なうことが、パフォーマンスの管理では大事です。

実際にはもっと高度な技術も存在しますが、それらも本記事の内容がベースとなっていますし、基本的な考え方は変わりません。また、今回はどうチューニングするかは触れていませんが、チューニングを検討する際にもDB以外を含めた広い視野で考えてみてください。きっと今までより良い方法が見つかるはずです。

▲ ページTOPに戻る

"門外不出のOracle現場ワザ" インデックスに戻る

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。