「遅い」処理のSQLチューニングの前にやるべきこと（性能問題切り分けのための取得情報）では、問題切り分けのために取得するべき必要な情報についてまとめました。
本記事では、性能問題切り分けのための情報を使って、どのように性能問題を切り分けていくかについて解説します。
あくまで例ですので、すべての問題に対する切り分け方法ではないですが、代表的な切り分けポイントをまとめていますので、参考にしてみてください。
処理遅延の考えられる要因
本記事では以下の要因について、どのような資料から判断できるかを解説します。
- CPUやメモリなどのリソース不足
- ネットワークが遅い
- ディスクI/Oが遅い
- データ量の増加
- リソース競合（ラッチ、ロック）
- 不具合によるスピン/ループ
- SQLそのものが適切でない
- SGA/PGA不足などの基盤設計的な問題
- 索引不足などの論理設計的な問題
ハードウェアやリソース状態に起因
処理遅延に対して、Oracleデータベースではない部分が起因となっている事象です。
データベースが遅いと言われてしまうと、まずはデータベースの性能分析に目が行きがちですが、リソース状態が原因となっているケースも多いです。
CPUやメモリなどのリソース不足
以下の観点でリソース不足が発生していないかを切り分けます。
- CPUリソース不足
- OSの空きCPU使用率(id列)が定常的に数%以下になっている
- vmstatのr列(CPU待ち数)が、搭載しているCPU数よりも定常的に大きい数値となっている(CPU待ちが大量に発生して、CPUが捌き切れていない状態)
- Oracleデータベース上で、resmgr:cpu quantum待機イベントが長時間発生している(データベースに割り当てられているCPUが不足している可能性もある)
- メモリ不足
- vmstatのsi/so列が0よりも多くなっている(メモリ不足によるスワップが発生している状態)
- Oracleデータベース上で、ORA-4030またはORA-4031が発生している(データベースに割り当てられているSGAまたはPGAが不足している可能性もある)
- なお、vmstatのfree列(空きメモリ量)が少なくても、buff列やcache列で計上されているメモリを再利用できる場合も多く、問題にならないケースも多々あります。
- I/Oリソース不足
- iostatのディスクビジー率(%util列)が定常的に100%に近づいた状態になっている
- iostatの平均待ち行列長(avgqu-sz)とレスポンスタイム(await)が大きくなっている
- Oracleデータベース上でdb file sequential read待機イベントを含む、I/O関連の待機イベントが長時間発生している(大量にI/Oが発生している場合)
- db file sequential readの平均待機時間が、通常時と比較して大きくなっている(大量にI/Oが発生していることで、シングル・ブロック・リードの平均待機時間が数倍～数十倍に大きくなる可能性があります)
ネットワークが遅い
ネットワーク上のどこで遅延が発生してしまっているかを確認するため、まずはネットワーク経路を確認します。
- どのスイッチを経由しているのか
- エンドポイントから各スイッチ間の帯域はどの程度か
- 通信の行きと帰りは同じ経路か
さらに各径路での通信応答時間を測定します。
pingによる計測も重要ですが、pingはICMPを利用した診断プログラムですので、tcpingのようなTCPを利用した診断プロトコルの方が測定として望ましいケースもあります。
実際にICMP経由だと高速なのに、TCP経由では遅いといったケースもあり、その場合はpingだけでは診断することができます。
調査観点としては以下を確認します。
- netstatでRX-ERR/RX-DRP列、TX-ERR/TX-DRP列の値が増えていないこと(輻輳が発生していて、結果的にネットワーク処理遅延が発生している可能性あり)
- Oracleデータベース上でSQL*Net more data from client待機イベントを含む、ネットワーク関連の待機イベントが長時間発生している
ディスクI/Oが遅い
ハードウェアそのものが故障している可能性も考慮して以下を確認します。
- OSログから何らかのハードウェアエラーが発生していないか
- iostatの平均I/Oサイズ(avgrq-sz)があまり変化せず、サービスタイム(svctm)が大きくなっていないか
Oracleデータベースに起因
次に、Oracleデータベースが要因となって処理遅延を引き起こす可能性のある要因をみていきます。
データ量の増加
データの洗い替えなどでデータ件数やデータ量が大きく変化した場合に、統計情報が最新化されておらず適切な実行計画が選択されない場合があります。
結果的に処理遅延が発生します。
実行計画の確認方法はこちらの記事を参照してください。
例えば、データの洗い替えなどで、件数やデータ量が大きく異なっているのに、統計情報が最新化されておらず、適切な実行計画が選択されないといった場合です。
こういった場合は統計情報を最新化してあげることで、適切な実行計画となるため、性能問題も解消します。
切り分けの観点としては、統計情報から推測された行数と実際に返された行数に大きな乖離がないかを見ることで判断することができます。
統計情報から推測している行数と実際の行数に大きな違いがある場合(一般的には桁が1つ以上異なっている場合)は、統計情報が適切ではないと言えます。
リソース競合（ロック待ち、ラッチ待ち）
あるセッションが持つロックを他のセッションが要求した場合に、ロック待ちが発生する可能性があります。
ロック待ちが発生しているかは以下のような情報から切り分けます。
- AWRレポートから、ロック待ち(ラッチ待ち)待機イベントが長時間発生している
- ASHから特定セッションがブロッカーとなっている
Oracleデータベースでは排他制御するための仕組みとして、ロック、ラッチ、mutexといった名称があります。
いずれもカレントブロックなどの一貫性を保った状態でアクセスするための仕組みで用いられており、排他制御の粒度としては、ロック > ラッチ > mutex の順で小さくなります。
ロックは、ラッチやmutexと比べて、より広範囲のリソースに対して排他制御を行います。
ロック待ちはORA-54（リソースビジー）といったエラーの原因となります。
不具合によるスピン/ループ
製品不具合などの何らかの要因によって、特定のプロセスがスピン/ループし続けてしまう場合があります。
こういった場合は、特定のプロセスがボトルネックとなってしまい、データベース全体の性能が低下してしまう可能性があります（サーバプロセスか、バックグラウンドプロセスかによっても影響範囲が異なります）。
特定のプロセスがスピン/ループに陥っていないかは、以下の情報から切り分けます。
- psコマンドの結果から、特定プロセスのC列（CPU使用率）がずっと100%に張り付いていないかを確認する
- topコマンドの結果から、特定プロセスのCPU使用率がずっと100%に張り付いていないかを確認する
- 複数回のerrorstackから、特定の処理が繰り返されていないかを確認する
ASHから、この特定のセッション（プロセス）がブロッカーになっていることを確認することができれば、他のセッションは影響を受けていると判断することができます。
SQLそのものが適切でない
一般的にSQLの書き方が非効率となっており、無駄なデータアクセスが発生してしまった結果、処理遅延が発生する場合があります。
以下などのようなSQLアンチパターンが使用されていないを確認します。
- 自然結合による直積（MERGE JOIN CARTESIAN）が使われている（WHERE条件が指定されていない）
- 否定形条件（!=, ^=, <>, NOT）が使用されている（索引を利用できない）
- NULL条件が使用されている（索引を利用できない）
SGA/PGA不足などの基盤設計的な問題
データベース設計が原因で性能遅延に陥っている場合もあります。
SGAやPGAが不足していた場合に発生する代表的な状況を解説します。
[SGA不足] バッファキャッシュが不足している
バッファキャッシュによって一時的に表や索引のデータをメモリ上に格納することができます。
ディスクアクセスよりメモリアクセスの方が高速ですので、バッファキャッシュが十分であれば、処理を高速化できます。
逆にバッファキャッシュが不足している場合は、頻繁にディスクアクセスが発生することになりますので、処理遅延に繋がる可能性があります。
[SGA不足] SQL領域が増えすぎてしまっている
バインド変数を利用せずにWHERE条件をリテラルで指定しているケースは要注意です。
リテラル指定の場合、類似したSQL文だったとしても、別々のSQLとして管理されますので、SQL領域が大きくなります。
SQL領域が多くなると、対象のSQLを検索するにも時間が掛かるようになってきますし、バッファキャッシュを無駄に消費することにも繋がります。
[PGA不足] 一時領域を確保できずにディスクI/Oが発生している
SQLを実行する上で、ソートなどの一時的な処理はPGAを使って処理されますが、利用できるPGA領域が不足すると、一時領域としてディスクを使うようになります。
バッファキャッシュ不足と同様、頻繁にディスクアクセスが発生することになりますので、処理遅延に繋がる可能性があります。
索引不足などの論理設計的な問題
適切な索引が存在しない、または、索引が断片化している、といった理由で性能遅延が発生している場合もあります。
WHERE条件を指定しているにも関わらず、表に対するフルアクセスが発生している場合は、適切な索引が存在していない場合があります。
一方で、索引を作り過ぎてしまうと、今度はレコード更新時にメンテナンス対象となる索引が多くなりますので、更新性能に影響を与える可能性があります。
まとめ
性能問題切り分けのための情報を使って、どのように性能問題を切り分けていくかについて解説しました。
性能問題の要因は多岐に渡りますし、複数の要因が絡んで発生するケースも珍しくありませんので、ひとつずつ可能性を潰して、要因を特定していくことが大事になってきます。
コメント