門外不出のOracle現場ワザ
第１章 目からウロコのOracleパフォーマンス分析テクニック
日本オラクル株式会社 コンサルティング統括本部テクノロジーコンサルティング本部 小田 圭二(おだ けいじ)
目次
- Part1 間違いだらけのOracleパフォーマンス分析
- Part2 標準ツールでOracleの状態を正確に知る方法
- Part3 OSとI/Oはパフォーマンス低下にどう影響するか
Part1 間違いだらけのOracleパフォーマンス分析
待機イベントは「悪」か？
まず、世の中で広く信じられている次の説について考えてみたいと思います。
待機イベント注1の値が大きい＝DBチューニングの余地がある。
皆さんの中にも、このような説明を見た方が多いかもしれません。特にチューニング経験が豊富な方は、この説は大体正しいと感じると思います。私の感覚では10回中7回か8回程度正しいのではないでしょうか。
しかし、この説を信じてチューニングしても、思うように効果を得られなかったケースがあるはずです。
まず誤解を解いておきたいのは、「待機イベント＝“悪”」という図式です。待機イベントはある状態を示しているに過ぎません。そして、待機イベントはアプリケーションまで含めた、システムのアーキテクチャの観点から捉えるべきものです。
詳しい理由については後述します。ここは“急がば回れ”で、基礎知識を再確認するためにも、アーキテクチャの説明から始めます。
なお、本稿では、一時的な性能悪化を調査する方法など、ほかの書籍や記事では紹介されていないノウハウの解説を多くしました。また、内容をノウハウの紹介に集中しているため、コマンドは詳しく解説していません。コマンドについては、ほかの書籍や記事をご覧ください。また、話を複雑にしないため、マルチスレッドサーバー構成（MTS構成）は今回扱いません。記事中のスクリプトなどは、特に注意書きがない限り、Oracle9i R2で確認しています。
注1：待機イベントについては後ほど説明します。今は「待ち」のことだと思ってください。つまり、「待機イベントの値が大きい＝待ちが多い」ということです。
Oracleの動作モデルを描けますか？
チューニングの際、皆さんは「Oracleの仕組みはこうだから、こうすればいいだろう」と頭の中で考えていますよね。世の中 でよく言われるように、仕組みを図（イメージ）で書くことができれば、それを「理解している」と言って良いと思います。皆さんはOracleの動作モデルを図で描けますか？この先の解説を理解するためにも、ここで確認しておきましょう。
ただし、 Oracleのセミナー（研修）などで学んだ方もいらっしゃると思います。理解しているという方は図だけを見て、次節「システムのパフォーマンス測定方法とOracleの比較」に進んでください。
3つの基本モデル
まず、いちばん簡単なキャッシュに載っているデータをSELECTするだけのケースです（図1）。「Oracleクライアントプロセス」が、アプリケーションサーバー（以下、APサーバー）などのOracleから見たクライアントです。OracleクライアントからSQL文が送信され、サーバープロセスがそれを受信して、Oracleのバッファキャッシュにあるデータにアクセスし、SQL文の結果を作ります。
図1 キャッシュ上のデータをSELECTした場合の動き
次に、データがキャッシュにないケースを説明します（図2）。先ほどと異なるのは、SQL文の処理に必要なデータをディスクからサーバープロセスが読み込むことです。先ほどと同様にサーバープロセスがSQL文の処理を一手に引き受けています。
図2 ディスクからデータを読み込む場合の動き
最後に、データ更新（UPDATE）時の動作について説明します（図3）。すでにキャッシュにデータは載っているものとします。
図3 データ更新の動き
Oracleクライアントプロセスから見ると、サーバープロセスが処理を一手に引き受けており、主なプレーヤーであることは変わりありません。サーバープロセスがデータを変更した後、OracleクライアントプロセスへUPDATEが終了したことを送信します。それを確認して、OracleクライアントプロセスはCOMMITをサーバープロセスに送信します。
次に、サーバープロセスはログをログファイルに書きますが、自分自身では書くことができないため、ログライター（LGWR：ログを書き出す役目のプロセス）に書き出しを依頼します。サーバープロセスはログライターが書き終わるまで待機します。ログライターから書き終わりの通知がサーバープロセスに届くと、サーバープロセスはOracleクライアントプロセスにCOMMIT終了を通知します。
システムのパフォーマンス測定方法とOracleの比較
パフォーマンスの計測ポイント
システムのパフォーマンス測定では、タイムスタンプを主な処理ポイントで取得したりします。例えば、一般的なWebシステムでは、次のような計測ポイントがあるでしょう。
まず、Webブラウザから出たリクエストがAPサーバーにいつ到着し、さらにAPサーバーで処理されDBへSQL文を発行したのはいつで、DBからSQL文の結果が戻ってきたのはいつ、APサーバーからブラウザへの送信はいつ、といった具合です（図4）。このよう にタイムスタンプを記録しておけば、どこで時間がかかっているのかが一目瞭然です。
図4 システムにおけるパフォーマンス測定の例
実は、Oracle内部の動作に関しても、同様の図を書くことができます（図5）。
図5 筆者が考えるOracleの動作モデル
平均値／合計値しかとれないツールでは遅延原因を特定できない
しかし、広く使われている「STATSPACK」というパフォーマンス分析ツールを使っても、「SQLトレース（SQL文を調査するツール）」を使っても、Oracleのパフォーマンス情報はサマリーで表示されてしまうため、この図のような計測は行なうことができません。
このように情報をサマリーすることには、メリットとデメリットがあります。メリットとしては、莫大な詳細情報を見ずに（DBサーバー全体の）概要を把握できることがあります。特に、STATSPACKはそのような用途に向いています。SQLトレースはセッション（Oracleクライアントとサーバーの論理的な接続）単位であるものの、SQL文の情報をサマリーとして見ることができます。例えば、同じSQL文が1万回実行されても、情報としては数行から数十行にまとめてくれます。
サマリーのデメリットは、実行したまさにその時の状態を知ることができない点です。実際のシステムにおいては、ずっと同じ状態ということはありえません。例えば、10回に1回だけSQL文の処理が遅延したとします。その場合、STATSPACKのサマリーされた情報では平均値しか見ることができないため、遅延したかどうかすら分かりません。また、遅延していた時にSQL文がDBに届いていたのかも判別できません。SQLトレースもSQL文の情報は表示されますが、いつ実行されたかや、その時どんな待ちが発生したのかは分かりません。
誤解しないでいただきたいのは、サマリー自体が悪いわけではなく、大切なのは、サマリーをとるケースと、サマリー以外の情報をとるケースとを判断できることです。また、取得した情報の意味や特性を正しく理解することが重要です。
情報の意味や特性を説明する前に、次はパフォーマンスとは何かを正しく理解する上で大切な、Oracleのアーキテクチャや動作について解説します。
サーバープロセスとバックグラウンドプロセス
ここでは、STATSPACKやOracleに付属のツールでパフォーマンスデータを見る上で重要なキーワードである「サーバープロセス」と「バックグラウンドプロセス」について説明します。まずは、STATSPACKの出力結果（抜粋）を見てください（LIST1）。
LIST1 STATSPACKの出力結果（抜粋）
最初の2つの白抜きのところがサーバープロセス、最後の白抜きのところがバックグラウンドプロセスです。バックグラウンドプロセスの箇所には「Background」と書いてあるので、区別できます。バックグラウンドプロセスとは、サーバープロセスをサポートするプロセスと考えて構いません。サーバープロセスが表で活躍する人（プロセス）で、バックグラウンドプロセスが裏方という役割です（図6）。
図6 各プロセスの役割と動き
図1や図2と見比べてください。これらの図から分かるように、バックグラウンドプロセスの処理が進まなくても、サーバープロセスの処理さえ遅れなければSQL文は高速に処理されます。つまり、Oracleのパフォーマンスを確認する際は、 サーバープロセスの情報を見ればだいたいは十分です。バックグラウンドプロセスの情報は、バックグランドプロセスが原因でサーバープロセスを待たせてしまっている場合のみ見ればいいのです。
STATSPACKを使って得られる情報（LIST1の白抜きの箇所）は、存在するサーバープロセス（もしくはバックグラウンドプロセス）の合計となっています。そのため、1時間計測しただけなのに、データベースファイルの読み込み（ディスクの読み込み）時間が3時間といった数値を示すことがあります。この「 STATSPACKの情報は存在したプロセスの合計である」という点は非常に重要ですから、よく覚えておいてください。
待機イベントって本当は何なの？
「待機」とは、プロセス（セッション）がCPUを使わずに待っている状態のことです。「イベント」とは、直訳すると「出来事」ですから、I/Oやロック競合などのことを指します。誤解を恐れず簡単に言ってしまうと、「プロセスがCPUを使わずに待たなければならない出来事」が「待機イベント」です。
待機イベントと統計値の関係
「cpu used by this session」という統計値がSTATSPACKなどにありますが、これは全サーバープロセスが使用したCPUの合計時間です。動作モデルの図5を使い、「cpu used by this session」と待機イベントの関係を表わすと、図7のようになります。
図7 SQL文処理と待機イベント、CPU処理の関係
例えば、「SQL*Netmessage from client」というイベントがあります。これはDBにとってのクライアント（例えば、SQL*PlusやAPサーバー）からSQL文が来るのをサーバープロセスが待っている状態です。図7では、 ■線部分がSQL*Net message from clientを示しています。アイドル（何もしない）状態であることを意味しているため、SQL文の処理時間には含まれません。つまり、アイドルである待機イベントは通常、無視して構わないものです。
次に、「db file sequential read」というイベントについて説明しましょう。プロセスはデータベースファイルからランダムI/O 注2でデータ（ブロック）を読み込んでいて、完了するのを待っている状態です。図7では ■線部分が相当します。SQL文を処理するために必要なデータを読み込んでいるわけですから、SQL文の処理時間の中に含まれます。つまり、アイドルでない待機イベントは、注目に値すると言えます。
真の待機イベントとは
この待機イベントをチューニングする場合、DBだけを見て解決できることは少なく、視野を広くして「そもそもアプリケーションやSQL文を変更すれば、読み込みを減らせるんじゃないの？」といった発想も求められます。
OLTP系システムであって、パラレル処理をまったく行なっていなければ、図7から分かるように 「cpu used by this session」とアイドルではない待機イベントの合計が、ほぼSQL文の処理にかかった合計時間と言えます 注3。処理されたSQL文の数で割れば、ほぼ1つのSQL文の平均処理時間になります。
このように、待機イベントには 2 種類あり、アイドルの待機イベントは無視して問題なく、アイドルではない待機イベントと「cpu used by this session」の合計がほぼSQL文の処理にかかった合計時間であることを、ここでは理解してください。
注2：待機イベント名には「sequential（シーケンシャル）」と書いてありますが、実質的にはランダムI/Oです。 Oracleにおける、いわゆるシーケンシャル（順次）I/Oは「db file scattered read」です。
注3：後述するように、CPUリソースの不足時には当てはまりません。
世間で一般的なOracleパフォーマンス分析方法の問題点(1) 合計と平均のマジック
皆さんは、パフォーマンス情報の取得間隔をどれくらいにしていますか？STATSPACKによる取得（「スナップショット」と呼ばれる）間隔は1時間であったり、OS側のI/O情報も数十分間隔であったりするのではないでしょうか。そのような状況で数十秒から1分程度の遅延が発生したと仮定して、どのようなデータが現われるか考えてみましょう。遅延理由は、「ディスクからの読み込みが1分程度遅延した」とします。
I/Oが激しいDBであれば、遅延していなくても1時間に3000秒くらいの「db file sequential read（ディスクからのランダム読 み込み）」が計測されます。普段は20ミリ秒の読み込みが1回だけ1分間遅延したとしても、3000秒が3060秒にしか増えません。この程度の増加は、誤差や負荷のぶれ程度にしか見えません。さらに平均してしまえば、1回の読み込み時間は20.4ミリ秒です（図8）。OS側のI/O情報も、数十分間隔で取得していれば、同じようなデータになります。
このようなデータを基に「問題はない」と主張されているエンジニアも多くいらっしゃいますが、実際には1分間も遅延している場合だってあるのです。
図8 遅延なく読み込んでいる場合と1回だけ読み込みが1分間遅延した場合
「マジック」に惑わされるな
このように、データの取得スパンが問題時間に比べて長いため、合計や平均からは問題が見えにくくなる事象を、本特集では「合計と平均のマジック」と呼ぶことにします。もちろん、このような事象を切り分ける（見分ける）方法も、本特集の後半（ パート2、 パート3）で説明します。
なお、次節「 世間で一般的なOracleパフォーマンス分析方法の問題点(2)～瞬間的な待ち行列のマジック」と次々節「 世間で一般 的なOracleパフォーマンス分析方法の問題点(3)～計測ポイントのマジック」は内容が少し難しいので、読み飛ばしていただいても構いません。
世間で一般的なOracleパフォーマンス分析方法の問題点(2) 瞬間的な待ち行列のマジック
「待ち行列」と聞いて、教科書で習ったとか、資格試験のために数式を暗記したという方は多いと思います。DB エンジニアには関係ないと考えている方も多いと思いますが、実は真のパフォーマンスを見る際には、計算式こそ用いないものの、待ち行列の考え方や特性は大事な役割を果たします。
一般にはあまり知られていませんが、至るところに待ち行列は存在しています。例えば、enqueue待ち（表や行にかかったロックなどを待っている待機イベント）やコネクションプールにおける待ち（コネクション取得待ち）もそうです。ほかにも、CPU、ラッチ（Oracleの内部ロック）、ログライター（LGWR）、データベースライター（DBWR：Oracleのバッファのデータをデータベースファイルに書きこむプロセス）、ファイルシステム、デバイスドライバ、ディスク、ネットワークドライバなど、挙げればきりがありません 注4。
待ち行列で探るパフォーマンス劣化の原因
気が付いていないだけで、実際のシステムでも待ち行列状態になっているケースはとても多いのです。気が付いていない理由の1つに、待ち行列は「できては無くなり」を繰り返していることが挙げられます。また、待ち行列の存在や計測ポイントがあまり知られていないこともあり、性能が悪くても軽視されているようです。
例えば、次のようなケースを考えます。日中はOLTP処理により、均一的に多数の表が少しずつまんべんなく更新され、夜間はバッチ処理により1つの表の多数のデータが更新されるとします。Oracleの場合、データベースライター（DBWR）が書き込みを行ないます。日中は1度に1ブロックが、夜間は1度に10ブロックがデータベースファイルに書き出されるとします。両者の1ブロックあたりの書き込みにかかる時間（レスポンスタイム）はどれくらいでしょうか？
実際に1ブロック書き込むのに20ミリ秒かかるとして、日中は20ミリ秒、夜間は1ブロック当たり110ミリ秒かかります（数式、図9）。
夜間は、実際に書き込んでいる時間よりも待たされている時間のほうが長くなっています。しかし、このレスポンスタイムをもってして、ストレージの性能が悪いと言えるでしょうか 注5。
数式 夜間の1ブロック当たりのレスポンスタイム
図9 待ち行列によるレスポンス悪化
注5：このような動きをするため、私たちOracleコンサルタントは、書き込みのパフォーマンスは気にしないか、もしくはもっと正確に理解しようとします。
高負荷な状態のほうが処理が速い謎
このような現象は至るところで起きています。例えば、SolarisのI/Oの統計情報を無風状態時と負荷をかけた時で比較します。すると、なぜか無風状態時のほうがレスポンスタイムが悪いケースがよくあるのです。これも待ち行列の仕業です。これ以上の解説は本題から逸れるので、詳細を知りたい方はWebサイト「 Solving the iostat disk mystery」を参照してください。
話が長くなりましたが、真のパフォーマンスとは何かを理解するためには、待ち行列の特性を理解し、どこで待ち行列が発生しているのか突き止める必要性があるのです。この、短い時間で「できては無くなり」を繰り返してレスポンスを悪化させる現象を、本特集の中では「瞬間的な待ち行列のマジック」と呼ぶことにします。
世間で一般的なOracleパフォーマンス分析方法の問題点(3) 計測ポイントのマジック
本パートの冒頭で紹介したように、「STATSPACKの待機イベントの合計が大きい場合にはチューニングが必要、待機イベントの平均が大きければチューニングが必要」という世間一般の通説があります。果たして本当にそうなのでしょうか。次のような例を考えてみましょう。
セッション数が多くてもスループットは同じ
DB中にボトルネック（表のロック）が1箇所あり、すべての処理がそこで引っかかっているとします。セッション数が2の場合と、3の場合でどのような待機イベントの合計値になるか考えてみてください。
大雑把に言って、2の場合に比べ、3の場合には待機イベント（Oracleでは表のロック待ちは「enqueue（エンキュー）」と表示 されます）の合計値は2倍になります（図10）。表のロックを持っているセッションは処理をしているため、該当する1セッションでは、待機イベントは表示されません。しかし、残りのセッションはenqueue待ちをします。セッション数が2の場合には1つのセッションがenqueue待ちをしますが、セッション数が3の場合には2つのセッションがenqueue待ちをします。単純計算で2倍の 合計値になるわけです（図10の ■線の部分を足してみてください）。
しかし、これをもってセッション数が3の場合のほうがパフォーマンスが悪いとは言えません。なぜなら、スループットはセッション数2でも3でも同じだからです（図10の「結果の返信」を数えてみてください）。
図10 2セッションの場合と3セッションの場合との比較
ご存知の方も多いと思いますが、DBへの接続（コネクト）処理にかかるコストは低くありません。SQL文を実行する数十倍、数百倍といったCPU時間がかかります。また、ちょっと前のOracleのバージョンだと、この接続処理は多重で処理できず、ボトルネック（待ち行列）になりやすいのです。
アプリケーション開発者としては、コネクションプーリングを作ったり使ったりするより楽なので、SQL文を実行するたびに接続処理を行なうケースがあるようですが、このような理由により、それはできるだけ避けてください。これはパフォーマンス情報の取得でも同様です。本記事のスクリプトはDBへのコネクトが最小限になるよう作られています。
待ち時間が多くてもスループットは同じ？
次に、このロックを必要とする処理が1回あたり0.1秒かかるとして、待ち時間の平均を計算します。セッション数が2の場 合、enqueue待ちの平均は0.1秒で、セッション数が3の場合、0.2秒になります。テスト時のチューニングにおいて、セッション数が多すぎてパフォーマンスが劣化していると思い、セッション数を減らし、待機イベントが減ったと喜んでいるケースがありますが、図10のような状況では、スループットに変化はないのです。
これが、「待機イベントが大きい＝DBチューニングの余地がある」は、正しいとは限らないと書いた理由の1つです。
なお、SQL文のレスポンスタイムとしては、enqueue待ちに加え、処理にかかる0.1秒もあるため、セッション数が2の場合には、レスポンスタイム0.2秒、3の場合には、0.3秒となります。ここで、「レスポンスタイム0.2秒のほうが、0.3秒よりいいじゃないか」と一概に言えないことに注意してください。例えば、APサーバーのコネクションプールを使用している場合、セッション数（コネクション数）が2でも3でも、ユーザーから見たレスポンスタイムは変わりません。DBはシステムの一部である以上、上位（この場合はAPサーバー）からの処理要求を満たさなければ、いくらチューニングしたところで意味がないのです（図11）。
図11 待っている場所が違うだけで、問題は解決していない
このようにボトルネックがDBにある場合、本当のエンドユーザーから見ると、セッション数が少なくても多くても待たされる時間に変わりはありません。しかし、DB内の見た目（待機イベント）は変わります。よくよく考えてみると、計測ポイントによっては見えないことがあるだけです。このような計測ポイントによって見える現象が異なることを本特集の中では「計測ポイントのマジック」と呼ぶことにします。
なお、このマジックを見破るにはシステム構成を含むアーキテクチャを正しく理解するしか方法はありません。
以上、マジックと題してはまりやすい罠を3つ紹介しました。このように見ていくと、「待機イベントの合計数値が小さければ良い」とか「平均値が小さければ問題ない」と簡単に言えないことがお分かりいただけたと思います。
次の パート2では、 パート1で紹介したアークテクチャや理論を踏まえ、具体的なパフォーマンスチューニングの方法を、Oracleのツールやスクリプトとともに説明します。
Copyright © 2008, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。
Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。
小田 圭二(おだ けいじ)
1996年日本オラクル入社。人事教育本部にて、新卒や中途採用社員に対し、データベースやOS、ネットワークの講師を5年ほど経験した後、2000年にテクノロジーコンサルティング本部に異動。 テクノロジーのコンサルタントとして、主に大規模ミッションクリティカルシステムを担当。
ポリシーは、「OracleもOS上で動くアプリケーションにすぎない。だから、OS、ストレージ、ネットワークを学ぶべき」。 スキル面の興味は、アーキテクチャ、DBA、インフラ技術、教育、コンサル手法など。