Oracle Databaseのパフォーマンスダウン発生に備えたV$ビューの定期取得と調査方法

データベースに一時的なパフォーマンスダウンが発生した場合、調査のためにはパフォーマンスダウンが発生していた当時、つまり、過去のデータベースの情報が必要です。本稿ではパフォーマンスダウンに備えた動的パフォーマンスビュー（V$ビュー）の定期取得方法と、ビューからの調査方法を実際のサポート事例と併せてご紹介します。

昨今、企業のビジネスの多くはITで支えられており、データベースの性能低下はビジネスに直接影響を与えます。例えば、ECサイトのバックエンドで稼働するデータベース性能の低下は、ユーザが操作するWeb画面の応答時間が長くなることを意味します。応答時間が2秒以上になると直帰率が上がるとも言われており、データベースのパフォーマンスダウンは売上機会の損失につながる可能性があります。

アシストのサポートセンターには「処理が遅くなってしまいセッションが滞留した」「本来数分で終わるべき処理が1時間以上かかっても終わらなかった」などのパフォーマンスダウンに関するお問い合わせをいただくことがあります。

データベースのパフォーマンスダウンは主にメモリ、CPU、I/Oなどのハードウェアのリソース競合か、ロック、ラッチなどのデータベースのリソース競合で起こります。

具体的に何が原因で処理のパフォーマンスダウンが発生してしまっているのかを確認する情報として、Oracle Databaseでは動的パフォーマンスビュー（V$ビュー）があります。

V$ビューはデータベースがオープンしている間、セッションやメモリ、ロックの獲得状況といった内部的な情報が継続的に更新される、参照専用の特別なビューです。

今まさにパフォーマンスダウンが発生中であれば、V$ビューの結果を取得し、ボトルネックの特定を行います。しかし実際のお問い合わせでは、処理が遅延し、滞留していたセッションのKILLや処理実行元のアプリケーションサーバの再起動などでリソースの解放を行い、パフォーマンスダウンが解消してから、発生原因の調査を依頼いただくケースがほとんどです。

過去のパフォーマンス情報を確認する方法としては、Automatic Workload Repository（AWR）とActive Session History（ASH）があります。これらは自動でパフォーマンス情報を取得しています。ただし、これらの情報を参照するにはEnterprise EditionとDiagnostics Packのオプションライセンスの契約が必要です。

これらの契約がない環境で過去のパフォーマンス情報を得るには、AWRに代わるSTATSPACKの導入やASHに代わるV$ビューの定期情報取得など、ユーザ側での事前準備が必要です。

パフォーマンスダウンに関するトラブルのお問い合わせのうち、ライセンスごとの解決率を示しているのが図1です。

ここで言う「解決」とは、処理遅延の原因がどこにあったのか（ロック競合、リソース不足など）の特定までを指します。その後のチューニングまでは含んでいないにもかかわらず、パフォーマンス情報の有無で解決率に2倍以上の差があります。

過去のパフォーマンス情報が取得できていない場合、遅延処理を実行していたセッションの状態などが確認できません。該当時間帯のログファイルにエラーや警告などのメッセージが出力されているようなケースを除いて、多くのケースでは推測での調査しか行えないため、解決率が低くなっています。

言い換えればAWRやASHが利用できない環境でも、STATSPACKやV$ビューの定期取得を行うことで解決率をライセンス所持環境に近いレベルまで上げることができます。そこで今回はパフォーマンスダウンのトラブルに備えたV$ビューの定期取得方法と、取得した情報を使用した調査方法をご紹介します。

Enterprise EditionでDiagnostics Packライセンス未所持の環境やStandard Editionの環境でASH相当の情報を取得するには図2にあるV$SESSIONとV$PROCESSの定期取得が必要です。また、処理遅延の調査の観点ではV$LOCKの情報もあると良いでしょう。

これらのV$ビューの情報は、パフォーマンスダウンの調査ですぐに使用できるよう、可読性の高い形で取得します。

なお、ASHが利用できる環境であれば、デフォルトで過去のセッション情報が取得されているため情報の保持期限（デフォルト8日）さえ気をつければ問題ありません。

次にV$ビューを定期取得する際に注意すべき4つのポイントを紹介します。

定期取得した情報の蓄積には以下の2つの方法があります。

お勧めは「ファイルへのリダイレクト」です。お客様からお問い合わせをいただく際に、リダイレクトしたファイルを提供いただくだけで調査が可能になるからです。また、不要なファイルはOS上から削除することもできます。

ただし、SELECT * FROM V$XXXX;の結果をそのままファイルにリダイレクトすれば良いということではありません。

V$ビューの情報はバージョンを追うごとに多くなっており、例えばOracle Database 12cR1のV$SESSIONでは100列以上あります。実際にSELECT*FROM V$SESSION;を実行していただければわかりますが、何も調整せずにそのままSELECTした結果をリダイレクトしてしまうと、改ページや不要な空白などによって1行で収まらず、可読性の低い、調査に適さない情報になります。

後々調査で使用することを考慮して、ファイルは日次、CSV形式で出力することをお勧めします。図3にあるようにSQLを実行することで、カンマ区切りで結果を出力させることもできます。

取得する列は極力絞らずに、全ての列を取得することを推奨します。取得列や取得条件などの例外を作ると、情報取得スクリプトを作成した本人以外が調査に使用できなくなる可能性が高まるためです。

一方、別テーブルにINSERTする方法の場合、調査に使用するためにはデータベースが起動している必要があり、また、サポートに送付する場合はEXPDPなどで別途抽出作業が必要になります。

また、不要になったデータを削除する際も、同一テーブルにINSERTを続けていた場合には削除範囲を限定したDELETEで行う必要があり、断片化による領域の肥大化や大量のREDOを生成する原因となり得ます。個別に日毎のテーブルを作成した場合は、DROP TABLEで対応可能ですが、オブジェクトが多数作成されるため、管理が煩雑になります。

V$ビューの定期的な取得を依頼する際に「どのくらいの間隔で取得すれば良いですか？」といったご質問をいただきます。

基本的には短ければ短いほど調査に有効な情報を得られますが、出力される情報量（サイズ）と取得による負荷も考慮する必要があります。

ASHではアクティブなセッションの履歴が1秒間隔で確認できるV$ACTIVE_SESSION_HISTORYを利用できますが、1秒間隔では情報量が多く、調査に用いることは容易ではありません。

調査で頻繁に活用するのはDBA_HIST_ACTIVE_SESS_HISTORYというデータディクショナリビューで、V$ACTIVE_SESSION_HISTORYの情報が10秒間隔で格納されています。V$ビューを定期取得する場合も10秒をベースに取得する間隔を検討します。

あるタイミングで取得したV$SESSIONの結果から、セッションがロック解放待ちの状態であることを確認できたとしても、その状態はその時点だけであったか、その後も解放待ちの状態が続いていたのかは判断できません。

定期取得した複数の時点のV$ビューの情報からは、各セッション（プロセス）やロックの状態がどのように変わっているか、変遷を見ていきます。

取得間隔の検討のポイントとなるのは、利用しているシステムで遅延が発生した際に、調査が必要な処理の最大実行時間です。アプリケーション側のタイムアウトが60秒で設定されている処理の調査に対して、情報取得間隔を30秒とした場合、ファイル内に含まれるセッション情報は1～2回です。これでは変遷を追うことはできません。

状態の変遷を見るためには最低3回以上のV$ビューの情報が含まれるよう、このケースでは取得間隔を10～15秒程度とすることが望ましいでしょう。

そのV$ビューの結果はいつ取得した情報かというのは、調査を行う上で非常に重要な要素です。パフォーマンスダウンが発生した時刻がわかっていても、V$ビューの取得時刻が不明では調査に有効な情報とは言えません。

V$ビューの結果を定期取得する際には、図4のようにSELECTで指定する列の最後にTO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’)を含めることを推奨します。

定期的な情報取得では、情報をいつまで残しておく必要があるのかが問題になります。検討する上でポイントになるのが、遅延が発生した際に調査が必要になる処理の実行間隔です。

パフォーマンスダウンが発生した際の調査では「正常時との比較」も行います。そのため週1のバッチ処理をベースに考えるのであれば、8日以上前の情報を保持することで前回との比較が行えます。月1回であれば32日以上残しておけば良いでしょう。

以上、取得形式、取得間隔、取得時刻、保持期間の4点を考慮し、取得したV$SESSIONの情報は図5のように出力されます。

CSV形式で出力することで、図6のように表計算ソフトを使用した条件ごとの並べ替えが行えます。後ほど紹介するSQL*Loaderを使用してデータベースにロードすることで、ASHのようにSQLを利用した調査も行えます。

次に、定期取得したV$ビューを使用したトラブルの調査方法を2つご紹介します。

ここから、本情報を取得いただいているお客様の実際のサポート事例をベースに「アプリケーションからの処理がタイムアウトした」というトラブルの調査方法をご紹介します。

まず、タイムアウト発生時のヒアリングを行ったところ、アプリケーション側で3分と指定しているタイムアウトに抵触して処理が失敗したとのことで「恐らく処理遅延が原因と思っているが、正確なところは不明」とのことでした。

ここでポイントになるのが「タイムアウトした」という過去形であるということです。この場合、今からV$ビューの情報を取得しても、既にタイムアウトしてしまった後であるため原因究明に必要な情報を得ることはできません。

そこで、これまで10秒間隔で定期取得していたV$ビューの情報からアプリケーションからの処理を実行しているセッションがどのような状態にあったのかを調べます。

CSV形式でファイルにリダイレクトしていたV$ビューの情報をデータベースにロードします。Oracle Database 12cR1よりSQL*LoaderにExpress Modeという機能が追加されています。この機能を使用することで容易、かつ、高速にデータベースにデータをロードすることが可能です。

まず、調査用のユーザに接続し、ロードしたいV$ビューと同じ定義の表を作成します。V$SESSIONをロードするのであれば図7のSQLを実行します。

CSV形式で情報をリダイレクトする際に日付のデータを追加していますので、作成した表には日付情報を格納する列を追加します。

前述の「取得時刻-SELECT結果にSYSDATEを含める-」で定期的な情報取得をする際、列の最後に日付を追加することを推奨した理由がこちらにあります。

Oracle DatabaseではALTER TABLE文で表に列を追加する際に任意の位置を指定することができないため、ユーザによる情報取得時に追加のデータを含める場合は最後の列にそのデータを含めることで、格納用の表を容易に作成できます。

これで準備は完了です。

SQL*Loaderを使用してデータをロードします。コマンドは非常に簡単で、ユーザ名とパスワード、tableパラメータに先程作成したテーブル名、dataパラメータに定期取得したV$ビューのファイル名を指定するだけです。

図8の例ではスペースの関係もあり、cdコマンドでCSVファイルの配置先ディレクトリに移動していますが、dataパラメータでフルパスを指定すればディレクトリの移動も不要です。V$SESSIONと同様の手順でV$LOCKの定期取得情報のロードも行います。

「アプリケーションからの処理がタイムアウトした」という事実のため、まずはそのアプリケーションからの処理を実行するセッションがインスタンス上に存在していたのか確認します。

先程ロードしたテーブルをSELECTしますが、そのまま全列、全行表示してしまうと膨大な検索結果が返ってしまいます。

図9のようにタイムアウトが発生した時間帯、アプリケーションからの接続で使用するユーザ名をWHERE句の条件に加えて、表示する情報を絞り込みます。

図10がSELECTの結果です。10秒おきに情報を取得しているため、SNAP_DATE列の値が10秒おきにカウントアップされています。

まずは1段目のSEQ#列に注目していきます。この列の値はセッションの待機状況が変わるごとにカウントアップします。セッションが何か処理を行っているのであれば、処理→待機を繰り返すため、SEQ#の値は増加します。

SNAP_DATEの11:47:14から11:49:54までSEQ#の値が“23”のまま変わっていません。つまり、このセッションは「処理を実行していない」か「処理を実行しようとしているが何かに阻害され続けている」かのどちらかであると推測できます。

どちらであるかを判断するために2段目の列を確認します。EVENT列の値を見ると、“enq: TX - row lock contention”という行ロックの開放を待つ際の待機イベントが確認できます。STATUS列が“ACTIVE”かつSTATE列が”WAITING”になっているので、SQL_ID=64dbjbgssx62fpのSQLは実行中だが、行ロックの解放を待っているということがわかります。

そこで、3段目のBLOCKING_SESSION列とFINAL_BLOCKING_SESSION列から該当のロックを保持しているセッションを確認します。

ロックの待機は連鎖している可能性があり、BLOCKING_SESSION列はこのセッションにとって直接ロック競合の原因となっているセッションのSIDを示し、FINAL_BLOCKING_SESSIONは待機チェーンの先頭にいるセッションのSIDを示しています。今回のケースではどちらも同じく“260”となっているため、SID:19のセッションはSID:260のセッションが保持しているロックを待機していた、ということがわかります。

このことより、アプリケーションタイムアウトの原因は「SQLのパフォーマンスダウン」ではなく「別セッションとのロック競合による待機」であることがわかりました。

ただし、この内容で解決するかというと、そうではありません。原因究明にはどのオブジェクトに対し、どのような処理を実行してロックが競合していたのかの確認が必要です。

セッションがロック競合で待機していた場合、図12にあるようにロック競合の対象となったオブジェクト（ロックを要求しているオブジェクト）のオブジェクトIDがV$SESSIONのROW_WAIT_OBJ#列に表示されます。この値はDBA_OBJECTSのOBJECT_ID列と同じ値であるため、DBA_OBJECTSと照合することで、対象オブジェクトのオーナーとオブジェクト名を確認できます。

さらに、ロック競合していたSQLの特定についてはV$SESSIONのSQL_ID列の値を確認します。この値はV$SQLのSQL_IDと同じ値であるため、図13のようにV$SQLと照合することで待機セッションが実行していたSQL文を確認できます。

ただし、V$SQLから確認できるのはメモリ上に存在しているSQLのみであり、データベースの再起動後や、トラブル発生から長時間経過して、メモリ上からエージアウトした後では確認できない場合があります。

SQL文まで確認できれば、タイムアウトした（ロックを待機していた）セッションの状態は確認できたと言えますが、根本原因はロックを保持していた側のセッションであるため、こちらも調べる必要があります。

ロックを保持していたセッションを調べるには、まずV$LOCKを使用します。V$LOCKもV$SESSIONと同じように、定期取得していたデータをデータベース上にロードし、図14のようなSQLを実行します。

DMLの実行により行ロック（TX）を排他で取得する場合は、必ず表ロック（TM）を行排他で取得します。これはDML実行中の表に対し、後続のDDLが表の定義を変更できないようにするためです（後続のDDLはORA-00054：リソースビジーエラーになります）。

SQLの実行結果が図15です。V$LOCKにおいて、TYPE列が“TM”の行（TMロック)のID1列はV$SESSIONのROW_WAIT_OBJ#列と同じくオブジェクトIDを表します。CTIME列はいつからロックを持っていたのかを秒単位で表します。よって、SID:260のセッションは11:47:19の2,476秒前である11:06:03から、オブジェクト番号100718の表ロックを持っていたということがわかります。

次は、先ほどのV$SESSIONのロードした表に戻り、図16にあるように条件としてSID列が260、かつ、SNAP_DATE列が11:06:03付近の情報を検索します。次に図17にあるように該当行のSQL_ID列、PREV_SQL_ID列とV$SQLを照合することで、ロックを持っていたセッションがその時点で実行していたSQLを確認することができます。

ただし、該当セッションが1トランザクション内で複数のSQLを実行していた場合、10秒間隔のV$SESSIONではロックを獲得したSQLのSQL_IDが出力されない可能性もあります。この場合は該当時間帯近辺の処理をアプリケーション側から調べていくことになりますが、ここまでの内容で次のことが判明しているため、これらを用いて絞り込むことが可能です。

※取得タイミングによっては確認できない可能性もある

パフォーマンスダウンに関する問い合わせの中には「パフォーマンス遅延が発生していた。CPU使用率が高いプロセスがあったがこのプロセスは何をやっていたのか？」というものもあります。

特定プロセスのCPU使用率高騰によるパフォーマンスダウンの場合、既に対象プロセスを強制終了などで対処した後でお問い合わせをいただくケースがほとんどです。

OS側のリソース情報を定期取得する際、原因をプロセス単位まで絞り込むためにはvmstatのようなサーバ全体の情報だけでなく、ps、topのようなプロセス単位の情報も取得する必要があります。

Oracle DatabaseもOS上で動作するアプリケーションの1つであるため、パフォーマンスダウンの原因調査にはOS側のリソース情報が必要になるケースも少なくありません。パフォーマンスダウンの発生に備え、こちらの定期取得も行う必要があります。

図18の定期取得していたtopコマンドの結果からはPID:24317がCPUを使用していたことがわかるため、データベースにロードしたV$PROCESSの情報から該当時間帯のこのプロセスがどのような処理を行っていたのかを調べていきます。

V$PROCESSのSPID列はpsやtopで確認できるOS上のプロセスIDと同じ値であるため、図19のようにプロセスIDを条件に検索します。

V$PROCESSだけでは待機イベントやSQL_IDなどのセッション情報は確認できないため、プロセス情報とセッション情報の紐付けが必要です。図20にあるようにV$PROCESSのADDR列はV$SESSIONのPADDR列と同じ値であるため、まずはこの値を確認します。

V$PROCESSからCPUを使用していたプロセスのADDR列の値を確認した後、図21にあるようにこの値を条件に同じ時間帯のV$SESSIONの情報を確認します。

この後の調べ方は先述のロック競合のケースと同じです。MACHINE列、PROGRAM列から接続元のアプリケーションを特定し、SQL_ID列の値でV$SQLを確認することで処理内容を特定できます。

もし、実績のある処理が突然CPUを使用するようになったのであれば、処理対象の表データが突然増えていないか、SQLの実行計画が変わっていないかといった観点で調査を行っていくことになります。

パフォーマンスダウンはクリティカルなトラブルのため、復旧を優先するケースが多く、事象が発生している最中に調査が行えることは稀です。過去に発生していたパフォーマンスダウンの調査を行うには、その当時のプロセスやセッションの情報を取得しておく必要があります。

今回の2つの事例でお伝えした内容も、情報を取得していなければその時点で調査は行き詰まり、該当時間帯のログファイルなどに何も情報がなければ「原因不明」となってしまいます。

本稿ではパフォーマンスダウンの発生に備え、日頃からどのような情報の取得が必要になるのか事例を交えてご説明しました。

何も問題が起きないことが最良ですが、万が一問題が発生した際に調査が行えるよう、ご利用の環境でどのような情報取得が必要なのかを準備するきっかけとなれば幸いです。

最後までご覧いただきありがとうございました。
本記事でご紹介した製品・サービスに関するコンテンツをご用意しています。また、この記事の他にも、IT技術情報に関する執筆記事を多数公開しておりますのでぜひご覧ください。