1. データベース・パフォーマンス・チューニング・ガイド
  2. データベース・パフォーマンスの診断およびチューニング
  3. パフォーマンス・ビューを使用したインスタンスのチューニング

10 パフォーマンス・ビューを使用したインスタンスのチューニング

データベースの初期構成後は、インスタンスの定期的な監視およびチューニングがパフォーマンスの潜在的なボトルネックを解消するために重要になります。この章では、OracleのV$パフォーマンス・ビューを使用したチューニング・プロセスについて説明します。

この章の構成は、次のとおりです。

10.1 インスタンスのチューニング手順

次に、インスタンスのチューニング用のOracleパフォーマンス・メソッドの主な手順を示します。

  1. 問題の定義

    パフォーマンス問題の範囲についてユーザーから候補フィードバックを取得します。

  2. ホスト・システムの検査およびOracle Database統計の調査

    • オペレーティング・システム、データベースおよびアプリケーション統計一式を取得後に、パフォーマンスの問題の徴候を探すためにデータを調べます。

    • 一般的なパフォーマンス・エラーのリストを検討して、収集されたデータが問題に影響を与えていることを示しているかどうかを確認します。

    • 収集されたパフォーマンス・データを使用して、何がシステムで起こっているかを示す概念モデルを構築します。

  3. 変更の実装および測定

    行う変更および変更を実装した場合に予測される結果を提示します。次に、変更を実装してアプリケーション・パフォーマンスを測定します。

  4. 手順1で定義したパフォーマンスの目的が達成されたかどうかを判断します。達成されていない場合は、パフォーマンスの目標が達成されるまで手順2と3を繰り返します。

この章の残りの部分では、Oracle Databaseの動的パフォーマンス・ビューを使用したインスタンスのチューニングについて説明します。ただし、機能リストの拡張により、統計の収集、監視およびチューニングには、自動ワークロード・リポジトリ(AWR)および自動データベース診断モニター(ADDM)を使用することをお薦めします。

注意:

AWRおよびADDM機能がない場合、Statspackを使用してOracleデータベース・インスタンスの統計情報を収集できます。

10.1.1 問題の定義

ソリューションの実装を試みる前に、チューニング調査の目的と問題の性質をよく理解しておくことが不可欠です。これについて理解していないと、事実上、効果的な変更は実装できません。この段階で収集されたデータを使用して、次に行うこと、および調査する事象を簡単に決定できます。

次のデータを収集します。

  1. パフォーマンスの目的を識別します。

    許容できるパフォーマンスの測定尺度は何ですか。1時間、または1秒間当たり何件のトランザクションで、レスポンス時間が必要なパフォーマンス・レベルを満たしますか。

  2. 問題の範囲を識別します。

    スローダウンで何が影響を受けますか。たとえば、インスタンス全体は低速ですか。それは、特定のアプリケーション、プログラム、特定の操作またはシングル・ユーザーですか。

  3. 問題が発生したときの時間帯を識別します。

    その問題はピーク時間のみ明白ですか。パフォーマンスはその日の経過に伴って低下しますか。スローダウンは徐々に(月または週の単位で)発生しましたか、または突然発生しましたか。

  4. スローダウンを検証します。

    これは、問題の範囲の識別に役立ち、問題の修復のために実装された変更により実際に改善されたかどうかを判断するときの比較結果の測定基準としての役割を果します。一貫して再生可能なレスポンス時間またはジョブ実行時間の測定値を検索します。プログラムの動作が正常だったときよりタイミングがどのくらい悪化していますか。

  5. 変更を識別します。

    パフォーマンスが許容可能になった後に変化した内容を識別します。これにより、潜在的な原因を素早くつきとめることができます。たとえば、オペレーティング・システムのソフトウェア、ハードウェア、アプリケーション・ソフトウェアまたはOracle Databaseリリースのアップグレードを実行しましたか。さらに多くのデータがシステムにロードされたか、データ・ボリュームまたはユーザー人口が増加しましたか。

このフェーズの終わりまでに、症状についてよく理解しておく必要があります。症状をプログラムまたはプログラム・セットにローカルなものとして識別できる場合、その問題はインスタンス全体のパフォーマンスの問題とは異なる方法で処理されます。

10.1.2 ホスト・システムの検査

データベース・サーバーに対する負荷とデータベース・インスタンスを調べてください。オペレーティング・システム、I/Oサブシステムおよびネットワーク統計を検討してください。これらの領域を調べると、調査する価値のあるものは何かが容易にわかります。多層のシステムでは、アプリケーション・サーバーの中間層ホストも調べてください。

ホスト・ハードウェアを調べると、システム内のボトルネックがよくわかります。この調査によって、相互参照および今後の診断に役立つOracle Databaseパフォーマンス・データを判断できます。

調べるデータには、次のものがあります。

10.1.2.1 CPU使用率

アイドル状態のCPUが大量にある場合、I/O、アプリケーションまたはデータベースのボトルネックが存在する可能性があります。I/O待機はアイドル状態のCPUとしてみなす必要があります。

CPU使用率が高い場合は、CPUが効果的に使用されているかどうかを判断してください。CPU使用率の大部分は、CPU使用率の高い少数のプログラムによるものですか、または均等に分散されたワークロードでCPUが消費されていますか。

CPUが使用頻度の高い少量のプログラムで使用されている場合は、プログラムを調べて原因を判断してください。一部のプロセスのみが1つのCPUの能力全体を使用しているかどうかを確認してください。プロセスによっては、この情報はCPUまたはプロセスによりワークロードがバインドされていることを示している場合があり、プロセス・アクティビティを分割またはパラレル化することで解決できます。

10.1.2.1.1 Oracle以外のプロセス

プログラムがOracleのプログラムではない場合は、それらのプログラムがそのような量のCPUを必要としているかどうかを識別してください。必要としている場合は、プログラムの実行をピーク以外の時間に遅らせることができるかどうかを判断します。これらのCPU集中型プロセスを識別することで、I/O、ネットワークおよびページングなどのどのアクティビティがリソースを消費しているかを特定し、データベース・ワークロードとの関係を確認できます。

10.1.2.1.2 Oracleプロセス

少数のOracleプロセスによって多くのCPUリソースが使用されている場合は、SQL_TRACEとTKPROFを使用してSQL文またはPL/SQL文を特定して、特定の問合せまたはPL/SQLプログラム・ユニットをチューニングできるかどうかを確認します。たとえば、SELECT文の実行によりキャッシュ内の多数のデータ読取り(論理読取り)が発生する場合、さらなるSQLの最適化によってCPUの集中的な使用を回避できます。

10.1.2.1.3 Oracle Database CPU統計

Oracle Database CPU統計は、V$ビューで取得できます。

  • V$SYSSTATは、すべてのセッションのOracle Database CPU使用率を示します。CPU used by this session統計は、すべてのセッションで使用されているCPUの集計を示します。parse time cpu統計は、解析に使用された合計CPU時間を示します。

  • V$SESSTATは、各セッションのOracle Database CPU使用率を示します。このビューを使用して、CPUを最も多く使用している特定のセッションを調べます。

  • Oracle Database Resource Managerを実行している場合、各コンシューマ・グループのCPU使用率の統計情報がV$RSRC_CONSUMER_GROUPに表示されます。

10.1.2.1.4 CPU統計の解釈

CPU時間と実時間の違いを認識しておくことは重要です。8個のCPUの場合、実時間で1分間に使用可能なCPU時間は8分間です。この時間は、WindowsおよびUNIXではユーザー時間またはシステム時間です(Windowsでは特権モード)。したがって、システム上のすべてのプロセス(スレッド)で使用される平均CPU時間は、1分の実時間間隔当たり1分を超える可能性があります。

どの特定の時点においても、システム上でOracle Databaseが使用した時間を識別できます。使用可能な時間が8分間で、Oracle Databaseがそのうちの4分間を使用した場合、総CPU時間の50%がOracleによって使用されたことがわかります。ユーザーのプロセスがその時間を消費していない場合は、他のプロセスが消費しています。CPU時間を使用しているプロセスを識別し、原因を解明し、それらのプロセスのチューニングを試行してください。

CPU使用率が多数のOracleサーバー・プロセスに均一に分散している場合は、V$SYS_TIME_MODELビューを調べると、最長時間が消費されているプロセスを正確に把握できます。

関連項目:

様々な待機イベントと考えられるその原因の詳細は、「表10-1」を参照してください

10.1.2.2 I/Oの問題の識別

過度にアクティブなI/Oシステムは、2より大きいディスク・キューの長さ、すなわち、20から30ミリ秒を超えるディスク・サービス時間でわかります。I/Oシステムが過度にアクティブである場合、さらに多くのディスク間にI/Oを分散させることで利益を得られる潜在的なホット・スポットの有無をチェックします。また、これらのリソースを使用して、プログラムのリソース要件を少なくして負荷を減らせるかどうかも識別します。I/O問題の原因がOracle Databaseである場合、I/Oチューニングを開始できます。Oracle Databaseが使用可能なI/Oリソースを消費していない場合、I/Oを消費しているプロセスを識別します。プロセスがI/Oを消費している原因を究明して、プロセスをチューニングします。

I/O問題は、Oracle DatabaseのV$ビューおよびオペレーティング・システムのモニタリング・ツールによって識別できます。次の項では、I/O問題の識別について説明します。

10.1.2.2.1 V$ビューを使用したI/Oの問題の識別

V$SYSTEM_EVENTのOracle待機イベント・データをチェックして、トップの待機イベントがI/O関連かどうかを確認します。I/O関連イベントには、db file sequential read、db file scattered read、db file single write、db file parallel writeおよびlog file parallel writeがあります。これらはいずれも、データファイルおよびログ・ファイルに対して実行されたI/Oに対応するイベントです。これらの待機イベントが高い平均時間に該当する場合は、I/Oの競合を調べる必要があります。

自動ワークロード・リポジトリ・レポート内のI/OセクションでホストI/Oシステム・データを相互参照し、ホット・データファイルおよび表領域を識別します。さらに、オペレーティング・システムから報告されたI/O時間と、Oracle Databaseから報告された時間とを比較して、それらに一貫性があるかどうかを確認します。

I/Oの問題によって、I/Oに関連しない待機イベントが明らかになる場合もあります。たとえば、バッファ・キャッシュ内で空きバッファを検出できない場合や、ディスクへのログ書込みが完了するまでの待機時間が長い場合も、I/O問題の症状を示す場合があります。I/Oシステムを再構成する必要があるかどうかを調べる前に、I/Oシステム上の負荷を減らせるかどうかを判断します。

Oracle Databaseを原因とするI/O負荷を減らすには、次のビューを使用して、データベースによるすべてのI/Oコールに対して収集されたI/O統計を調査します。

  • V$IOSTAT_CONSUMER_GROUP

    V$IOSTAT_CONSUMER_GROUPビューには、コンシューマ・グループのI/O統計が取得されます。Oracle Database Resource Managerが有効な場合、現在有効なリソース・プランに含まれるすべてのコンシューマ・グループのI/O統計が取得されます。

  • V$IOSTAT_FILE

    V$IOSTAT_FILEビューには、現在アクセスされているか、または過去にアクセスされたデータベース・ファイルのI/O統計が取得されます。SMALL_SYNC_READ_LATENCY列には、単一ブロック同期読取り(ミリ秒単位)の待機時間が表示されますが、これはクライアントが次の操作に移行する前に待機する必要のある時間を表します。これにより、現在の負荷に基づいたストレージ・サブシステムのレスポンス性が定義されます。重要なデータファイルに対する待機時間が長い場合、それらのファイルを再配置してサービス時間を短縮することを検討してください。待機時間の統計を計算するには、timed_statisticsがTRUEに設定されている必要があります。

  • V$IOSTAT_FUNCTION

    V$IOSTAT_FUNCTIONビューには、データベース機能(LGWRやDBWRなど)のI/O統計が取得されます。

    I/Oは、異なる機能を持つ様々なOracleプロセスによって発行されます。上位のデータベース機能は、V$IOSTAT_FUNCTIONビューに分類されます。I/O機能間の競合がある場合、そのI/Oはより小さいFUNCTION_IDのバケットに配置されます。たとえば、XDBがバッファ・キャッシュからI/Oを発行する場合、そのI/OはXDB I/Oに分類されますが、これはXDB I/OのFUNCTION_ID値の方が小さいためです。未分類の機能は、Othersバケットに配置されます。次のようにV$IOSTAT_FUNCTIONビューを問い合せると、FUNCTION_IDの階層を表示できます。 

    select FUNCTION_ID, FUNCTION_NAME
from v$iostat_function
order by FUNCTION_ID;

FUNCTION_ID FUNCTION_NAME
----------- ------------------
           0 RMAN
           1 DBWR
           2 LGWR
           3 ARCH
           4 XDB
           5 Streams AQ
           6 Data Pump
           7 Recovery
           8 Buffer Cache Reads
           9 Direct Reads
          10 Direct Writes
          11 Others

これらのV$IOSTATビューには、単一ブロックと複数ブロックの読取り/書込み操作のI/O統計が含まれます。単一ブロック操作は、128KB以下の小規模なI/Oです。複数ブロック操作は、128KBを超える大規模なI/Oです。これらの操作ごとに、次の統計が収集されます。

  • 識別子

  • 合計待機時間(ミリ秒)

  • 実行された待機操作数(コンシューマ・グループおよび機能)

  • 操作ごとのリクエスト数

  • 単一および複数ブロックの読取りバイト数

  • 単一および複数ブロックの書込みバイト数

また、V$SQLAREAビューの問合せか、自動ワークロード・リポジトリ・レポートの「読取りによるSQLのソート」セクションの確認によって、多数の物理読取りを実行するSQL文についても調べる必要があります。これらの文を調べて、I/Oの回数を減らすようにこれらのSQL文をチューニングする方法を調べます。

関連項目:

V$IOSTAT_CONSUMER_GROUP、V$IOSTAT_FUNCTION、V$IOSTAT_FILEおよびV$SQLAREAビューの詳細は、『Oracle Databaseリファレンス』を参照してください

10.1.2.2.2 オペレーティング・システムのモニタリング・ツールを使用したI/Oの問題の識別

オペレーティング・システムのモニタリング・ツールを使用して、システム全体で実行されているプロセスを判別し、すべてのファイルに対するディスク・アクセスを監視してください。データファイルとREDOログ・ファイルを保持しているディスクは、Oracle Databaseに関連しないファイルも保持している可能性があります。データベース・ファイルを含むディスクに対する過度のアクセスを減らしてください。データベース以外のファイルへのアクセスは、V$ビューではなく、オペレーティング・システムの機能でのみ監視できます。

多数のUNIXシステム上のsar -d(またはiostat)やWindowsシステム上の管理パフォーマンス・モニタリング・ツールなどのユーティリティは、システム全体のI/O統計を調べます。

関連項目:

プラットフォームで使用可能なツールのオペレーティング・システムのマニュアル

10.1.2.3 ネットワークの問題の識別

オペレーティング・システムのユーティリティを使用して、ネットワーク・ラウンドトリップのping時間と衝突数を調べます。ネットワークでレスポンス時間の大幅な遅延が発生している場合は、考えられる原因を調べてください。

データベース・ファイルへのリモート・アクセスを原因とするネットワークI/Oを識別するには、V$IOSTAT_NETWORKビューを調査します。このビューには、リモート・データベース・インスタンスのファイルへのアクセスに基づく次のようなネットワークI/O統計が含まれます。

  • ネットワークI/Oを開始したデータベース・クライアント(RMANやPLSQLなど)

  • 発行された読取りおよび書込み操作の数

  • 読取りおよび書込みKB数

  • 読取り操作の合計待機時間(ミリ秒単位)

  • 書込み操作の合計待機時間(ミリ秒単位)

ネットワーク問題の原因の識別後にネットワーク負荷を減らすには、大きいデータ転送をピーク時間外へスケジュールするか、リモート・ホストに対してリクエスト当たり1回ずつ(またはそれ以上)アクセスせずに、リクエストをバッチ処理するようにアプリケーションをコーディングします。

10.1.3 Oracle Database統計の調査

Oracle Database統計を確認し、それをオペレーティング・システムの統計と照合して、一貫性のある問題の診断を行います。オペレーティング・システムの統計では、チューニングの出発点となる最適な情報を取得できます。ただし、Oracleデータベース・インスタンスをチューニングすることが目的の場合は、修正処置を行う前に、Oracle Database統計を調べ、データベースの観点からリソースのボトルネックを特定します。

この項では、次の項目について説明します。

関連項目:

Oracle Database統計の解釈

10.1.3.1 統計収集のレベルの設定

Oracle Databaseには、データベースのすべての主要な統計収集またはアドバイザを制御する初期化パラメータSTATISTICS_LEVELがあります。このパラメータは、データベースの統計収集レベルを設定します。

STATISTICS_LEVELの設定に応じて、次のように一定のアドバイザまたは統計が収集されます。

  • BASIC: アドバイザも統計も収集されません。監視機能および多数の自動機能が無効です。重要なOracle Database機能が無効になるため、この設定は使用しないことをお薦めします。

  • TYPICAL: これはデフォルト値であり、すべての主要統計が収集され、データベース全体のパフォーマンスが最高になります。ほとんどの環境では、この設定で十分です。

  • ALL: TYPICAL設定を使用して収集されるすべてのアドバイザと統計に加えて、オペレーティング・システム時間統計および行ソース実行統計が含まれます。

関連項目:

  • STATISTICS_LEVEL初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください

  • V$STATISTICS_LEVELビューの詳細は、Oracle Databaseリファレンスを参照してください。このビューには、STATISTICS_LEVEL初期化パラメータで制御される統計またはアドバイザのステータスがリストされます。

10.1.3.2 待機イベント

待機イベントは、処理を継続する前にイベントが完了するまで待機する必要があることを示すために、サーバー・プロセスまたはスレッドによって増やされる統計です。待機イベント・データは、ラッチの競合、バッファの競合、I/Oの競合などのパフォーマンスに影響を与えると思われる様々な問題の症状を表します。ただし、これらの問題は実際の原因でなく、問題の症状にすぎないことに注意してください。

待機イベントは、クラス別にグループ化されています。待機イベント・クラスには、Administrative、Application、Cluster、Commit、Concurrency、Configuration、Idle、Network、Other、Scheduler、System I/O、およびUser I/Oがあります。

サーバー・プロセスには、次のような待機があります。

  • バッファやラッチなどのリソースが使用可能になるのを待機します。

  • I/Oなどのアクションが完了するのを待ちます。

  • 実行する追加作業(クライアントが次に実行するSQL文を提供するまで待機する場合など)。サーバー・プロセスが追加作業の待機中であることを識別するイベントのことをアイドル・イベントと呼びます。

待機イベント統計には、イベントを待機した回数や、イベントが完了するまでの待機時間があります。TIMED_STATISTICS初期化パラメータをtrueに設定すると、各リソースを待機した時間も表示されます。

ユーザー・レスポンス時間をできるだけ少なくするには、イベントが完了するまでサーバー・プロセスが待機する時間を減らします。すべての待機イベントが同じ待機時間を持っているとはかぎりません。したがって、発生回数の多い待機イベントより、最大の合計時間を持つイベントを調べるほうが重要です。通常は、少なくともパフォーマンスの監視中にTIMED_STATISTICS動的パラメータをtrueに設定することが最善です。

関連項目:

10.1.3.3 待機イベント統計を含む動的パフォーマンス・ビュー

待機イベント統計について、これらの動的パフォーマンス・ビューの問合せを行うことができます。

  • V$ACTIVE_SESSION_HISTORY

    V$ACTIVE_SESSION_HISTORYビューには、1秒ごとにサンプリングされたアクティブなデータベース・セッションのアクティビティが表示されます。

  • V$SESS_TIME_MODELおよびV$SYS_TIME_MODEL

    V$SESS_TIME_MODELビューおよびV$SYS_TIME_MODELビューには、データベース・コールの所要時間の合計であるDB timeなど、時間モデル統計が含まれます。

  • V$SESSION_WAIT

    V$SESSION_WAITビューには、各セッションの現在または最後の待機に関する情報(待機ID、クラス、時間など)が表示されます。

  • V$SESSION

    V$SESSIONビューには、各現行セッションに関する情報が表示されます。また、このビューには、V$SESSION_WAITビューと同じ待機統計が含まれています。該当する場合、このビューには、セッションが現在待機中のオブジェクトの詳細情報(オブジェクト番号、ブロック番号、ファイル番号、行番号など)、現在の待機の原因となったブロック・セッション(ブロック・セッションID、ステータス、タイプなど)、および待機時間も含まれます。

  • V$SESSION_EVENT

    V$SESSION_EVENTビューは、セッションが開始した後に待機したすべてのイベントのサマリーを示します。

  • V$SESSION_WAIT_CLASS

    V$SESSION_WAIT_CLASSビューは、待機数および各セッションの待機イベントの各クラスで消費される時間を示します。

  • V$SESSION_WAIT_HISTORY

    V$SESSION_WAIT_HISTORYビューには、各アクティブ・セッションの最新10件の待機イベントに関する情報(イベント・タイプや待機時間など)が表示されます。

  • V$SYSTEM_EVENT

    V$SYSTEM_EVENTビューは、インスタンス起動後のインスタンスの、全イベント待機のサマリーを示します。

  • V$EVENT_HISTOGRAM

    V$EVENT_HISTOGRAMビューには、待機数、最大待機時間およびイベントごとの合計待機時間を示すヒストグラムが表示されます。

  • V$FILE_HISTOGRAM

    V$FILE_HISTOGRAMビューには、ファイルごとに1ブロック読取り中の待機回数を示すヒストグラムが表示されます。

  • V$SYSTEM_WAIT_CLASS

    V$SYSTEM_WAIT_CLASSビューは、待機数に対するインスタンス全体の総時間および待機イベントの各クラスで消費される時間を示します。

  • V$TEMP_HISTOGRAM

    V$TEMP_HISTOGRAMビューには、一時ファイルごとに1ブロック読取り中の待機回数を示すヒストグラムが表示されます。

パフォーマンス・チューニングを実行するときに、待機イベントと関連するタイミング・データを調査します。最大時間がリストされるイベントは、多くの場合、パフォーマンス・ボトルネックを顕著に示しています。たとえば、V$SYSTEM_EVENTを参照することで、多くのbuffer busy waitsが発生していると気づくことがあります。おそらく、多数のプロセスが同じブロックに挿入しようとするときに、各プロセスが他のプロセスの挿入を待機してからでないと挿入できないことが原因です。問題となっているオブジェクトに自動セグメント領域管理またはパーティション化を使用することで解決する可能性があります。

関連項目:

10.1.3.4 システム統計

システム統計は通常、パフォーマンスの問題の原因をさらに示すものを見つけるために、待機イベント・データとともに使用されます。

たとえば、最大の待機イベント(待機時間の点で)がbuffer busy waitsイベントであることをV$SYSTEM_EVENTが示している場合、V$WAITSTATビューで使用できる特定のバッファ待機統計を調べて、どのブロック・タイプが最大の待機カウントと最大の待機時間を持っているかを識別します。

ブロック・タイプを識別した後、問題の発生中にV$SESSIONをリアルタイムで調べるか、問題の発生後にV$ACTIVE_SESSION_HISTORYビューおよびDBA_HIST_ACTIVE_SESS_HISTORYビューを調べ、表示されたオブジェクト番号を使用して競合対象のオブジェクトを識別します。このデータの組合せは、適切な修正アクションを示しています。

統計は、多数のV$ビューで使用できます。システム統計を含むV$ビューをいくつか次にあげます。

V$ACTIVE_SESSION_HISTORY

このビューには、1秒ごとにサンプリングされたアクティブなデータベース・セッションのアクティビティが表示されます。

V$SYSSTAT

ロールバック、論理I/O、物理I/O、解析データをはじめとするOracle Databaseの様々な部分の全般的な統計が含まれます。バッファ・キャッシュ・ヒット率などの比率を計算するには、V$SYSSTATからのデータを使用します。

V$FILESTAT

これには、ファイル当たりのI/O回数や平均読取り時間など、ファイルごとの詳細なファイルI/O統計が含まれています。

V$ROLLSTAT

これには、詳細なロールバック・セグメントおよびUNDOセグメント統計が含まれています。

V$ENQUEUE_STAT

これには、エンキューが要求された回数やエンキューを待機した回数、待機時間など、各エンキューの詳細なエンキュー統計が含まれています。

V$LATCH

これには、各ラッチが要求された回数やラッチを待機した回数など、各ラッチの詳細なラッチ使用統計が含まれています。

関連項目:

動的パフォーマンス・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

10.1.3.5 セグメント・レベルの統計

個別のセグメントに関連するパフォーマンス問題に焦点をあてるのに役立つ、セグメント・レベルの統計を収集できます。セグメント・レベルの統計を収集して表示することは、インスタンスで競合度の高い表あるいは索引を効果的に識別するための優れた方法です。

パフォーマンスの問題を識別するために待機イベントおよびシステム統計を表示した後で、セグメント・レベルの統計を使用して問題の原因となっている特定の表または索引を検索できます。バッファ・ビジー待機が、大半の待機時間の原因になっていることをV$SYSTEM_EVENTが示している例を考えます。バッファ・ビジー待機の原因になっているトップ・セグメントをV$SEGMENT_STATISTICSから選択できます。これにより、それらのセグメントの問題の解決に集中できます。

セグメント・レベルの統計は、次の動的ビューを使用して問い合せます。

  • V$SEGSTAT_NAME: : このビューには収集するセグメント統計と、各種統計(たとえばサンプル統計など)のプロパティがリストされます。

  • V$SEGSTAT: これは非常に効率的で、リアルタイム監視が可能なビューであり、統計値、統計名およびその他の基本情報が表示されます。

  • V$SEGMENT_STATISTICS: ユーザーが扱いやすい統計値のビューです。V$SEGSTATのすべての列の他、ここにはセグメント所有者や表領域名などの情報があります。統計の理解は容易になりますが、コストがより高くなります。

    関連項目:

    動的パフォーマンス・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

10.1.4 変更の実装および測定

チューニングを実施した後、多くの場合、問題を軽減できると思われる2つまたは3つの変更を識別できます。どの変更が最高の利益を提供するかを識別するには、一度に1回の変更を実装することをお薦めします。変更の効果は、問題定義段階でみられたベースライン・データ測定と対照して測定する必要があります。

一般に、パフォーマンスの問題を持つ大半のサイトでは、一度に重複した変更を実装するので、どの変更が利益を実現したかを識別できません。これはすぐに問題になることはありませんが、どの変更が最も効果をあげ、どのような作業を優先する必要があるかを知ることは不可能なので、今後同様の問題が発生した場合に大きな障害になります。

個別に変更を実装できない場合は、異なる変更の効果の測定を試みてください。たとえば、変更された問合せのパフォーマンスを向上するために新しい索引を作成する効果とは別に、REDOの生成を最適化するために初期化変更を行う効果を測定します。SQLがチューニングされ、オペレーティング・システムのディスク・レイアウトが変更され、初期化パラメータも同時に変更されている場合は、オペレーティング・システムをアップグレードすることの利益は測定できません。

パフォーマンス・チューニングは反復操作です。インスタンス全体のパフォーマンスの問題を解決する特効薬的な対策が見つかることはほとんどありません。ほとんどの場合、あるボトルネックを解決しても別の(ときにはさらに悪い)問題が発生するため、優れたパフォーマンスにはパフォーマンス・チューニング段階を反復する必要があります。

いつチューニングを停止するかを知ることも重要です。パフォーマンスの最も優れた測定は、統計が理想的な値にどの程度近いかではなく、ユーザーの理解力です。

10.2 Oracle Database統計の解釈

インスタンスにパフォーマンスの問題があった時間範囲の統計を収集します。比較のためのベースライン・データをすでに収集してある場合は、問題のワークロードを最も代表するベースラインからのデータと、現行のデータを比較できます。

2つのレポートを比較する場合、それらのレポートが、システムを比較できるようなワークロードか確認してください。

10.2.1 負荷の検査

待機イベントは通常、最初に検査するデータです。ただし、ベースライン・レポートがある場合は、負荷が変化したかどうかをチェックします。ベースラインがあるかどうかにかかわらず、リソースの使用率が高いかどうかを確認すると便利です。

検査する負荷に関連する統計には、redo size、session logical reads、db block changes、physical reads、physical read total bytes、physical writes、physical write total bytes、parse count (total)、parse count (hard)およびuser callsがあります。このデータは、V$SYSSTATから問合せが行われます。秒ごとおよびトランザクションごとに、このデータを正規化することが最も有効です。また、physical read total bytesおよびphysical write total bytesの合計を使用して、1秒当たりの合計I/O負荷(MB)を調べることも有益です。合計値には、Recovery Manager(RMAN)バックアップおよびリカバリとOracle Databaseバックグラウンド・プロセスによる、バッファ・キャッシュ、REDOログ、アーカイブ・ログでのI/Oが含まれます。

AWRレポートの「ロード・プロファイル」セクションを参照してください。データは、トランザクションおよび秒ごとに正規化されています。

負荷の変更

秒ごとの負荷プロファイル統計は、スループットの変化(すなわち、インスタンスの作業実行量が毎秒ごとに増えているかどうか)を示します。トランザクションごとの統計は、アプリケーション特性の変化をベースライン・レポートからの対応する統計と比較することで識別します。

高いアクティビティ率

アクティビティ率が非常に高いかどうかを識別するには、秒ごとに正規化した統計を調べます。サイトごとにしきい値が異なり、アプリケーションの特性、CPUの個数と速度、オペレーティング・システム、I/OシステムおよびOracle Databaseのリリースに左右されるため、高い値に関する包括的な推奨事項を示すのは困難です。

次に、いくつかの一般化された例を示します(許容値は各サイトで異なります)。

  • 秒当たり100を超えるハード解析率は、非常に大量なハード解析がシステム上にあることを示します。高いハード解析率は重大なパフォーマンスの問題を発生させるので、調査する必要があります。通常は、高いハード解析率に共有プール上のラッチの競合とライブラリ・キャッシュ・ラッチが伴います。

  • ライブラリ・キャッシュおよび共有プール・ラッチ・イベント(latch: library cache、latch: library cache pin、latch: library cache lockおよびlatch: shared pool)の待機時間の合計が、V$SYSSTATに表示される統計のDB timeに比べて大きいかどうかを調べます。大きい場合は、AWRレポートのSQL ordered by Parse Callsセクションを調べます。

  • 高いソフト解析率は、秒当たり300以上の率になる可能性があります。不必要なソフト解析もアプリケーションのスケーラビリティを制限します。最適な方法として、SQL文をセッション当たり1回ソフト解析し、何回も実行します。

10.2.2 待機イベント統計を使用したボトルネックへのドリルダウン

Oracleプロセスは待機状態になると、一連の事前定義済待機イベントのいずれかを使用して待機状態を記録します。これらの待機イベントは、待機クラス別にグループ化されます。Idle待機クラスには、実行する作業がなく、さらに作業が実行されるのを待っている場合にプロセスが待機するイベントすべてがグループ化されます。アイドル状態でないイベントはリソースあるいはアクションが完了するまでの非生産的な待機時間を示します。

注意:

すべての症状が待機イベントによって証明されるわけではありません。チェックできる統計は、「追加された統計情報」を参照してください。

待機イベント・データを使用する最も効率的な方法は、待機時間別にイベントを順序付けすることです。この方法は、TIMED_STATISTICSがtrueに設定されているときのみ可能です。設定しない場合は、待機イベントを待機数別に順位付けします。これは、一般的に問題を最もよく表す順序付けではありません。

どこで時間が消費されているかが判明してから、次の手順を実行してください。

  1. V$SYSTEM_EVENTのデータ収集を調べます。対象のイベントは、待機時間別に順位付けする必要があります。

    待機時間の最も大きいパーセンテージを持つ待機イベントを識別します。待機時間のパーセンテージを決定するには、アイドル・イベント(Null event、SQL*Net message from client、SQL*Net message to client、SQL*Net more data to clientなど)を除くすべての待機イベントの合計待機時間を加算します。各イベントの待機時間をすべてのイベントの総待機時間で除算し、5つの最も重要なイベントの相対的なパーセンテージを計算します。

    別の方法として、自動ワークロード・リポジトリ・レポートの先頭の「トップ5待機イベント」の項を参照してください。この項では、待機イベント(アイドル・イベントを除く)を自動的に順序付けし、相対的なパーセンテージを計算します。

    Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                % Total
Event                                         Waits    Time (s) Call Time
-------------------------------------- ------------ ----------- ---------
CPU time                                                    559     88.80
log file parallel write                       2,181          28      4.42
SQL*Net more data from client               516,611          27      4.24
db file parallel write                       13,383          13      2.04
db file sequential read                         563           2       .27

    状況によっては、同程度のパーセンテージを持つイベントがいくつか存在する場合があります。このため、すべてのイベントが同じタイプのリソース・リクエスト(たとえば、すべてがI/O関連イベント)に関連している場合に、追加の証拠を提供できます。

  2. これらのイベントの待機数と平均待機時間を見てください。たとえば、I/O関連イベントの場合、平均時間がI/Oシステムが低速であるかどうかを識別するのに役立つ場合もあります。次に、AWRレポートの「待機イベント」セクションから引用した、このデータの例を示します。

                                                                 Avg
                                                Total Wait   wait     Waits
Event                           Waits  Timeouts   Time (s)   (ms)      /txn
--------------------------- --------- --------- ---------- ------ ---------
log file parallel write         2,181         0         28     13      41.2
SQL*Net more data from clie   516,611         0         27      0   9,747.4
db file parallel write         13,383         0         13      1     252.5

  3. トップの待機イベントは、次に調査する場所を識別します。表10-1に、一般的な待機イベントを示します。高負荷SQLを調べることもお薦めします。

  4. 待機イベントで指示される関連データを調べて、このデータから得られる他の情報を確認します。この情報が待機イベント・データとの一貫性を持っているかどうかを判断します。ほとんどの場合、パフォーマンス・ボトルネックの潜在的な原因に関する理論の展開を開始するためのデータは十分にあります。

  5. この理論が有効かどうかを確認するには、使用可能な他の統計で調べたデータをクロスチェックして一貫性のあることを確認します。適切な統計は問題により異なりますが、通常はV$SYSSTATやオペレーティング・システム統計などにある、ロード・プロファイル関連のデータが含まれています。他のデータとのクロスチェックを行って、展開中の理論を肯定または否定します。

関連項目:

10.2.3 待機イベントおよび潜在的な原因の表

表10-1に、待機イベントと考えられる原因との関連付けの他、次に検討するのに最も有益と思われるOracleデータの概要を示します。

表10-1 待機イベントおよび潜在的な原因

待機イベント 一般的な領域 考えられる原因 検索/調査

buffer busy waits

バッファ・キャッシュ、DBWR

バッファ・タイプによって異なります。たとえば、索引ブロックの待機は、昇順に基づく主キーが原因である場合があります。

問題が発生している間にV$SESSIONを調べ、競合したブロックのタイプを判別します。

free buffer waits

バッファ・キャッシュ、DBWR、I/O

低速なDBWR(おそらくI/Oに起因)

小さすぎるキャッシュ

オペレーティング・システム統計を使用して書込み時間を調べます。キャッシュが小さすぎることの証拠があるかどうかについてバッファ・キャッシュ統計をチェックします。

db file scattered read

I/O、SQL文のチューニング

チューニングが適切ではないSQL

低速なI/Oシステム

V$SQLAREAを調べて、多数のディスク読取りを実行するSQL文があるかどうかを確認します。I/OシステムとV$FILESTATをクロスチェックして、読取り時間に問題がないかをチェックします。

db file順次読取り

I/O、SQL文のチューニング

チューニングが適切ではないSQL

低速なI/Oシステム

V$SQLAREAを調べて、多数のディスク読取りを実行するSQL文があるかどうかを確認します。I/OシステムとV$FILESTATをクロスチェックして、読取り時間に問題がないかをチェックします。

enqueue待機(enq:で始まる待機)

ロック

エンキューのタイプにより異なる

V$ENQUEUE_STATを参照します。

ライブラリ・キャッシュ・ラッチ待機: library cache、library cache pinおよびlibrary cache lock

ラッチの競合

SQLの解析または共有

V$SQLAREAを調べて、比較的多数の解析コールまたは多数の子カーソルを使用するSQL文があるかどうかを確認します(VERSION_COUNT列)。V$SYSSTATの解析統計と毎秒の対応する割合を調べます。

log buffer space

ログ・バッファのI/O

小さいログ・バッファ

低速なI/Oシステム

V$SYSSTATの統計redo buffer allocation retriesをチェックします。メモリーの構成の章の、ログ・バッファの構成の項をチェックしてください。オンラインREDOログを格納するディスクをチェックして、リソースの競合の有無をチェックします。

log file sync

I/O、コミット過剰

オンライン・ログを格納する低速なディスク

バッチされないコミット

オンラインREDOログを格納するディスクをチェックして、リソースの競合の有無をチェックします。V$SYSSTATから毎秒のトランザクション数(コミット数+ロールバック数)をチェックします。

関連項目:

  • 表10-1に示した各イベントの詳細およびクロスチェックするその他の情報は、「待機イベント統計」を参照してください

  • 動的パフォーマンス・ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

  • buffer busy waits (34405.1)およびfree buffer waits (62172.1)に関するMy Oracle Supportの通知。これらの通知および関連通知には、My Oracle SupportのWebサイトで「busy buffer waits」と「free buffer waits」を検索してアクセスすることも可能です。

10.2.4 追加された統計情報

対応する待機イベントのない一部の統計にも、パフォーマンスの問題を示す情報が含まれる場合があります。

REDOログ領域リクエスト統計

V$SYSSTAT統計のredo log space requestsは、サーバー・プロセスが、REDOログ・バッファの領域ではなく、オンラインREDOログの領域を待機した回数を示します。この統計および待機イベントは、LGWRではなく、チェックポイント、DBWRまたはアーカイバ・アクティビティのチューニングが必要であることを示しています。ログ・バッファの容量を増加しても役に立ちません。

読取り一貫性

システムは、一貫したビューを維持するために、ブロックの変更内容のロールバックに長時間を費やすことがあります。次のシナリオを参考にしてください。

  • 多数の小さいトランザクションがあり、変化が起こっている同じ表のバックグラウンド内でアクティブな長時間実行問合せが動作している場合、表の一貫読取りイメージを取得するために、問合せはこれらの変化を頻繁にロールバックする必要がある場合があります。次のV$SYSSTAT統計を比較して、変化が発生しているかどうかを判断します。

    • consistentchanges: 統計は、ブロックの読取り一貫性を実施するために、データベース・ブロックにロールバック・エントリが適用される回数を示します。多数のconsistent changesを生成するワークロードは、多数のリソースを消費する可能性があります。

    • consistent gets: 統計は、一貫性モードでの論理読取り数をカウントします。

  • 大きいロールバック・セグメントがほとんどない場合、システムはどのシステム変更番号(SCN)のトランザクションがコミットされたかを正確に知るために、遅延ブロックのクリーンアウト時に長時間かけてトランザクション表をロールバックする可能性があります。Oracle Databaseでトランザクションをコミットしたとき、変更されたブロックすべてがコミットSCNで即時に更新されるとはかぎりません。この場合は、ブロックの読取り時または更新時に必要に応じて更新されます。これを遅延ブロック・クリーンアウトと呼びます。

    次のV$SYSSTAT統計の比率は、1に近い値であることが必要です。 

    ratio = transaction tables consistent reads - undo records applied /
        transaction tables consistent read rollbacks

    解決策は、自動UNDO管理を使用することをお薦めします。

  • ロールバック・セグメントが十分にない場合、ロールバック・セグメント(ヘッダーまたはブロック)の競合が発生します。この問題は、次のようにすると明らかになります。

    • WAITS数をV$ROLLSTAT内のGETS数と比較する方法。GETSに対するWAITSの比率は小さい値である必要があります。

    • V$WAITSTATを調べて、CLASS 'undo header'のバッファに対して多数のWAITSがあるかどうかを確認する方法。

    解決策は、自動UNDO管理を使用することをお薦めします。

継続行による表フェッチ

V$SYSSTAT内のtable fetch continued row統計数をチェックして、移行行または連鎖行を検出できます。少数の連鎖行(1%以下)は、システム・パフォーマンスに影響を与える可能性はほとんどありません。ただし、連鎖行のパーセンテージが大きいと、パフォーマンスに影響を与える可能性があります。

ブロック・サイズより大きい行の連鎖は避けられません。そのようなデータについては、ブロック・サイズのより大きい表領域の使用を考慮してください。

ただし、小さい行の場合は、適切な領域パラメータとアプリケーション設計を使用することで連鎖を回避できます。たとえば、キー値が入力され、かつその他のほとんどの列がNULLである行を挿入した後に、実際のデータで更新しないでください。その場合は初めからデータが入力された行を挿入します。

UPDATE文によって行のデータ量が増加し、行がそのデータ・ブロックに収まらなくなった場合、Oracle Databaseは行全体を保持するのに十分な空き領域を持つ別のブロックを見つけようとします。そのようなブロックが利用可能であれば、Oracle Databaseは新しいブロックへ行全体を移動します。この操作は、行の移行と呼ばれています。行が大きすぎて利用可能なブロックに収まらない場合、データベースはその行を複数の断片に分割し、各断片を別々のブロックに格納します。この操作は、行の連鎖と呼ばれています。データベースでは、行の挿入時にも行連鎖が発生する可能性があります。

移行と連鎖は、特に次の場合のパフォーマンスに影響があります。

  • 移行と連鎖の原因となるUPDATE文のパフォーマンスはよくありません。

  • 移行行または連鎖行が追加入出力を実行するため、これらの行を選択する問合せをします。

サンプルの出力表CHAINED_ROWSの定義が、配布媒体上の使用可能なSQLスクリプトに収録されています。このスクリプトの一般的な名前はUTLCHN1.SQLですが、正確な名前と位置は使用しているプラットフォームによって異なります。出力表の列名、データ型およびサイズは、CHAINED_ROWS表と同じである必要があります。

移行行を回避するには、PCTFREEを増やします。ブロック内に使用可能な空き領域を多く残しておくと、行の拡張に対処できます。削除割合が高い表と索引を再編成すなわち再作成することもできます。頻繁に行が削除される表の場合は、データ・ブロックに部分的に空き領域が生じることがあります。行を挿入し後から拡張する場合、行の削除されたブロックにその行が挿入されることがありますが、拡張の余地はありません。表を再編成すると主な空き領域を完全に空のブロックにできます。

注意:

PCTUSEDは、PCTFREEの反対の意味ではありません。

関連項目:

解析関連の統計

アプリケーションの解析が長くなるほど、競合の可能性が高くなり、システムの待機時間が長くなります。parse time CPU(CPU解析時間)がCPU時間の大半を占める場合、文の実行ではなく解析に時間が消費されています。この場合には、アプリケーションはリテラルSQLを使用しているためにSQLを共有できないか、または共有プールの構成が適切でないことがあります。

統計により、Oracleが解析に費やした時間の長さを識別することができます。V$SYSSTATから解析関連の統計の問合せを行います。次に例を示します。 

SELECT NAME, VALUE
  FROM V$SYSSTAT
 WHERE NAME IN (  'parse time cpu', 'parse time elapsed',
                  'parse count (hard)', 'CPU used by this session' );

様々な比率を計算することで、解析に問題があるかどうかを判断できます。

  • parse time CPU / parse time elapsed

    この比率は、解析に費やされる時間のうちのどのくらいが、ラッチなどのリソースに対する待機ではなく、解析操作自体によるものかを示します。比率1は良好であり、経過時間が競合率の高いリソースを待機するために費やされなかったことを示します。

  • parse time CPU / CPU used by this session

    この比率は、Oracleサーバー・プロセスで使用されるCPU全体のうちのどのくらいが解析関係の操作で費やされたかを示します。0(ゼロ)に近い値ほど良好であり、CPUの大部分が解析に費やされないことを示します。

10.3 待機イベント統計

V$SESSION、V$SESSION_WAIT、V$SESSION_HISTORY、V$SESSION_EVENTおよびV$SYSTEM_EVENTの各ビューは、どのようなリソースを待機したかに関する情報を表示し、構成パラメータTIMED_STATISTICSがtrueに設定されている場合は、各リソースを待機した時間に関する情報も表示されます。

関連項目:

パフォーマンス・チューニングを実行するときに、待機イベントと関連するタイミング・データを調査します。最大時間がリストされるイベントは、多くの場合、パフォーマンス・ボトルネックを顕著に示しています。

次の各ビューには、同じデータの関連する(ただし、異なる)ビューが含まれています。

  • V$SESSIONは、各現行セッションのセッション情報をリストします。このビューには、現在待機されているイベントか、各セッションで最後に待機されたイベントがリストされます。また、このビューには、ブロック・セッション、待機状態および待機時間に関する情報も含まれます。

  • V$SESSION_WAITは、現在の状態ビューです。このビューには、現在待機されているイベントか、各セッションで最後に待機されたイベント、待機状態および待機時間がリストされます。

  • V$SESSION_WAIT_HISTORYは、各現行セッションの最新10件の待機イベントと、関連する待機時間をリストします。

  • V$SESSION_EVENTは、各セッションで待機されるイベントの累積履歴をリストします。セッションが終了した後、そのセッションに対する待機イベント統計はこのビューから削除されます。

  • V$SYSTEM_EVENTは、インスタンスの起動以降にインスタンス全体により待機されるイベントと回数(すなわち、ロールアップされた、すべてのセッション待機イベント・データ)をリストします。

V$SESSION_WAITは現在の状態ビューであるため、V$SESSION_EVENTまたはV$SYSTEM_EVENTよりも細かい単位の情報も含まれています。このビューには、P1、P2およびP3の3つのパラメータ列があり、現行イベントの追加識別データが含まれます。

たとえば、V$SESSION_EVENTはセッション124(SID=124)がdb file scattered readで多く待機していたことを示すことはできますが、どのファイルとブロック番号かは示しません。ただし、V$SESSION_WAITはP1内のファイル番号、P2内に読み取られたブロック番号およびP3内に読み取られたブロック数を示します(P1とP2により待機イベントがどのセグメントに対して発生するかを判断できます)。

この項では、V$SESSION_WAITの使用例を中心に説明します。ただし、時間間隔のパフォーマンス・データの収集と、パフォーマンスおよび容量分析のためにこのデータを保存することをお薦めします。この形式のロールアップ・データの問合せは、AWRによりV$SYSTEM_EVENTビューで行います。

最も一般的に発生するイベントについては、この章で、大/小文字を区別するアルファベット順にリストして説明します。調べる対象の他のイベント関連データも含まれています。各イベント名に使用する大/小文字区別は、V$SYSTEM_EVENTビューでの表示と同一です。

10.3.1 過去のリリースからの待機イベント統計の変更

Oracle Database 11g以降、Oracle Databaseでは待機イベントの待機カウントおよびタイムアウトの累計が過去のリリースとは異なります(V$SYSTEM_EVENTビューなど)。特定のタイプのリソース(エンキューなど)に対する継続的な待機は、内部で短時間の待機コールのセットに分割されます。Oracle Database 11gより前のリリースでは、個々の内部の待機コールが、別々の待機としてカウントされていました。Oracle Database 11gから、待機中にセッションで発生した内部タイムアウトの回数にかかわらず、1つのリソースに対する待機は、1つの待機として記録されます。

Oracle Databaseでは、この変更により、待機カウントをより忠実に表現し、リソースの合計待機時間を正確に表示できます。タイムアウトは、個々の内部の待機コールではなく、リソースの待機を参照するようになりました。この変更は、平均待機時間および最大待機時間にも影響を与えます。たとえば、トランザクション行をロックして表の1行を更新するために、ユーザー・セッションがエンキューを待機する必要があり、そのエンキューを取得するのに10秒かかった場合、Oracle Databaseでは、エンキューの待機を3秒間の待機コールに分割します。この例では、3秒間の待機コールが3回と、1秒間の待機コールが1回です。ただし、セッションの観点からは、エンキューの待機は1回のみです。

Oracle Database 11gより前のリリースでは、この待機シナリオは、V$SYSTEM_EVENTビューで次のように表現されます。

  • TOTAL_WAITS: 待機回数4(3秒間の待機が3回、1秒間の待機が1回)

  • TOTAL_TIMEOUTS: タイムアウト回数3(最初の3回の待機はタイムアウトになり、最後の待機中にエンキューを取得)

  • TIME_WAITED: 10秒(4回の待機時間の合計)

  • AVERAGE_WAIT: 2.5秒

  • MAX_WAIT: 3秒

Oracle Database 11gから、この待機シナリオは次のように表現されます。

  • TOTAL_WAITS: 待機回数1(10秒間の待機が1回)

  • TOTAL_TIMEOUTS: タイムアウト回数0(リソースの待機中にエンキューを取得)

  • TIME_WAITED: 10秒(リソースを待機した時間)

  • AVERAGE_WAIT: 10秒

  • MAX_WAIT: 10秒

この変更の影響を受ける一般的な待機イベントは次のとおりです。

  • エンキュー待機(enq: name - reason待機など)

  • ライブラリ・キャッシュ・ロック待機

  • ライブラリ・キャッシュ・ピン待機

  • 行キャッシュ・ロック待機

この変更の影響を受ける統計情報は次のとおりです。

  • 待機回数

  • 待機のタイムアウト回数

  • 平均待機時間

  • 最大待機時間

この変更の影響を受けるビューは次のとおりです。

  • V$EVENT_HISTOGRAM

  • V$EVENTMETRIC

  • V$SERVICE_EVENT

  • V$SERVICE_WAIT_CLASS

  • V$SESSION_EVENT

  • V$SESSION_WAIT

  • V$SESSION_WAIT_CLASS

  • V$SESSION_WAIT_HISTORY

  • V$SYSTEM_EVENT

  • V$SYSTEM_WAIT_CLASS

  • V$WAITCLASSMETRIC

  • V$WAITCLASSMETRIC_HISTORY

関連項目:

V$SYSTEM_EVENTビューの詳細は、『Oracle Databaseリファレンス』を参照してください。

10.3.2 buffer busy waits

この待機は、複数のプロセスがバッファ・キャッシュ内のいくつかのバッファに同時にアクセスしようとしていることを示します。バッファのクラスごとに、待機統計についてV$WAITSTATを問い合せます。バッファ・ビジー待機を持つ一般的なバッファ・クラスには、data block、segment header、undo headerおよびundo blockがあります。

次のV$SESSION_WAITパラメータ列をチェックします。

  • P1: ファイルID

  • P2: ブロックID

  • P3: クラスID

原因

考えられる原因を判別するには、最初にV$SESSIONを問い合せて、セッションがbuffer busy waitsを待機しているときのROW_WAIT_OBJ#の値を識別します。次に例を示します。 

SELECT row_wait_obj# 
  FROM V$SESSION 
 WHERE EVENT = 'buffer busy waits';

競合対象のオブジェクトとオブジェクト型を識別するには、V$SESSIONから戻されるROW_WAIT_OBJ#の値を使用してDBA_OBJECTSを問い合せます。次に例を示します。 

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

処置

必要な処置は、競合対象のクラスと実際のセグメントにより異なります。

セグメント・ヘッダー

競合がセグメント・ヘッダー上にある場合、これは最も起こりうる空きリストの競合です。

ローカルに管理されている表領域で自動セグメント領域管理を行えば、PCTUSED、FREELISTSおよびFREELIST GROUPSの各パラメータを指定する必要はありません。可能であれば、手動領域管理から自動セグメント領域管理(ASSM)に切り替えます。

ASSMを使用できない場合(たとえば、表領域でディクショナリ領域管理を使用しているため)、これに関連する情報は次のとおりです。

空きリストは、通常セグメントの様々なエクステント内に存在するブロックを含む空きデータ・ブロックのリストです。空きリストは、空き領域がPCTFREEに達していないブロック、または使用済領域がPCTUSEDを下回っているブロックで構成されます。FREELISTSパラメータでプロセスの空きリスト数を指定します。FREELISTSのデフォルト値は1です。最大値はデータ・ブロック・サイズによって決まります。

そのセグメントの空きリストに対する現在の設定を見つけるには、次を実行します。

SELECT SEGMENT_NAME, FREELISTS
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = segment name
   AND SEGMENT_TYPE = segment type;

空きリスト、または空きリスト数の増分を設定します。さらに空きリストを追加しても問題が軽減されない場合は、空きリスト・グループを使用します(このようにすると、単一のインスタンス内でも違いが出る可能性があります)。Oracle RACを使用する場合、各インスタンスに独自の空きリスト・グループがあることを確認してください。

関連項目:

自動セグメント領域管理、空きリスト、PCTFREEおよびPCTUSEDの詳細は、『Oracle Database概要』を参照してください。

データ・ブロック

表または索引(セグメント・ヘッダーではない)に対する競合がある場合、次のようにします。

  • 昇順インデックスを調べます。これは、多数のプロセスによって同じ点に挿入される索引です。たとえば、キー値に順序番号ジェネレータを使用する索引をチェックしてください。

  • ASSMの使用またはグローバル・ハッシュ・パーティション索引の使用を検討します。また、複数のプロセスによる同一ブロックへの挿入を回避するために空きリストの増加も検討してください。

UNDOヘッダー

ロールバック・セグメント・ヘッダーに対する競合の場合

  • 自動UNDO管理を使用しない場合は、さらにロールバック・セグメントを追加してください。

UNDOブロック

ロールバック・セグメント・ブロックに対する競合の場合

  • 自動UNDO管理を使用しない場合は、ロールバック・セグメント・サイズを大きくすることを検討してください。

10.3.3 db file scattered read

このイベントは、ユーザー・プロセスがSGAバッファ・キャッシュにバッファを読み取り、物理I/Oコールが戻るまで待機することを意味します。db file scattered readは、データを複数の不連続メモリー位置に読み取るために散布読取りを発行します。散布読取りは通常、マルチブロック読取りです。全体スキャンの他、(索引の)高速全スキャンでも行うことができます。

db file scattered read待機イベントは、全体スキャンが発生していることを識別します。バッファ・キャッシュへの全体スキャンを実行すると、読み取られたブロックは物理的に相互に接近していないメモリー位置に読み取られます。このような読取りが散布読取りコールと呼ばれるのは、ブロックがメモリー全体に分散されているからです。対応する待機イベントが「db file scattered read」と呼ばれるのは、このためです。バッファ・キャッシュへの全体スキャンのためのマルチブロック(最大でDB_FILE_MULTIBLOCK_READ_COUNTブロック)読取りは、db file scattered readに対する待機として現れます。

次のV$SESSION_WAITパラメータ列をチェックします。

  • P1: 絶対ファイル番号

  • P2: 読み取られるブロック

  • P3: ブロック数(1より大きい値が必要)

処置

正常なシステムで、物理読取り待機はアイドル待機後の最大の待機になります。ただし、小さい索引付きアクセスを行う必要のある運用(OLTP)システム上に、直接読取り待機(パラレル問合せによる全表スキャンを意味する)またはdb file scattered read待機があるかどうかも確認してください。

システム上の過剰なI/O負荷を示す他の要素として、次のものがあります。

  • 低いバッファ・キャッシュ・ヒット率

  • ユーザーへのレスポンス時間を長くしている待機時間のほとんどを発生させている待機イベント

過剰なI/Oの管理

過剰なI/O待機には、様々な対処方法があります。有効性の順に示すと、これらの方法は次のとおりです。

  • SQLチューニングによるI/Oアクティビティの削減。

  • ワークロードの管理による、I/Oを実行する必要性の削減。

  • DBMS_STATSパッケージを使用したシステム統計の収集。問合せオプティマイザによる、全体スキャンを使用する可能性のあるアクセス・パスのコストの正確な見積りが可能になります。

  • 自動ストレージ管理の使用。

  • さらに多くのディスクを追加することによる、各ディスクのI/O数の削減。

  • 既存のディスク間へのI/Oの再分散によるI/Oホット・スポットの削減。

最初に行うことは、I/Oを削減するためのチャンスを見つけることです。これらのイベントを待機するセッションによって実行されるSQL文と、V$SQLAREAから多数の物理I/Oを実行する文を調査します。過剰なI/Oを実行し、実行計画にマイナスの影響を与える可能性のある要因として、次のものがあります。

  • 不適切に最適化されたSQL

  • 索引の欠落

  • 表の高度な並列度(スキャン方向へオプティマイザを偏らせる)

  • オプティマイザの正確な統計がないこと

  • DB_FILE_MULTIBLOCK_READ_COUNT初期化パラメータの設定値が全体スキャンには大きすぎること

不十分なI/O分散

I/Oを削減する他、ディスク間のファイルのI/O分散も調べます。I/Oはディスク間に均等に分散されていますか、あるいは、いくつかのディスク上にホット・スポットがありますか。データベースのI/Oリクエストを満たすだけの十分な個数のディスクがありますか。

データベースによるI/O操作(読取りと書込み)の総数を調べ、それと使用したディスク数を比較します。必ず、LGWRプロセスとARCHプロセスのI/Oアクティビティを含めてください。

I/Oを待機しているセッションで実行されたSQL文の検索

次の問合せを使用して、ある時点でどのセッションがI/Oを待機しているかを判断します。 

SELECT SQL_ADDRESS, SQL_HASH_VALUE
  FROM V$SESSION 
 WHERE EVENT LIKE 'db file%read';

I/Oを必要とするオブジェクトの検索

考えられる原因を判別するには、最初にV$SESSIONを問い合せて、セッションがdb file scattered readを待機しているときのROW_WAIT_OBJ#の値を識別します。次に例を示します。 

SELECT row_wait_obj# 
  FROM V$SESSION 
 WHERE EVENT = 'db file scattered read';

競合対象のオブジェクトとオブジェクト型を識別するには、V$SESSIONから戻されるROW_WAIT_OBJ#の値を使用してDBA_OBJECTSを問い合せます。次に例を示します。 

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

10.3.4 db file順次読取り

このイベントは、ユーザー・プロセスがSGA内のバッファ・キャッシュにバッファを読み取り、物理I/Oコールが戻るまで待機することを意味します。順次読取りは、単一ブロック読取りです。

単一ブロックI/Oは通常、索引を使用した結果です。非常にまれなケースとして、エクステントの境界のため、またはバッファ・キャッシュ内にバッファが存在するため、全表スキャン・コールが単一ブロック・コールに切り捨てられることがあります。これらの待機もdb file sequential readとして現れます。

次のV$SESSION_WAITパラメータ列をチェックします。

  • P1: 絶対ファイル番号

  • P2: 読み取られるブロック

  • P3: ブロック数(値は1)

    関連項目:

    過剰I/Oの管理、不適切なI/O分散、さらにI/Oを発生させるSQLおよびI/Oが実行されるセグメントの検索の詳細は、「db file scattered read」を参照してください。

処置

正常なシステムで、物理読取り待機はアイドル待機後の最大の待機になります。ただし、パラレル問合せによる全表スキャンを多く実行する必要のある大規模なデータ・ウェアハウスに対するdb file sequential readsがあるかどうかも確認してください。

次の図は、これらの待機イベントの相違を示しています。

  • db file sequential read(1つのSGAバッファに読み取られる単一ブロック)

  • db file scattered read(多数の不連続SGAバッファに読み取られるマルチブロック)

  • direct read(SGAをバイパスして、PGAに読み取られる単一またはマルチブロック)

図10-1 散布読取り、順次読取りおよびダイレクト・パス読取り

図10-1の説明が続きます
「図10-1 散布読取り、順次読取りおよびダイレクト・パス読取り」の説明

10.3.5 direct path readおよびdirect path read temp

SGAのバッファ・キャッシュではなく、ディスクからPGAに直接バッファの読取りを実行しているセッションは、このイベントで待機します。I/Oサブシステムが非同期I/Oをサポートしない場合、各待機は物理読取りリクエストに対応します。

I/Oサブシステムが非同期I/Oをサポートする場合、このプロセスでは読取りリクエストの発行を、PGAに存在するブロックの処理に重複させることができます。プロセスがディスクからまだ読み取られていないPGA内のブロックにアクセスしようとする場合、待機コールを発行し、このイベントの統計を更新します。したがって、待機数は必ずしも読取りリクエスト数と同じではありません(db file scattered readおよびdb file sequential readとは異なります)。

次のV$SESSION_WAITパラメータ列をチェックします。

  • P1: 読取りコールのFile_id

  • P2: 読取りコールのStart block_id

  • P3: 読取りコールのブロック数

原因

これは次の状況で発生します。

  • ソートが大きすぎてメモリーに収まらないため、ソート・データの一部がディスクに直接書き出される。そのデータは、直接読取りで後から再度読み取られます。

  • データのスキャンにパラレル実行サーバーが使用される。

  • サーバー・プロセスが、I/Oシステムがバッファを戻すより早くバッファを処理する。これは過負荷のI/Oシステムを示します。

処置

file_idにより、読取りがTEMP表領域内のオブジェクトのためのもの(ディスクへのソート)か、パラレル実行サーバーによる全表スキャンであるかが示されます。この待機は、大規模なデータ・ウェアハウス・サイトでは最大の待機です。ただし、ワークロードが意思決定支援システム(DSS)ワークロードでない場合は、この状況が発生した理由を調査してください。

ディスクへのソート

待機が発生しているセッションで、現在実行されているSQL文を調べて、ソートの原因を確認します。ソートを生成するSQL文を検索するために、V$TEMPSEG_USAGEを問い合せます。また、ソートのサイズを決定するセッションに関するV$SESSTATからの統計を問い合せます。SQL文をチューニングしてソートを削減できるかどうかを確認します。WORKAREA_SIZE_POLICYがMANUALである場合、システム(ソートが大きすぎない場合)または個々のプロセスのSORT_AREA_SIZEを大きくすることを検討します。WORKAREA_SIZE_POLICYがAUTOである場合、PGA_AGGREGATE_TARGETを大きくするかどうかを調べます。

全表スキャン

高い並列度で表を定義すると、パラレル実行サーバーによる全表スキャンを行うようにオプティマイザが偏る可能性があります。ダイレクト・パス読取りを使用して読み取るオブジェクトをチェックします。全表スキャンが有効なワークロード部分である場合は、I/Oサブシステムが並列度に対して適切かどうかを確認します。ディスクのストライプ化またはOracle Automatic Storage Management(Oracle ASM)を使用していない場合は、ディスクのストライプ化を使用することを検討してください。

ハッシュ領域サイズ

ハッシュ結合を呼び出す問合せ計画の場合、過剰なI/OはHASH_AREA_SIZEが小さすぎることから発生する可能性があります。WORKAREA_SIZE_POLICYがMANUALである場合、システムまたは個々のプロセスのHASH_AREA_SIZEを大きくすることを検討してください。WORKAREA_SIZE_POLICYがAUTOである場合、PGA_AGGREGATE_TARGETを大きくするかどうかを調べます。

関連項目:

10.3.6 direct path writeおよびdirect path write temp

プロセスがバッファをPGAから直接書き込む(バッファ・キャッシュからバッファを書き込むDBWRとは反対)場合、プロセスは書込みコールが完了するまでこのイベント上で待機します。ダイレクト・パス書込みを実行する可能性のある操作には、ディスクでのソート、パラレルDML操作、ダイレクト・パスINSERT、パラレルCREATE TABLE AS SELECT処理、およびいくつかのLOB操作があります。

ダイレクト・パス読取りと同様に、I/Oサブシステムが非同期書込みをサポートする場合、待機数は発行された書込みコール数と同じではありません。セッションがPGA内のすべてのバッファを処理し、I/Oリクエストが完了するまで処理を継続できない場合、セッションは待機します。

関連項目:

ダイレクト・パス・インサートの詳細は、『Oracle Database管理者ガイド』を参照してください。

次のV$SESSION_WAITパラメータ列をチェックします。

  • P1: 書込みコールのFile_id

  • P2: 書込みコールのStart block_id

  • P3: 書込みコール内のブロック数

原因

これは次の状況で発生します。

  • ソートが大きすぎ、メモリー内に収まらないため、ディスクに書き込まれる。

  • オブジェクトを作成/移入するために、パラレルDMLが発行される。

  • ダイレクト・パス・ロード

処置

大規模なソートは、「ディスクへのソート」を参照してください。

パラレルDMLについては、ディスク間のI/O分散をチェックし、I/Oサブシステムが並列度に対して適切に構成されているかどうかを確認してください。

10.3.7 enqueue (enq:)待機

エンキューは、データベース・リソースへのアクセスを調整するロックです。このイベントは、セッションが別のセッションで保持されているロックを待機していることを示します。

エンキュー名は待機イベント名の一部としてenq: enqueue_type - related_details形式で含まれています。次の関連TXタイプなど、同じエンキュー・タイプを異なる目的で保持できる場合があります。

  • enq: TX - allocate ITL entry

  • enq: TX - contention

  • enq: TX - index contention

  • enq: TX - row lock contention

V$EVENT_NAMEビューには、すべてのenq:待機イベントのリストが表示されます。

次のV$SESSION_WAITパラメータ列で追加情報を確認できます。

  • P1: ロックのTYPE(または名前)およびMODE

  • P2: ロックのリソース識別子ID1

  • P3: ロックのリソース識別子ID2

関連項目:

Oracle Databaseエンキューの詳細は、『Oracle Databaseリファレンス』を参照してください

ロックおよびロック・ホルダーの検索

ロックを保持するセッションを見つけるには、V$LOCKの問合せを行います。イベント・エンキューを待機するすべてのセッションでは、REQUEST <> 0を持つV$LOCK内に行があります。次の2つの問合せのうちの1つを使用して、ロックを保持しているセッションとロックを待機しているセッションを検索します。

エンキュー待機がある場合は、次の文を使用することによりこれらを確認できます。

SELECT * FROM V$LOCK WHERE request > 0;

待機中のロックのホルダーおよびウェイタのみを表示するには、次の文を使用します。 

SELECT DECODE(request,0,'Holder: ','Waiter: ') || 
          sid sess, id1, id2, lmode, request, type
   FROM V$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   ORDER BY id1, request;

処置

適切な処置は、エンキューのタイプにより異なります。

競合されたエンキューがSTエンキューである場合、問題は動的な領域割当てにある可能性が最も高いと言えます。セグメントにそれ以上空き領域がない場合、Oracle Databaseはセグメントにエクステントを動的に割り当てます。このエンキューは、ディクショナリ管理表領域にのみ使用します。

このリソースに対する競合を解決するには、次のようにします。

  • 一時(すなわち、ソート)表領域がTEMPFILESを使用するかどうかを確認します。使用しない場合は、TEMPFILESを使用するように切り替えます。

  • 動的に拡張するセグメントを含む表領域がディクショナリ管理表領域の場合は、ローカル管理表領域を使用するように切り替えます。

  • ローカル管理表領域に切り替えることができない場合は、拡張するオブジェクトの次のエクステント・サイズを、一定の領域割当てを回避できる十分な大きさに変更することによって、STエンキュー・リソースの使用量を減らすことができます。どのセグメントが常に拡張するかを判断するには、すべてのSEGMENT_NAMEについてDBA_SEGMENTSビューのEXTENTS列を監視します。

  • ALTER TABLE ALLOCATE EXTENT SQL文でエクステントを割り当てるなどして、セグメント内の領域を事前に割り当てます。

関連項目:

HWエンキューは、セグメントの最高水位標を超える領域の割当てをシリアライズする場合に使用します。

  • V$SESSION_WAIT.P2 / V$LOCK.ID1は表領域番号です。

  • V$SESSION_WAIT.P3 / V$LOCK.ID2は、領域が割り当てられるオブジェクトのセグメント・ヘッダーの相対データ・ブロック・アドレス(DBA)です。

これがオブジェクトの競合点である場合、エクステントの手動割当てにより問題が解決されます。

TMロック上の待機の最も一般的な理由は、制約される列が索引付けされない場合の外部キー制約に関係している傾向があります。この問題を回避するには、外部キー列を索引付けします。

これらのロックは、トランザクションがその最初の変更を開始するときに排他的に取得され、トランザクションがCOMMITまたはROLLBACKを行うまで保持されます。

  • モード6のTXの待機は、あるセッションが別のセッションによって保持されている行レベル・ロックを待機しているときに発生します。これは、あるユーザーがある行を更新または削除し、別のセッションがその行を更新または削除する場合に発生します。このタイプのTXエンキュー待機は、待機イベントenq: TX - row lock contentionに対応します。

    解決方法は、ロックを保持している最初のセッションにCOMMITまたはROLLBACKを実行させることです。

  • モード4のTXの待機は、セッションがブロック内でITL(必要なトランザクション・リスト)スロットを待機している場合に発生する可能性があります。これは、セッションがそのブロック内の行をロックしても、1つ以上の他のセッションの行が同じブロックでロックされ、そのブロック内に空きITLスロットがない場合に発生します。通常は、Oracle Databaseが別のITLスロットを動的に追加します。この操作は、ITLを追加するためのブロック内の空き領域が十分にない場合には実行できません。空き領域が十分にある場合、セッションはモード4でTXエンキューを持つスロットを待機します。このタイプのTXエンキュー待機は、enq: TX - allocate ITL entryに対応します。

    解決方法は、表のINITRANSまたはMAXTRANSを変更する(ALTER文を使用するか、それより高い値で表を再作成する)ことによって使用可能なITLの個数を増やすことです。

  • モード4のTXの待機は、セッションがUNIQUE索引内の潜在的な重複のためにセッションが待機している場合にも発生します。2つのセッションが同じキー値を挿入しようとする場合、第2のセッションはORA-0001が発生したかどうかを確認するまで、待機する必要があります。このタイプのTXエンキュー待機は、待機イベントenq: TX - row lock contentionに対応します。

    解決方法は、ロックを保持している最初のセッションにCOMMITまたはROLLBACKを実行させることです。

  • モード4のTXの待機は、共有ビットマップ・インデックス・フラグメントのためにセッションが待機している場合にも発生する可能性があります。ビットマップ索引では、キー値およびROWID範囲が索引付けされます。ビットマップ索引の各エントリには実際の表の行を数多く含めることができます。2つのセッションが同じビットマップ索引フラグメントでカバーされる行を更新する場合、第2のセッションは、モード4でTXロックを待機して、第1のトランザクションのCOMMITまたはROLLBACKを待機します。このタイプのTXエンキュー待機は、待機イベントenq: TX - row lock contentionに対応します。

  • モード4でのTXの待機は、PREPAREDトランザクションを待機している場合にも発生する可能性があります。

  • モード4のTXの待機は、索引に1行を挿入するトランザクションが、他のトランザクションによる索引ブロック分割の終了を待つ必要がある場合にも発生します。このタイプのTXエンキュー待機は、待機イベントenq: TX - index contentionに対応します。

関連項目:

参照整合性およびデータの明示的ロックの詳細は、『Oracle Database開発ガイド』を参照してください

10.3.8 Other待機クラスのイベント

このイベントは、「その他」の待機クラスに属し、通常はシステムで発生しないようにしてください。このイベントは、latch freeなど、「その他」の待機クラスのその他すべてのイベントの集計であり、V$SESSION_EVENTおよびV$SERVICE_EVENTビューでのみ使用されます。これらのビューでは、「その他」待機クラスのイベントは、セッションごとにはメンテナンスされません。かわりに、この1つのイベントにロール・アップされ、「その他」待機クラスのイベント統計をメンテナンスするためのメモリーを削減します。

10.3.9 free buffer waits

この待機イベントは、サーバー・プロセスが空きバッファを検索できず、使用済バッファを書き出すことによって空きバッファを作成するためにデータベース・ライターを転送したことを示します。使用済バッファは、内容が変更されたバッファです。使用済バッファは、DBWRがブロックをディスクに書き込み終えると、再利用するために解放されます。

原因

DBWRは、次の状況では、使用済バッファの書込みに対応できません。

  • I/Oシステムが低速である。

  • ラッチなど、空くまで待機しているリソースがある。

  • バッファ・キャッシュが小さすぎるため、DBWRはサーバー・プロセスのバッファのクリーニングに大部分の時間を費やす。

  • バッファ・キャッシュが大きすぎるため、1つのDBWRプロセスでは、リクエストの対応に十分なだけキャッシュ内のバッファを解放できない。

処置

このイベントが頻繁に発生する場合は、DBWRに対するセッション待機を調べて、DBWRを遅らせる原因があるかどうかを確認してください。

セッションが書込みを待機している場合は、書込みを遅らせている原因を解明し、修正してください。次の点をチェックします。

  • V$FILESTATを調べて、書込みの大半が発生する場所を確認してください。

  • I/Oシステムのホスト・オペレーティング・システム統計を調べてください。書込み時間は許容できるものですか。

I/Oが低速である場合は、次のようにしてください。

  • さらに高速なI/O手段を使用して書込み時間を高速化することを検討してください。

  • 多数のスピンドル(ディスク)とコントローラの間にI/Oアクティビティを拡散してください。

キャッシュが小さすぎるためにDBWRが非常にアクティブである可能性があります。バッファ・キャッシュ・ヒット率が低いかどうかを確認して、これが考えられる原因であるかどうかを調べます。また、V$DB_CACHE_ADVICEビューを使用して、それより大きいキャッシュ・サイズが有利かどうかを判断します。

キャッシュ・サイズが適切であり、I/Oが均等に分散されている場合は、非同期I/Oを使用するか、複数のデータベース・ライターを使用して、DBWRの動作を修正できます。

複数のデータベース・ライター(DBWR)・プロセスまたはI/Oスレーブの検討

複数のデータベース・ライター・プロセスを構成したり、I/Oスレーブを使用するのは、トランザクション・レートが高い場合や、バッファ・キャッシュ・サイズが大きすぎて単一のDBWnプロセスが負荷に耐えられない場合に役立ちます。

DB_WRITER_PROCESSES初期化パラメータを使用すると、複数のデータベース・ライター・プロセス(DBW0からDBW9までと、DBWaからDBWj)を構成できます。複数のDBWRプロセスを構成すると、書き込まれるバッファの識別に必要な作業が分散され、また、これらのプロセス間にI/O負荷もが分散されます。複数のデータベース・ライター・プロセスは、複数のCPUを持つシステム(CPU 8つにつき最低1つのデータベース・ライター)や、複数のプロセッサ・グループを持つシステム(最低でプロセス・グループと同数のデータベース・ライター)にお薦めします。

Oracle Databaseでは、CPU数またはプロセッサ・グループ数に基づいて、DB_WRITER_PROCESSESに適切なデフォルト設定を選択するか、ユーザー定義の設定を調整します。

複数のDBWRプロセスを使用することが実用的ではない場合、Oracle Databaseには複数のスレーブ・プロセス間にI/O負荷を分散する機能があります。DBWRプロセスは、ブロックを書き出すためにバッファ・キャッシュLRUリストをスキャンする唯一のプロセスです。ただし、これらのブロックのI/OはI/Oスレーブで実行されます。I/Oスレーブの個数は、DBWR_IO_SLAVESパラメータで決定されます。

DBWR_IO_SLAVESは、(CPUが1つの場合など)複数のDB_WRITER_PROCESSESを使用できない場合を想定しています。I/Oスレーブは、非同期I/Oが使用できないときにも役立ちます。これは、書き込まれるキャッシュ内のブロックの識別を継続するためにDBWRを解放することによって、複数のI/Oスレーブが非同期の非ブロック・リクエストをシミュレートするためです。一般的に、非同期I/Oがある場合、オペレーティング・システム・レベルの非同期I/Oをお薦めします。

DBWRのI/Oスレーブは、初回のI/Oリクエストが実行されるときに、データベースが開いた直後に割り当てられます。DBWRは、I/Oの実行とは別に、DBWR関連のすべての作業の実行を継続します。I/Oスレーブは単に、DBWRのためにI/Oを実行します。バッチの書込みはI/Oスレーブ間でパラレル化されます。

注意:

DBWR_IO_SLAVESを実装するには、余分な共有メモリーをI/Oバッファとリクエスト・キューに割り当てる必要があります。複数のDBWRプロセスをI/Oスレーブと併用することはできません。I/Oスレーブを構成すると、1つのDBWRプロセスのみ起動するように設定されます。

複数のDBWRプロセスを構成すると、単一のDBWRプロセスでは必要なワークロードに耐えられない場合のパフォーマンスに役立ちます。ただし、複数のDBWRプロセスを構成する前に、非同期I/Oが使用でき、システム上に構成されるかどうかを確認します。システムが非同期I/Oをサポートしても現在使用されていない場合は、これにより問題が軽減されるかどうかを確認するために非同期I/Oを使用可能にします。システムが非同期I/Oをサポートしない場合、または非同期I/Oが構成され、DBWRボトルネックが依然として存在する場合は、複数のDBWRプロセスを構成します。

注意:

プラットフォーム上で非同期I/Oを使用できない場合は、DISK_ASYNCH_IO初期化パラメータをFALSEに設定して非同期I/Oを無効にできます。

複数のDBWRを使用すると、バッファの収集と書込みがパラレル化されます。そのため、複数のDBWnプロセスは同じ数のI/Oスレーブを使用する1つのDBWRプロセスよりもスループットを向上します。このため、複数のDBWRプロセスを優先して、I/Oスレーブの使用は非推奨になりました。I/Oスレーブは、複数のDBWRプロセスを構成できない場合のみ使用してください。

10.3.10 アイドル待機イベント

これらのイベントはIdle待機クラスに属し、作業がないためにサーバー・プロセスが待機中であることを示します。これは通常、ボトルネックが存在する場合にボトルネックがデータベース・リソースに対するものではないことを意味します。チューニングの際、アイドル・イベントの大部分を無視することが必要なのは、アイドル・イベントがパフォーマンス・ボトルネックの性質を示さないためです。アイドル・イベントの中には、ボトルネックでないものを示す際に役立つものがあります。このタイプのイベントの例として、最も一般的に発生するアイドル待機イベントであるSQL Net message from clientがあります。表10-2に、このアイドル・イベントと他のアイドル・イベント(およびそれらのカテゴリ)のリストを示します。

表10-2 アイドル待機イベント

待機名 バックグラウンド・プロセス・アイドル・イベント ユーザー・プロセス・アイドル・イベント パラレル問合せアイドル・イベント 共有サーバー・アイドル・イベント Oracle Real Application Clustersアイドル・イベント

dispatcher timer

.

.

.

X

.

pipe get

.

X

.

.

.

pmon timer

X

.

.

.

.

PX Idle Wait

.

.

X

.

.

PX Deq Credit: need buffer

.

.

X

.

.

rdbms ipc message

X

.

.

.

.

shared server idle wait

.

.

.

X

.

smon timer

X

.

.

.

.

SQL*Net message from client

.

X

.

.

.

関連項目:

各アイドル待機イベントの説明は、『Oracle Databaseリファレンス』を参照してください。

10.3.11 ラッチ・イベント

ラッチは、メモリー構造を保護するためにOracle Databaseで使用される下位レベルの内部ロックです。ラッチ解放イベントは、サーバー・プロセスがラッチの取得を試み、最初の試行でラッチを取得できないときに更新されます。

通常は大きな競合を生成する一般的なラッチ用に、専用のラッチ関連待機イベントがあります。これらのイベントの場合は、latch: library cacheまたはlatch: cache buffers chainsのように、ラッチ名が待機イベント名に含まれます。このため、ラッチに関連するほとんどの競合の原因が特定のタイプのラッチであるかどうかをすばやく確認できます。他のすべてのラッチの待機は、汎用のlatch free待機イベントにグループ化されています。

関連項目:

ラッチおよび内部ロックの詳細は、『Oracle Database概要』を参照してください。

処置

このイベントは、ラッチ待機がシステム上の待機時間全体の重要な部分である場合か、または問題が発生している個々のユーザーに対してのみかを考慮してください。

  • 関連するリソースのリソース使用量を調べます。たとえば、ライブラリ・キャッシュ・ラッチの競合が大きい場合は、ハードとソフトの解析率を調べます。

  • ラッチ競合が発生しているセッションのSQL文を調べて、共通性があるかどうかを確認してください。

次のV$SESSION_WAITパラメータ列をチェックします。

  • P1: ラッチのアドレス

  • P2: ラッチ番号

  • P3: ラッチの待機中にプロセスがスリープした回数

例: 現在待機されているラッチの検索

SELECT EVENT, SUM(P3) SLEEPS, SUM(SECONDS_IN_WAIT) SECONDS_IN_WAIT
  FROM V$SESSION_WAIT
 WHERE EVENT LIKE 'latch%'
  GROUP BY EVENT;

前述の問合せには、インスタンスまたは長期的なインスタンスのチューニングよりも、セッションのチューニングまたは短期的なインスタンスのチューニングに関して多くの情報が示されるという問題があります。

次の問合せは長期的なインスタンス・チューニングの詳細情報を提供し、ラッチの待機がデータベース全体の時間に占める割合が大きいかどうかを示します。

SELECT EVENT, TIME_WAITED_MICRO, 
       ROUND(TIME_WAITED_MICRO*100/S.DBTIME,1) PCT_DB_TIME 
  FROM V$SYSTEM_EVENT, 
   (SELECT VALUE DBTIME FROM V$SYS_TIME_MODEL WHERE STAT_NAME = 'DB time') S
 WHERE EVENT LIKE 'latch%'
 ORDER BY PCT_DB_TIME ASC;

ラッチ待機固有でない、より汎用的な問合せは次のようになります。

SELECT EVENT, WAIT_CLASS, 
      TIME_WAITED_MICRO,ROUND(TIME_WAITED_MICRO*100/S.DBTIME,1) PCT_DB_TIME
  FROM V$SYSTEM_EVENT E, V$EVENT_NAME N,
    (SELECT VALUE DBTIME FROM V$SYS_TIME_MODEL WHERE STAT_NAME = 'DB time') S
   WHERE E.EVENT_ID = N.EVENT_ID
    AND N.WAIT_CLASS NOT IN ('Idle', 'System I/O')
  ORDER BY PCT_DB_TIME ASC;

表10-3 ラッチ待機イベント

ラッチ SGA領域 考えられる原因 検索対象

共有プール、ライブラリ・キャッシュ

共有プール

文の再利用不足

バインド変数を使用しない文

アプリケーション・カーソル・キャッシュのサイズが不十分

各実行後に明示的にクローズされたカーソル

頻繁なログインとログオフ

基礎的なオブジェクト構造の変更(たとえば、切捨て)

小さすぎる共有プール

次の項目が高いセッション(V$SESSTAT内)

  • CPU解析時間

  • 所要解析時間

  • 解析数(ハード) / 実行数の比率

  • 解析数(合計) / 実行数の比率

次のカーソル(V$SQLAREA/V$SQLSTATS内)

  • PARSE_CALLS / EXECUTIONSの高い比率

  • EXECUTIONS = 1 WHERE句のリテラルのみ異なる(すなわち、バインド変数を使用しない)

  • 高いRELOADS

  • 高いINVALIDATIONS

  • 大きい(> 1MB)SHARABLE_MEM

キャッシュ・バッファLRU連鎖

バッファ・キャッシュLRUリスト

過剰なバッファ・キャッシュ・スループット。たとえば、正しくない索引に繰り返しアクセスする非効率的なSQL(大きい索引レンジ・スキャン)、または多くの全表スキャンがあります。

実行中のワークロードに耐えられないDBWR。これにより、フォアグラウンド・プロセスが空きバッファを検索するためにラッチを保持する時間が長くなります。

小さすぎるキャッシュ

論理I/Oまたは物理I/Oが非常に多く、選択性のない索引が使用される文

キャッシュ・バッファ連鎖

バッファ・キャッシュ

ホット・ブロックと呼ばれる1つ(または少数)のブロックへのアクセスの繰返し

順序番号ジェネレータを使用せずに番号を生成するための、表の行を更新する順序番号生成コード

非常に多くのプロセスが、きわめて類似した述語を使用して選択性のない同一の索引をスキャンすることから発生する、索引リーフ・ブロックの競合

ホット・ブロックが属するセグメントの識別

行キャッシュ・オブジェクト

      

共有プールとライブラリ・キャッシュ・ラッチの競合

共有プールまたはライブラリ・キャッシュ・ラッチの競合の主な原因は解析です。不要な解析およびそのタイプは、いくつかの方法によって識別できます。

この方法では、リテラルがバインド変数と置換された場合に共有できる類似したSQL文を識別します。その概念は次のいずれかです。

  • 実行を1つのみ持つSQL文を手動で検査して、SQL文が類似しているかどうかを確認します。

    SELECT SQL_TEXT
  FROM V$SQLSTATS
 WHERE EXECUTIONS < 4
 ORDER BY SQL_TEXT;

  • または、類似した文をグループ化することによって、このプロセスを自動化します。類似したSQL文のバイト数を見積り、そのバイト数によってSQL文をグループ化します。たとえば、次の例では、最初の60バイト以降のみが異なる文をグループ化します。

    SELECT SUBSTR(SQL_TEXT, 1, 60), COUNT(*)
  FROM V$SQLSTATS
 WHERE EXECUTIONS < 4 
 GROUP BY SUBSTR(SQL_TEXT, 1, 60)
 HAVING COUNT(*) > 1;

  • 同じ実行計画を持つ個別のSQL文をレポートします。次の問合せでは、同じ実行計画を4回以上共有する個別のSQL文が選択されます。この種のSQL文では、バインド変数ではなくリテラルが使用されている可能性があります。

    SELECT SQL_TEXT FROM V$SQLSTATS WHERE PLAN_HASH_VALUE IN
  (SELECT PLAN_HASH_VALUE 
     FROM V$SQLSTATS 
    GROUP BY PLAN_HASH_VALUE HAVING COUNT(*) > 4)
  ORDER BY PLAN_HASH_VALUE;

V$SQLSTATSビューをチェックします。次の問合せを入力します。 

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS 
  FROM V$SQLSTATS
ORDER BY PARSE_CALLS;

PARSE_CALLSの値が指定の文のEXECUTIONS値に近い場合は、その文の再解析を継続できます。解析コールが多い文をチューニングします。

不要な解析コールが発生しているセッションを識別して、不要なコールを識別します。特定のバッチ・プログラムやある種のアプリケーションがほとんどの再解析を行っている場合があります。この目的を達成するには、次の問合せを実行します。

SELECT pa.SID, pa.VALUE "Hard Parses", ex.VALUE "Execute Count" 
  FROM V$SESSTAT pa, V$SESSTAT ex 
 WHERE pa.SID = ex.SID 
   AND pa.STATISTIC#=(SELECT STATISTIC# 
       FROM V$STATNAME WHERE NAME = 'parse count (hard)') 
   AND ex.STATISTIC#=(SELECT STATISTIC# 
       FROM V$STATNAME WHERE NAME = 'execute count') 
   AND pa.VALUE > 0;

結果として、すべてのセッションのリストおよびセッションで実行された解析の量が得られます。セッション識別子(SID)ごとに、V$SESSIONで、再解析の原因となっているプログラムの名前を検索します。

注意:

この問合せではインスタンス起動後のすべての解析コールがカウントされるため、解析率の高いセッションを検索することをお薦めします。たとえば、50日間の接続が高い解析値を示すとしても、2番目の10分間の接続の方がより速い速度で解析される場合があります。

出力は、次のようなものです。

   SID  Hard Parses  Execute Count
------  -----------  -------------
     7            1             20
     8            3          12690
     6           26            325
    11           84           1619

cache buffers lru chainのラッチは、キャッシュ内のバッファのリストを保護します。リストからバッファの追加、移動または削除を行う場合、ラッチを取得する必要があります。

対称型マルチプロセッサ(SMP)システムでは、Oracle Databaseによって、LRUラッチの数がシステムのCPU数の2分の1に等しい値になるように自動的に設定されます。非SMPシステムでは、LRUラッチは1つあれば十分です。

LRUラッチの競合は、多数のCPUを搭載したSMPコンピュータでのパフォーマンスを低下させることがあります。LRUラッチの競合は、V$LATCH、V$SESSION_EVENTおよびV$SYSTEM_EVENTに問い合せることによって検出できます。競合を回避するには、アプリケーションのチューニング、DSSジョブのバッファ・キャッシュのバイパスまたはアプリケーションの再設計を検討してください。

cache buffers chainsのラッチは、バッファ・キャッシュでバッファ・リストを保護する場合に使用します。これらのラッチは、バッファの検索、追加、またはバッファ・キャッシュからの削除を行う際に使用されます。このラッチの競合は、競合度の高い(ホットな)ブロックが存在することを意味します。

頻繁にアクセスされるバッファ連鎖を識別して競合するブロックを識別するには、V$LATCH_CHILDRENビューを使用してcache buffers chainsのラッチのラッチ統計を調べます。他の子ラッチと比較して、多くのGETS、MISSESおよびSLEEPSを持つ特定のcache buffers chainsの子ラッチがある場合、これは子ラッチで競合します。

このラッチには、ADDR列で識別されるメモリー・アドレスがあります。このラッチで保護されているブロックを識別するには、X$BH表と結合されたADDR列の値を使用します。たとえば、頻繁に競合するラッチのアドレス(V$LATCH_CHILDREN.ADDR)を指定すると、ファイル番号とブロック番号の問合せが作成されます。 

SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH
  FROM X$BH
 WHERE HLADDR = 'address of latch'
  ORDER BY TCH;

X$BH.TCHは、バッファのタッチ・カウントです。X$BH.TCHの値が高い場合は、ホット・ブロックであることを示します。

多くのブロックは、各ラッチにより保護されています。これらのバッファの1つは、おそらくホット・ブロックになります。TCH値の高いブロックは、潜在的なホット・ブロックです。この問合せを複数回実行し、出力に一貫して表示されるブロックを識別します。ホット・ブロックを識別した後は、ファイル番号とブロック番号を使用してDBA_EXTENTSの問合せを行い、セグメントを識別します。

ホット・ブロックの識別後に、次の問合せを使用してそのホット・ブロックが属するセグメントを識別できます。

SELECT OBJECT_NAME, SUBOBJECT_NAME
  FROM DBA_OBJECTS
 WHERE DATA_OBJECT_ID = &obj;

この問合せでの&objは、X$BHへの前述の問合せにおけるOBJ列の値です。

row cache objectsのラッチは、データ・ディクショナリを保護します。

10.3.12 log file parallel write

このイベントには、ログ・バッファからREDOログ・ファイルへのREDOレコードの書込みが含まれます。

10.3.13 library cache pin

このイベントはライブラリ・キャッシュの同時実行性を管理します。オブジェクトを確保すると、ヒープがメモリーにロードされます。クライアントがオブジェクトを変更または検討するには、クライアントはロック後に確保を取得する必要があります。

10.3.14 library cache lock

このイベントは、ライブラリ・キャッシュの複数クライアント間の同時実行性を制御します。これによって、オブジェクト・ハンドルのロックが取得されるため、次の利点があります。

  • クライアントは、別のクライアントが同じオブジェクトにアクセスしないようにできます。

  • クライアントは、別のクライアントにオブジェクトの変更を許可しない依存関係を長期間維持

このロックの取得には、ライブラリ・キャッシュ内のオブジェクト位置を見つける働きもあります。

10.3.15 log buffer space

このイベントは、サーバー・プロセスがログ・バッファ内の空き領域を待機しているときに発生します。これは、LGWRがREDOを書き出すよりも、すべてのREDOが生成されるほうが速いためです。

処置

REDOログ・バッファ・サイズを修正します。ログ・バッファのサイズが適切な場合、オンラインREDOログが存在するディスクでI/O競合が発生しないことを確認します。log buffer space待機イベントは、REDOログが存在するディスク上のディスクI/O競合を示しているか、小さすぎるログ・バッファを示している可能性があります。REDOログを含むディスクのI/Oプロファイルをチェックして、I/Oシステムがボトルネックであるかどうかを調べます。I/Oシステムが問題ではない場合、REDOログ・バッファが小さすぎる可能性があります。このイベントが問題にならなくなるまで、REDOログ・バッファのサイズを大きくします。

10.3.16 log file switch

一般に発生する待機イベントは、次の2つです。

  • Log file switch (archiving needed)

  • Log file switch (checkpoint incomplete)

これらのイベントが発生すると、LGWRは次のオンラインREDOログ・ファイルへの切替えができません。すべてのコミット・リクエストは、このイベントを待機します。

処置

log file switch (archiving needed)イベントの場合、アーカイバがログをアーカイブできない理由を適時調べます。次の理由が考えられます。

  • アーカイブ先に空き領域が不足している。

  • アーカイバがREDOログを十分高速に読み取れない(LGWRとの競合)。

  • アーカイバが十分高速に書込みを行えない(アーカイブ先での競合、またはARCHプロセスの数が不足している)。その他の可能性(ディスクが低速であったり、アーカイブ先がいっぱいなど)が除外された場合は、ARCnプロセス数の増加を検討してください。デフォルトは2です。

  • 必須でリモートに転送されるアーカイブ・ログがある場合は、ネットワーク遅延によってこのプロセスが遅くなっていないか、またはエラーによって書込みが完了していないかをチェックしてください。

ボトルネックの性質により、I/Oを再分散したり、アーカイブ先に領域を追加して問題を軽減することが必要な場合があります。log file switch (checkpoint incomplete)イベントの場合、次を実行してください。

  • DBWRが、I/Oシステムがオーバーロードしているか、低速のために遅くなっているのかどうかを調べます。DBWR書込み回数を調べ、I/Oシステムをチェックし、必要に応じてI/Oを分散させます。

  • REDOログが少なすぎないか、または小さすぎないかを調べます。REDOログが少なすぎるか小さすぎる場合(たとえば、2 x 100KBのログ)、またDBWRがチェックポイントを完了する前に、すべてのログを巡回する十分なREDOが生成される場合、REDOログのサイズまたは個数、あるいはその両方を増やします。

10.3.17 log file sync

ユーザー・セッションがコミットする(またはロールバックする)場合、LGWRでセッションのREDO情報をREDOログ・ファイルにフラッシュする必要があります。COMMITまたはROLLBACKを実行するサーバー・プロセスは、REDOログへの書込みが完了するまで、このイベントで待機します。

処置

このイベントの待機がシステム上での長時間の待機か、レスポンス時間の問題が発生しているユーザーまたはシステム上のユーザーによる長時間の待機を構成している場合は、平均待機時間を調べます。

平均待機時間は短いが、待機数が多い場合、アプリケーションはCOMMITをバッチ処理するのではなく、すべてのINSERT後にコミットできます。アプリケーションは、行ごとではなく50行後にコミットして待機を減らすことができます。

平均待機時間が多い場合は、ログ・ライターに対するセッション待機を調べ、何を実行および待機するために多くの時間を費やしているかを調べます。待機が低速のI/Oによるものである場合は、次のことを試行してください。

  • REDOログを含むディスク上の他のI/Oアクティビティを削減するか、専用ディスクを使用します。

  • 異なるディスク上に交互のREDOログを設定して、ログ・ライターに対するアーカイバの影響をできるだけ少なくします。

  • REDOログをさらに高速なディスクまたはさらに高速なI/Oサブシステム(たとえば、RAID 5からRAID 1への切替え)に移動します。

  • RAWデバイス(またはディスク・ベンダーが提供しているシミュレートされたRAWデバイス)を使用して書込みを高速化することを検討してください。

  • アプリケーションのタイプにより異なりますが、1行ごとではなく、N行ごとにコミットして、COMMITをバッチ処理することで、ログ・ファイルの同期が少なくてすみます。

10.3.18 rdbms ipc reply

このイベントは、バックグラウンド・プロセスの1つからのメッセージを待機するために使用されます。

10.3.19 SQL*Netイベント

次のイベントは、データベース・プロセスがデータベース・リンクまたはクライアント・プロセスからの確認を待機していることを示します。

  • SQL*Net break/reset to client

  • SQL*Net break/reset to dblink

  • SQL*Net message from client

  • SQL*Net message from dblink

  • SQL*Net message to client

  • SQL*Net message to dblink

  • SQL*Net more data from client

  • SQL*Net more data from dblink

  • SQL*Net more data to client

  • SQL*Net more data to dblink

これらの待機がシステム上で、またはレスポンス時間の問題に対処しているユーザーに対して、待機時間の大部分を占めている場合、ネットワークまたは中間層がボトルネックになる可能性があります。

クライアント関連のイベントは、SQL*Net message from clientイベントで説明したとおりに診断する必要があります。dblink関連のイベントは、SQL*Net message from dblinkイベントで説明したとおりに診断する必要があります。

SQL*Net message from client

これはアイドル・イベントですが、このイベントを診断に使用できるときに何が問題でないかを説明することが重要です。このイベントは、サーバー・プロセスがクライアント・プロセスからの処理を待機していることを示します。ただし、このイベントが、長いレスポンス時間を経験しているユーザーの待機時間の大半を生成している状況がいくつかあります。その原因は、ネットワーク・ボトルネックまたはクライアント・プロセスでのリソース・ボトルネックのいずれかである可能性があります。

ネットワーク・ボトルネックは、アプリケーションによってサーバーとクライアントとの間で多量の通信が発生し、ネットワークの待機時間(ラウンドトリップの時間)が長い場合に発生する可能性があります。症状には次のものがあります。

  • このイベントに対する多数の待機

  • データベースとクライアント・プロセスは、時間のほとんどがアイドル状態(ネットワーク通信待ち状態)です。

ネットワーク・ボトルネックを軽減するには、次のことを試行します。

  • アプリケーションをチューニングしてラウンドトリップを軽減します。

  • 待機時間を削減するためのオプション(たとえば、VSATリンクとは反対の地上回線)を探索します。

  • 通信量の大きいコンポーネントを待機時間の少ないリンクに移動するように、システム構成を変更します。

クライアント・プロセスがリソースの大半を使用している場合、データベース内で実行できることはありません。症状には次のものがあります。

  • 待機数は多くなくても、待機時間は長い。

  • クライアント・プロセスは、リソースを多く使用している。

場合によっては、クライアント・プロセスで使用されるCPUの量により、待機しているユーザーのトラッキングのための待機時間がわかります。この場合のクライアントという用語は、n層アーキテクチャにおける、データベース・プロセス(中間層、デスクトップ・クライアント)以外の任意のプロセスを指します。

SQL*Net message from dblink

このイベントは、セッションがリモート・ノードにメッセージを送り、データベース・リンクからのレスポンスを待機している状態であることを意味します。この時間は、次の理由で増える可能性があります。

  • ネットワーク・ボトルネック

    詳細は、「SQL*Net message from client」を参照してください。

  • リモート・ノードでSQLを実行するのに要する時間

    リモート・ノードで実行されているSQLを確認することが有益です。リモート・データベースにログインし、データベース・リンクで作成されたセッションを検索し、そのセッションで実行されるSQL文を調べます。

  • ラウンドトリップ・メッセージの数

    セッションとリモート・ノード間の各メッセージにより、遅延時間が長くなり、処理オーバーヘッドが増加します。交換されるメッセージの数を減らすには、配列フェッチと配列挿入を使用します。

SQL*Net more data to client

サーバー・プロセスは、クライアントにさらに多くのデータまたはメッセージを送信します。クライアントに対する以前の操作も送信されました。

関連項目:

ネットワーク最適化の詳細は、『Oracle Database Net Services管理者ガイド』を参照してください。

10.4 インスタンス・リカバリのパフォーマンスのチューニング: ファスト・スタート・フォルト・リカバリ

この項では、インスタンス・リカバリと、クラッシュまたはインスタンス障害が発生した場合のOracleのファスト・スタート・フォルト・リカバリによる可用性の向上について説明します。また、クラッシュ・リカバリやインスタンス・リカバリの実行に必要な時間をチューニングするためのガイドラインも示します。

この項では、次の項目について説明します。

10.4.1 インスタンス・リカバリについて

インスタンス・リカバリおよびクラッシュ・リカバリとは、クラッシュやシステム障害後に、Oracleデータ・ブロックにREDOログ・レコードを自動的に適用することです。通常の操作中は、インスタンスが異常終了ではなく(SHUTDOWN IMMEDIATE文を使用する場合のように)正常に停止した場合、ディスクのデータファイルに書き込まれていないインメモリーの変更は、停止時に実行されるチェックポイントの一環としてディスクに書き込まれます。

ただし、シングル・インスタンス・データベースのクラッシュまたはOracle RAC構成のすべてのインスタンスのクラッシュが発生した場合、次の起動時にクラッシュ・リカバリが実行されます。Oracle RAC構成の1つ以上のインスタンスがクラッシュした場合は、残りのインスタンスによってインスタンス・リカバリが自動的に実行されます。インスタンス・リカバリとクラッシュ・リカバリの手順は、キャッシュ・リカバリ、トランザクション・リカバリの順に行われます。

キャッシュ・リカバリが完了するとすぐにデータベースを開くことができるため、可用性の向上にはキャッシュ・リカバリのパフォーマンスの向上が重要になります。

10.4.1.1 キャッシュ・リカバリ(ロールフォワード)

キャッシュ・リカバリ・ステップでは、REDOログ・ファイル内のコミット済およびコミットされていない変更がすべて、影響のあるデータ・ブロックに適用されます。キャッシュ・リカバリ処理に必要な作業量は、データベースの変更率(1秒当たりの更新トランザクション数)とチェックポイント間の時間に比例します。

10.4.1.2 トランザクション・リカバリ(ロールバック)

データベースの一貫性を保つには、クラッシュ時にコミットされなかった変更を元に戻す必要があります(ロールバック)。トランザクション・リカバリ・ステップでは、ロールバック・セグメントを適用してコミットされていない変更が元に戻されます。

10.4.1.3 チェックポイントとキャッシュ・リカバリ

Oracle Databaseでは、チェックポイントが定期的に記録されます。チェックポイントは最大のシステム変更番号(SCN)であるため、このSCN以下のデータ・ブロックはすべてデータファイルに書込み済であることがわかります。障害が発生した場合、チェックポイントよりも高いSCNでの変更を含むREDOレコードのみがリカバリ時に適用される必要があります。キャッシュ・リカバリ処理の時間は、チェックポイントのSCNよりも高いSCNでの変更を含むデータ・ブロックの数と、これらの変更を検出するために読取りが必要なログ・ブロックの数の2つの要因で決定されます。

チェックポイントによるパフォーマンスへの影響

チェックポイントの頻度が高くなると、そうでない場合よりも高い頻度で使用済バッファがデータファイルに書き込まれるため、インスタンス障害時のキャッシュ・リカバリ時間が短縮されます。チェックポイントの頻度が高い場合、REDOログの現在のチェックポイント位置とログの末尾までの間にあるREDOレコードを適用する際、相対的に少ないデータ・ブロックが処理されます。つまり、リカバリのキャッシュ・リカバリ・フェーズがかなり短くなります。

ただし、更新の多いシステムでは、チェックポイントの頻繁な発生により、実行時のパフォーマンスが低下する可能性があります。これはチェックポイントによってDBWnプロセスで書込みが実行されるためです。

ファスト・キャッシュ・リカバリのトレードオフ

キャッシュ・リカバリ時間を最短にするには、Oracle Databaseでのチェックポイントを頻繁にして、リカバリ時に適用されるREDOログ・レコードの数を最小限にします。ただし、更新の多いシステムでは、チェックポイントの頻繁な発生により、通常のデータベース操作のオーバーヘッドが増加します。

リカバリ時間の短縮よりも日常の操作効率の方が重要な場合は、チェックポイントによるデータファイルへの書込み頻度を減らします。これにより、操作効率は向上しますが、キャッシュ・リカバリ時間も増加します。

10.4.2 キャッシュ・リカバリ時間の構成: FAST_START_MTTR_TARGET

ファスト・スタート・フォルト・リカバリ機能により、キャッシュ・リカバリに必要な時間が短縮されます。また、使用済バッファの数、および最新のREDOレコードと最後のチェックポイントとの間に生成されるREDOレコードの数を制限して、リカバリの制御および予測を行うことができます。

ファスト・スタート・フォルト・リカバリの基本は、ファスト・スタート・チェックポイント・アーキテクチャです。バルク書込みを行う従来のイベント・ドリブン(ログ・スイッチ)・チェックポイントではなく、ファスト・スタート・チェックポイントは段階的に発生します。各DBWnプロセスは、定期的にバッファからディスクへの書込みを行い、チェックポイント位置を先に進めます。最も古い変更ブロックは、各書込みによってチェックポイントが先に進められるように最初に書き込まれます。ファスト・スタート・チェックポイントでは、従来のチェックポイントで発生するバルク書込みと、その結果発生するI/Oスパイクを回避できます。

ファスト・スタート・フォルト・リカバリ機能では、初期化パラメータFAST_START_MTTR_TARGETによって、インスタンス障害またはシステム障害からのリカバリ時間を簡単に構成できます。FAST_START_MTTR_TARGETは、平均リカバリ時間(MTTR)の目標値、つまりインスタンスを起動してキャッシュ・リカバリの実行にかかる時間(秒単位)の目標値を指定します。FAST_START_MTTR_TARGETを設定すると、データベースはその目標値を達成するように増分チェックポイントの書込みを管理します。FAST_START_MTTR_TARGETに現実的な値を選択した場合、選択したおおよその平均秒数内でデータベースがリカバリされます。

注意:

FAST_START_MTTR_TARGETを使用する場合は、初期化パラメータFAST_START_IO_TARGET、LOG_CHECKPOINT_INTERVALおよびLOG_CHECKPOINT_TIMEOUTを無効にするか削除する必要があります。これらのパラメータを設定すると、FAST_START_MTTR_TARGETを達成するためにキャッシュ・リカバリ時間の管理に使用するメカニズムが適切に機能しなくなります。

10.4.2.1 FAST_START_MTTR_TARGETの現実的な値

FAST_START_MTTR_TARGETの最大値は3600秒(1時間)です。3600を超える値を設定すると、Oracle Databaseでその値は3600に丸められます。

次に、FAST_START_MTTR_TARGETの値を設定する方法の例を示します。 

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;

原則ではFAST_START_MTTR_TARGETの最小値は1秒です。ただし、FAST_START_MTTR_TARGETにそのような小さい値を設定できるからといって、その目標値を達成できるということにはなりません。データベースの起動時間などの要因によって、達成可能なMTTR目標値には現実的な下限があります。

FAST_START_MTTR_TARGETの現在の値を前提として、データベースが達成可能なMTTR目標値を有効MTTR目標値といいます。現在の有効MTTRを参照するには、V$INSTANCE_RECOVERYビューのTARGET_MTTR列を参照します。

使用するデータベースの現実的なMTTR目標値の範囲は、そのデータベースで達成可能な有効MTTR目標値の最小値から、最悪の場合(バッファ・キャッシュ全体が使用済の場合)に起動とキャッシュ・リカバリにかかる時間の最大値までです。「FAST_START_MTTR_TARGETの現実的な範囲の決定」に記載された達成可能なMTTR目標値の範囲を決定する手順は、FAST_START_MTTR_TARGET値のチューニング・プロセスの1ステップです。

注意:

FAST_START_MTTR_TARGETを現実的な範囲外の値に設定するのは、通常、有益ではありません。FAST_START_MTTR_TARGETの値が現実的な範囲の下限よりも小さい場合、その効果は現実的な範囲の下限に設定した場合と同様になります。このような場合、有効なMTTR目標値はシステムで達成できる最高のMTTR目標値になりますが、チェックポイントが最高頻度で実行され、通常のデータベースのパフォーマンスに影響を与える可能性があります。FAST_START_MTTR_TARGETを現実的な範囲よりも長い時間に設定する場合、MTTR目標値は最悪の場合と同じになります。

10.4.2.2 ランタイム・パフォーマンスを最適化するためのチェックポイント頻度の低減

チェックポイントの頻度を低くしてランタイム・パフォーマンスを最適化するには、次の手順を実行します。

  • FAST_START_MTTR_TARGETの値を3600に設定します。これにより、ファスト・スタート・チェックポイントとファスト・スタート・フォルト・リカバリ機能が有効になりますが、実行時パフォーマンスへの影響は最小限に抑えられ、FAST_START_MTTR_TARGETによるパフォーマンスのチューニングが必要なくなります。

  • システムが生成するREDOの量に従って、オンラインREDOログ・ファイルをサイズ設定します。ログの切替えは、最も頻繁に行う場合でも20分間隔とします。ログ・ファイルのサイズが小さすぎると、チェックポイント・アクティビティが増加し、パフォーマンスが低下します。また、すべてのREDOログ・ファイルのサイズが同じになるようにしてください。

    関連項目:

    チェックポイントの詳細は、Oracle Database概要を参照してください

10.4.2.3 V$INSTANCE_RECOVERYによるキャッシュ・リカバリの監視

V$INSTANCE_RECOVERYビューには、現在のリカバリ・パラメータの設定が表示されます。このビューの統計を使用して、チェックポイントに最大の影響を与えている要因を判断することもできます。

次の表に、キャッシュ・リカバリのパフォーマンスの予測を管理する際に役立つ列を示します。

表10-4 V$INSTANCE_RECOVERYの列

列 説明

TARGET_MTTR

有効なMTTR目標値(秒単位)。FAST_START_MTTR_TARGETを指定しない場合、このフィールドは0です。

ESTIMATED_MTTR

現在の使用済バッファ数およびログ・ブロック数に基づいた、現在の推定MTTR(秒単位)。FAST_START_MTTR_TARGETを指定しているかどうかにかかわらず、このフィールドは常に計算されます。

使用するデータベースで実行中の監視の一環として、V$INSTANCE_RECOVERY.TARGET_MTTRとFAST_START_MTTR_TARGETを定期的に比較できます。この2つの値は、FAST_START_MTTR_TARGETの値が現実的な範囲内にある場合は通常同じになります。TARGET_MTTRがFAST_START_MTTR_TARGETよりも常に大きい場合は、FAST_START_MTTR_TARGETをTARGET_MTTR以上の値に設定します。TARGET_MTTRが常に小さい場合は、FAST_START_MTTR_TARGETをTARGET_MTTR以下の値に設定します。

関連項目:

V$INSTANCE_RECOVERYビューの詳細は、『Oracle Databaseリファレンス』を参照してください

10.4.3 FAST_START_MTTR_TARGETのチューニングとMTTRアドバイザの使用

使用するデータベースに適切なFAST_START_MTTR_TARGET値を判断するには、次の4つの手順を使用します。

10.4.3.1 FAST_START_MTTR_TARGETの測定

初期化パラメータFAST_START_MTTR_TARGETにより、REDOログの長さとデータ・キャッシュの使用済データ・バッファの数を制限するために、内部システム・トリガーの値が計算されます。この計算では、REDOブロックの読取りの概算時間、データ・ブロックの読取りと書込みの概算時間、およびシステムの一般的なワークロードの特性(変更ベクトル数に対応する使用済バッファ数など)が使用されます。

最初は、内部的なデフォルト値が計算に使用されます。これらのデフォルト値は、時間の経過とともに、システム操作時および実際のキャッシュ・リカバリ時にI/Oパフォーマンスに関して収集されたデータに置き換えられます。

FAST_START_MTTR_TARGET値を適切に測定するには、インスタンス・リカバリを複数回実行する必要があります。測定を開始する前に、データベース・クラッシュとハードウェア・クラッシュのどちらに対してFAST_START_MTTR_TARGETを測定するかを決定してください。これは、データベース・ファイルがファイル・システムに格納される場合、またはI/Oサブシステムにメモリー・キャッシュがある場合の考慮事項です。ファイルがキャッシュされるかどうかによって、ディスクへの読取り時間と書込み時間に大幅な違いがあるためです。FAST_START_MTTR_TARGETの適切な値は、どのタイプのクラッシュがより迅速にリカバリする必要があるかによって異なります。

FAST_START_MTTR_TARGETを効率的に測定するには、システムの一般的なワークロードを長時間実行し、リカバリ時のREDOブロックの読取り時間およびデータ・ブロックの読取りまたは書込み時間が正確に記録されるようにインスタンス・リカバリを複数回実行します。

10.4.3.2 FAST_START_MTTR_TARGETの現実的な範囲の決定

測定後、テストを実行して、使用するデータベースのFAST_START_MTTR_TARGETの現実的な範囲を決定できます。

10.4.3.2.1 FAST_START_MTTR_TARGETの下限の決定: シナリオ

現実的な範囲の下限を決定するには、FAST_START_MTTR_TARGETを1に設定してデータベースを起動します。次に、V$INSTANCE_RECOVERY.TARGET_MTTRの値を確認して、この値をFAST_START_MTTR_TARGETの有効な下限として使用します。この下限の決定には、通常、キャッシュ・リカバリ時間ではなくデータベースの起動時間の方が主要な要因となります。

たとえば、次のようにFAST_START_MTTR_TARGETを1に設定します。 

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=1;

次に、データベースを開いた直後に次の問合せを実行します。

SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR 
    FROM V$INSTANCE_RECOVERY;

次のような結果が返されます。

TARGET_MTTR ESTIMATED_MTTR 
18          15

TARGET_MTTRの18秒という値はシステムで達成可能な最小MTTR目標値、つまりFAST_START_MTTR_TARGETの現実的な下限値です。この最小値は、データベースの平均起動時間に基づいて計算されます。

ESTIMATED_MTTRフィールドには、実行中のデータベースの現在の状態に基づいた、概算平均リカバリ時間が含まれます。データベースは開いた直後であり、システムに含まれるのは少数の使用済バッファであるため、この時点でインスタンスに障害が発生した場合でも、それほど多くのキャッシュ・リカバリは必要ありません。そのため、ESTIMATED_MTTRは、この時点ではTARGET_MTTRに指定可能な最小値よりも小さい値になる場合があります。

ESTIMATED_MTTRは、最近のデータベース・アクティビティによって短期的に影響を受ける場合があります。データベースで大量の更新アクティビティを一定期間行った直後にV$INSTANCE_RECOVERYに対して問合せを実行するとします。次のような結果が返されます。 

TARGET_MTTR ESTIMATED_MTTR 
18          30

有効MTTR目標値は18秒のままで、(その時点でクラッシュが発生した場合の)概算MTTRは30秒です。これは許容可能な結果です。つまり、一部のチェックポイントの書込みが完了していないため、バッファ・キャッシュには目標よりも多くの使用済バッファが含まれています。

ここで60秒待機してから、V$INSTANCE_RECOVERYに対して再度問合せを発行します。次のような結果が返されます。 

TARGET_MTTR ESTIMATED_MTTR 
18          25

この期間に一部の使用済バッファが書き込まれたため、今回の概算MTTRは25秒に減少しています。

10.4.3.2.2 FAST_START_MTTR_TARGETの上限の決定

現実的な範囲の上限を決定するには、FAST_START_MTTR_TARGETを3600に設定し、一般的なワークロードでデータベースを一定期間操作します。次にV$INSTANCE_RECOVERY.TARGET_MTTRの値を確認します。この値が、FAST_START_MTTR_TARGETの有効な上限です。

この手順は「FAST_START_MTTR_TARGETの下限の決定: シナリオ」と実質的に似ています。

10.4.3.2.3 FAST_START_MTTR_TARGETの初期値の選択

FAST_START_MTTR_TARGETパラメータの現実的な範囲を決定した後、このパラメータの初期値を選択します。データベースのパフォーマンスを懸念する場合は、現実的な範囲内で高い値を選択し、リカバリ時間の短縮が優先される場合は現実的な範囲内で低い値を選択します。当然のことながら、現実的な範囲が狭いほど、選択は容易になります。

たとえば、現実的な範囲が17から19秒の場合、単純に19を選択できます。これはリカバリ時間にはほとんど差がなく、同時にシステムのパフォーマンスに対するチェックポイントの影響が最小限に抑えられるためです。しかし、現実的な範囲が18から40秒の場合は、中間的な値として30を選択し、この値に従ってパラメータを設定できます。

SQL> ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;

その後、MTTRアドバイザを使用して最適値を判断できます。

10.4.3.3 MTTRアドバイザによる様々な目標値の評価

FAST_START_MTTR_TARGETの初期値を選択した後、MTTRアドバイザを使用して、異なるFAST_START_MTTR_TARGETの設定がシステム・パフォーマンスに与える影響を、選択した設定と比較して評価できます。

10.4.3.3.1 MTTRアドバイザの有効化

MTTRアドバイザを有効にするには、2つの初期化パラメータSTATISTICS_LEVELおよびFAST_START_MTTR_TARGETを設定します。

STATISTICS_LEVELでは、MTTRアドバイザのみでなく、すべてのアドバイザの有効化を制御します。このパラメータがTYPICALまたはALLに設定されていることを確認します。次にFAST_START_MTTR_TARGETをゼロ以外の値に設定すると、MTTRアドバイザが有効になります。

10.4.3.3.2 MTTRアドバイザの使用

MTTRアドバイザを有効にした後、一般的なデータベース・ワークロードを一定期間実行します。MTTRアドバイザが有効な場合、現在のFAST_START_MTTR_TARGET値、およびFAST_START_MTTR_TARGET値の有効範囲内の最大4つの異なるMTTR設定に基づいて、チェックポイント・キュー動作がシミュレートされます。(この場合、FAST_START_MTTR_TARGETの有効範囲が決定されてから、その範囲内の複数の値がテストされます。)

10.4.3.3.3 MTTRアドバイザの結果の表示: V$MTTR_TARGET_ADVICE

動的パフォーマンス・ビューV$MTTR_TARGET_ADVICEでは、MTTRアドバイザによって収集された統計またはアドバイスを表示できます。

V$MTTR_TARGET_ADVICEには、データベースに対するFAST_START_MTTR_TARGETの各設定の影響に関するアドバイスが移入されます。FAST_START_MTTR_TARGETの値ごとに、行にはFAST_START_MTTR_TARGETの値に対してテストに使用されたワークロード下で実行されるキャッシュ書込み数に関する詳細が表示されます。

具体的には、各行には、その行のFAST_START_MTTR_TARGET値でのキャッシュ書込み数、合計物理書込み数(直接書込みを含む)および合計I/O(読取りを含む)に関する情報が、合計操作数と、選択したFAST_START_MTTR_TARGET値での操作との比率の両方で表示されます。たとえば、比率が1.2の場合は、キャッシュ書込みが20%多いことを表しています。

様々なFAST_START_MTTR_TARGET設定がキャッシュの書込みアクティビティやその他のI/Oに与える影響を理解すると、リカバリやパフォーマンスの要件に最適なFAST_START_MTTR_TARGET値をより効率的に決定できます。

MTTRアドバイザが現在有効な場合は、V$MTTR_TARGET_ADVICEに、収集されたアドバイザ情報が表示されます。現在、MTTRアドバイザがOFFの場合、データベースを起動してからMTTRアドバイザが最後にONであったときに収集された情報が表示されます(情報が存在する場合)。最後にMTTRアドバイザを使用してからデータベースが再起動された場合、またはMTTRアドバイザが1回も使用されなかった場合、このビューには行は表示されません。

関連項目:

V$MTTR_TARGET_ADVICEビューの列の詳細は、Oracle Databaseリファレンスを参照してください

10.4.3.4 最適なREDOログ・サイズの決定

V$INSTANCE_RECOVERYビューのOPTIMAL_LOGFILE_SIZE列を使用して、オンラインREDOログのサイズを決定できます。このフィールドには、FAST_START_MTTR_TARGETの現在の設定に基づいて最適と判断されるREDOログ・ファイルのサイズ(MB単位)が表示されます。このフィールドに、最も小さいオンライン・ログのサイズよりも大きな値が常に表示される場合は、すべてのオンライン・ログをこのサイズ以上に構成する必要があります。

ただし、REDOログ・ファイルのサイズがMTTRに影響を与えることに注意してください。ログ・ファイルのサイズを、希望する最適な値に設定してMTTRアドバイザを再実行すると、選択した最適なFAST_START_MTTR_TARGET値をさらに適切に調整できる場合もあります。