Oracle Databaseのハングを迷宮入りさせない

Oracle Databaseで発生する問題の中でも原因の特定が難しいデータベースのハングアップ。その調査を行うにあたり、最も重要なsystemstate dumpの取得方法と調査方法について紹介します。

アシストは1987年よりOracle Database製品の取り扱いを開始し、教育、技術支援、サポートを提供しています。Oracle Databaseはシステムの基幹となるソフトウェアであるため、ミッションクリティカルなシステムに対応できる24時間365日のサポートが必要であり、弊社サポートセンターには年間約9千件、1987年からの累積で約25万件のOracle Database製品に関するお問い合わせをいただいています。

お問い合わせの内容はパッチの入手方法やインストール要件などのQAから、運用中のデータベースに発生したトラブルの対処方法まで様々ですが、Oracle Databaseに発生するトラブルの中で、最も厄介なものは、データベースシステムからの応答が返らない状態となる「データベースのハングアップ（以下、DBハング）」の発生ではないでしょうか。

本稿では、サポートセンターにお問い合わせいただいた事例をもとに、DBハング時に取得するべき情報と、その調査方法についてご紹介します。

2013年に弊社サポートセンターにいただいたお問い合わせを内容ごとに分類した結果が図1です。

製品のQA（Recovery ManagerやEnterprise Managerなどの製品機能の使用方法やOracle Database製品の仕様動作、インストール要件など）が半数以上を占めています。2013年はOracle Database 12cがリリースされたため、新機能の詳細やインストール手順、既存バージョンからのアップグレード方法に関するお問い合わせを数多くいただきました。

データベースの運用中になんらかのエラーが発生したケースやパフォーマンス劣化の対策、原因特定といったいわゆる「トラブル」に分類されるお問い合わせは全体のおよそ4割であり、その中でもデータベース全体に影響を及ぼすDBハングについては全体の0.6%と非常に稀であることがわかります。

次に、お問い合わせごとの解決率の観点で見ていきます。解決率とは、いただいたお問い合わせに明確な回答を提示できたかどうかの割合です。製品入手であれば必要なインストールモジュールやパッチの入手方法、製品のQAはその製品を使用して行いたいことが実現できるかどうか、エラーについてはエラーの発生原因や対処方法、パフォーマンスとDBハングについてはボトルネックとなっている箇所の特定やパフォーマンス劣化の解消方法が提示できたかどうかをもとに集計しています。

DBハングの解決率は25%であり、他のお問い合わせと比較して最も解決率が低く、DBハングが発生した70%以上の環境が原因が特定できないまま、再発の可能性がある状態で運用を継続していることになります（図2）。

影響度の高いトラブルの解決率が低いことはサポートに携わるものとして忍びなく、本稿で「なぜDBハングは解決が難しい問題なのか」、「どのような情報があれば解決する可能性が高くなるのか」についてご紹介し、少しでもOracle Databaseの運用に携わる方のお役に立てればと思います。

トラブルに関するお問い合わせ（エラー、パフォーマンス、DBハング）のうち、エラーが発生した事象であればエラーコードやメッセージの内容から調査が行えますし、エラーが発生した処理内容についてもアプリケーション側から特定しやすいため、最も解決率が高くなります。

パフォーマンスの問題は、パフォーマス劣化の状況や、初期化パラメータの設定内容、SQLトレースなどを分析して調査します。SQLトレースは現象が発生しなければ情報を取得できないので、現象が再現できなければ原因を特定できないケースが多く、明示的にエラーが発生した場合と比べて解決率は低くなります。

DBハングの場合、ハングが発生時点でインスタンス上の各プロセスがどのような処理を実行していて何を待機していたかを確認することで原因を特定できますが、調査に必要な情報は自動出力されないため、事象発生時に明示的に情報を取得しなければなりません。

しかし、DBハングの調査に必要な情報を取得する準備が整っている環境は少なく、また、DBハングに関するお問い合わせの約6割は、サービス復旧が優先で、OSやDBを再起動した後にお問い合わせをいただくため、必要な情報が取得できていない状況にあります（図3）。

OSやDBを再起動した後では、DBハング発生時に各プロセスがどのような状態になっていたのかを確認することができません。内部機能で自動的に情報が出力されているケースを除けば、DBハングでは遅延の対象になっている処理を特定するための手がかりを得られないことが多く、最も解決率が低いトラブルとなります。

次の項では、どのような情報を取得すればDBハングの原因を特定できるかをご紹介します。

DBハングの調査には、ハングの要因となっている各プロセス（セッション）がどのような処理を実行しているのか、何を待機しているのか、といった情報を取得する必要があります。そのためには、SYSユーザからoradebugコマンドを使用して、インスタンス上のプロセスの詳細情報を確認できるSYSTEMSTATE DUMPを取得します（図4）。

SYSTEMSTATE DUMPでは、SYSTEMSTATE DUMP実行時のインスタンス上に存在する全てのプロセス情報が出力されます。例えば「あるプロセスが、別のプロセスに待たされている」といった時系列の関係性を把握するには、SYSTEMSTATE DUMPを複数回取得してプロセスの状態遷移を確認します。

ただ、SYSTEMSTATE DUMPに出力される情報量は非常に多いため、SYSTEMSTATE DUMPの出力結果からDBハングの調査対象となるプロセスを特定することは容易ではありません。可能であれば、現在のセッション情報が確認できるV$SESSIONビュー情報の取得をお薦めします。

V$SESSIONビューから各セッションのステータスや待機イベントを確認することで、ハングしているセッションの目安をつけ、そのセッションに対応するプロセスに着目してSYSTEMSTATE DUMPの分析が行えるため、SYSTEMSTATE DUMPのみを分析する場合に比べ調査効率が向上します。

例えば図5のV$SESSIONビューの検索結果であれば、SID：133のセッションから実行されている処理のSTATE列が「WAITING」、EVENT列が「TX - row lock contention」と表示されているため、このセッションは行ロックを取得できずに待機している状況を表しています。一定時間が経過しV$SESSIONビューを再度確認した際に、上記と同じ状況であれば、そのセッションは何かしらの原因で行ロックが取得できていないと判断できます。

V$SESSIONビューから待機しているセッションが確認できれば、併せて取得したSYSTEMSTATE DUMPからSID：133のセッションを待機させているプロセスの調査を行うことが可能になります。

ただし、DBハングが発生している状況では、SQL*Plusでデータベースに接続できない場合があります。

この場合は、SQL*Plusの予備接続オプションを使用してデータベースに接続します。予備接続は通常の接続とは異なり必要となるリソースが少ないため、DBハングしている状態でも接続が行える可能性があります。

予備接続ではSQLの実行ができないためV$SESSIONビューの確認は行えませんが、図4の方法でoradebugコマンドを使用したSYSTEMSTATE DUMPの取得が行えます。

SYSTEMSTATE DUMPに出力される主な情報について説明します。SYSTEMSTATE DUMPにはプロセスIDごとに情報が出力されます。図7はサーバプロセスの情報ですのでO/S infoの箇所に出力される起動OSユーザ名やマシン名には他のサーバプロセスでも全て同じ値が入ります。

サーバプロセスに紐付くセッションの情報や、接続元のクライアントの情報はPROCESS STATEから確認します。図8ではSIDが63のセッションについて表示されており、接続元のクライアント情報の確認はclient details: 以下から行います。先ほどのサーバプロセスと同様にプロセス名やマシン名が表示されるため、DBハングが発生した際にはハングしているセッションの接続元のプログラム名で検索することで、そのセッションの情報を確認できます。

図9は図8の続きですが、ここからセッションの詳しい情報が確認できます。waiting for で出力されている箇所が現在の待機イベントです。この例では「cursor: pin S wait on X」という待機イベントで待機していることがわかります。これはライブラリキャッシュ内のカーソルに対してmutex（ロック）を共有モードで獲得しようとしたところ、すでにこのmutexを排他モードで保持しているプロセスが存在していたために、その開放を待っている状態です。

このようにSYSTEMSTATE DUMPにはプロセス（セッション）ごとの詳しい情報が出力されますが、例えば、同じオブジェクトのロックを待機し続けているのか、ロックを保持しているセッションは変わらないのかといった情報は、複数回SYSTEMSTATE DUMPを取得しないと判断できません。

また、稼働しているプロセス数が多ければ多いほどSYSTEMSTATE DUMPの出力に時間がかかるため、出力時点の状況と実際の処理状況が異なり、情報として有効ではなくなるケースがあります。例えば、100個のプロセスが稼働している環境で、プロセス番号100があるオブジェクトをロックしプロセス番号1がその解除待ち状態になっている場合、「プロセス番号1はプロセス番号100のロック解除を待機中である」ことがSYSTEMSTATE DUMPに出力され、プロセス番号100の情報が出力される頃にはプロセス番号100が保持していたロックが解除されて「プロセス番号1が要求していたロックを保持しているプロセスはない」状態に見えることがあります。

以上のように、SYSTEMSTATE DUMPはDBハングの原因特定に最も重要な情報ではありますが、1回の取得だけでは調査が難しく、また、取得のタイミングによっては有効な情報とならない可能性もあります。そのため、SYSTEMSTATE DUMPはDBハングが発生している状態で最低2回、可能であれば3回取得することをお薦めします。

実際にDBハングが発生し、SYSTEMSTATE DUMPから原因を特定した例をご紹介します。

まずは応答のない処理のマシン名やアプリケーション名をもとに、SYSTEMSTATE DUMPから対象のセッションを確認します。

本ケースではMicrosoft Access（MS ACCESS）からの接続であったためアプリケーション名が「MSACCESS.EXE」のセッションを確認したところ、対象のセッションはhandle address=3bcb57630 のライブラリキャッシュオブジェクトに対して共有モード（request=S）でのロックを要求し待機状態にありました（図10）。他のMSACCESS.EXEのセッションも同様に、同一の handle addressのライブラリキャッシュオブジェクトの待機状態となっていました。

また、統計情報の収集を行っているセッションが同じhandle addressのライブラリキャッシュオブジェクトに対して排他モード（request=X）でのロックを要求し、待機状態となっていました（図11）。

このhandle address=3bcb57630 のライブラリキャッシュオブジェクトを保持しているセッションを確認したところ、長時間のEXPORTを実行しているセッションが共有モード（mode=S）でロックを保持し続けていました（図12）。

共有モード同士であれば、複数のセッションで同時にロックを保持できますが、この例では共有モードでのロック要求が待機していました。つまり、このSYSTEMSTATE DUMPの内容から、図13のようにEXPORTを実行しているセッションが保持しているライブラリキャッシュオブジェクトのロックを統計情報取得セッションが排他モードで要求しており、そのセッションをMS ACCESSのセッションが待機していたため待機が連鎖し、DBハングに至っていたと判断することができます。

ちなみにこのケースでは、問題となっていたEXPORT処理が再実行可能であったため、EXPORT処理を終了させることで後続の処理が進み、DBハングは解消しました。

DBハングは、ディスクやネットワーク障害のようにトラブル発生時のイメージがし難いこともあり、他のトラブルと比較して影響度が大きい割りに事前の対応策が講じられることの少ないトラブルです。

先ほどの例のようにSYSTEMSTATE DUMPが取得できていればDBハングの原因となっているセッションや処理を特定できる可能性が高くなるため、DBハングに備えて情報取得ができるよう準備しておくことが最も重要です。

以下にLinux環境で利用可能な情報取得シェルのサンプルをご紹介します。このシェルでは実行によりSYSTEMSTATE DUMPとV$ビューの情報が1分置きに3回取得されます。DBハングに備え情報取得ができるようご活用ください。

※本サンプルコードに対するサポートは行っておりませんので、本サンプルに対するご質問はご遠慮ください。またご利用の際には、御自身の責任のもとでご利用いただけますようお願いします。

本稿ではDBハングの原因を究明するためにSYSTEMSTATE DUMPの取得が重要であることを事例を交えてご説明しました。DBハングは発生しないことが望ましいのですが、発生した場合でも迷宮入りしないよう、事前にSYSTEMSTATE DUMP取得の準備をするきっかけとなれば幸いです。

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