もう怖くない！Oracle Databaseのパフォーマンスダウンにこう立ち向かえ！

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

システムを利用するユーザー側からすればストレスなく利用できるのがベストですし、提供する側も、システムや人的リソースには限りがあるため最小構成で長く安定稼働できることが望ましい状況です。パフォーマンスダウンは、多くの場合、システムリソースを大きくすれば一時的に解決できますが、将来的に、またシステムを長く持たせるためにも、根本的な原因を突き止め、適切な対策を実施しておくことが重要です。

本記事は、Oracle Databaseの運用で独り立ちされる方を想定し、Oracle Databaseのパフォーマンスダウンが発生する前に準備しておくべきことや、パフォーマンスダウンの原因別に対策をまとめました。パフォーマンスダウンはある日突然やってきます。この有事が発生する場合に備えどうしておくべきなのか、その一助になれば幸いです。

弊社のサポートセンターには「処理が遅くなってしまいセッションが滞留した」「本来数分で終わるべき処理が1時間以上かかっても終わらなかった」といったパフォーマンスダウンに関するお問い合わせが、年間1,000件以上も寄せられます。しかし、多くの場合は、お客様側で復旧作業を急ぐあまり、先にデータベースサーバを再起動してとりあえず復旧させ、その後再発防止のための原因特定をアシストに依頼されるのですが、実は発生時点の情報がなければ解決が非常に難しい問題なのです。

また、パフォーマンスダウンに関しては、システムリソースを増強すればかなりの確率で問題を解決できますが、増強にはコストがかかることや、増強しようと思っていた矢先に発生するものなのです。システムがスローダウンしてから突然止まるということがないように、日頃から個々の原因をきちんと特定し、問題が再発しないよう手をうっておくことが重要です。

今回の記事では、有事に役立ついくつかの基本的なポイントをご紹介します。是非実践してみてください。

パフォーマンスダウンの対応には、順番があります。まずはシステムが安定稼働している通常時はいったいどんな状況なのかを知るところから始めます。

1.　日頃から統計情報を取得し、基礎情報（ベースライン）を知っておく
2.　どこが原因かあたりをつける
3.　対策を行う（パフォーマンスチューニング）

基礎情報がなければ、通常時との比較ができず、対策を打つこともままなりません。また、チューニングを先に行ってしまうと、結局原因が特定できないまま、再発する可能性が高くなり、システムの安定稼働は実現できません。

パフォーマンスダウンの原因は必ずしもデータベースにあるとは限りません。そのため、常日頃からデータベースの統計情報だけでなくOSの統計情報も取得しておきます。

特に、システムが急激にスローダウンした場合は、迅速に判断しながらチューニングを進め、急いで通常の状態に戻すことが肝要です。データベース、OSともに統計情報を収集し、通常時のパフォーマンス情報を把握しておくことが重要です。

Oralce Database Enterprise EditionやStandard Editionを利用している環境では、統計情報取得の標準ツールとして、動的パフォーマンスビュー（V$ビュー）およびStatspackを利用できます。これらを利用して、定期的に統計情報を取得しておくことをお勧めします。

動的パフォーマンスビュー

様々なデータベースアクティビティを確認するためのツールで、データベース稼働中は継続的に値が更新されるという特徴があります。つまり、動的パフォーマンスビューでは最新情報は取得できますが、過去の情報は取得できない点に注意が必要です。

動的パフォーマンスビューの定期取得や調査方法、情報を簡単に取得するためのサンプルスクリプトは以下の記事をご参照ください。

▼Oracle Databaseのパフォーマンスダウン発生に備えたV$ビューの定期取得と調査方法
https://www.ashisuto.co.jp/db_blog/article/oracledb-dynamic-performance-view.html


▼WindowsとLinuxの各環境用にV$SESSION、V$PROCESS、V$LOCKを10秒ごとに自動取得するサンプルスクリプトをご用意、詳しくはこちらを
https://www.ashisuto.co.jp/db_blog/article/20161116_perform_view.html


Statspack

ある時点のパフォーマンス情報をスナップショットとして取得し、任意のスナップショット間のレポートを作成します。このレポートを確認することでボトルネックを分析することができます。Statspackでは累計と平均値を把握できますが、瞬間的な問題を把握できない可能性があります。そのため動的パフォーマンスビュー（V$SESSION）による情報収集と合わせて行うとよいでしょう。

Statspackの活用術や、情報を自動取得するためのサンプルスクリプトは以下の記事をご参照ください。

▼サポートのトップエンジニアが語るワンランク上のStatspack活用術！
https://www.ashisuto.co.jp/db_blog/article/oracledb-statspack-usage.html

▼Statspackレポートを期間指定で一括取得するスクリプトはこちら
https://www.ashisuto.co.jp/db_blog/article/201712-statspack-report.html

弊社サポートセンターの調査では、Statspackと動的パフォーマンスビューを定期取得している場合は、Enterprise Edition単体、またはStandard Editionで定期取得していない場合と比べて、解決率が2倍以上になるというデータがあります。

また、Enterprise Editionを利用している場合で有償オプションである「Diagnostics Pack」ライセンスがある場合は、Oracle Enterprise Manger（OEM）や、Statspackがより進化し、スナップショットの自動取得と自動管理が行えるAutomatic Workload Repository（AWR）を利用できます。

問題の切り分けは以下の順番で進めます。

1.　ハードウェア障害かどうかの確認
2.　OSがボトルネックかどうかを特定するための情報分析
3.　データベースがボトルネックかどうかを特定するための情報分析

まずはハードウェアに異常がないことを確認し、それからOS、データベースの順に統計情報を分析しながら問題の特定を進めます。

データベースのパフォーマンスダウンの原因特定に特に有効な手段となるのが、待機イベントの確認です。プロセスがCPUを使えずに何かしら処理を待っている状態には、それぞれ原因に応じた名前が付けられ待機時間と合わせて記録されます。待機イベントの傾向は、Statspackや動的パフォーマンスビューから調べることができます。

ボトルネックが特定できない状況でデータベースに対しチューニングを行うと新たな問題が発生する可能性があるため、まずは原因の特定を急ぎましょう。また、複数の変更を同時に行うと原因が不明確になるため、一つ一つ効果をみながらチューニングを実施します。

ここではパフォーマンスダウンの原因になりやすいポイントと対策について、図に沿って簡単にご紹介します。

不適切なSQLが多く存在すると、大量のディスクI/Oやメモリ消費が発生します。そのため、負荷が高くなっているSQLを特定し、適切な実行計画が選択されるように調整します。

パフォーマンスに影響がある不適切なSQLとは以下のような状況にあるものです。

・実行時間が長い
・ディスク読み取りブロック数が多い（I/O使用率が高い）
・バッファ読み取りブロック数が極端に多い（CPU使用率が高い）
・実行回数が極端に多い

この対策には、最初に高い負荷となっているSQLを特定する必要があります。この特定のためにSQLトレース、TKPROFを順に実行すると、以下のような情報を取得できます。

・SQLの実行時間（CPU時間と経過時間）
・アクセスブロック数
・実行回数
・実行計画


SQLトレースは1文単位で正確な情報を確認でき、パフォーマンス調査に非常に有効ですが、適切な方法で取得しないと意図した情報を得られずに、再設定、再発待ち、と原因特定までに時間がかかるケースがあります。ケース別にSQLトレースの取得方法をまとめた以下の記事をご参照ください。

▼「SQLトレースの取得方法まとめ（ケース別）」
https://www.ashisuto.co.jp/db_blog/article/20160630_sqltrace.html

Oracle DatabaseではSQLの解析、実行、フェッチが行われユーザーへ問い合わせ結果を返します。SQLの一連の処理をどう実行するか、Oracle Databaseが最も効率の良い方法を決めるのが「実行計画」です。

実行計画にはアクセスパス（索引の有無も判断しながら表から行を取り出す経路）や、２つ以上の表を結合して取り出す場合の方法や順序などが含まれます。

この実行計画は統計情報をもとに決定されますが、それを決めるのが「コストベースオプティマイザ」です。オプティマイザが選択する代表的なデータアクセス方法として「全表スキャン」と「索引スキャン」がありますが、これがパフォーマンスに大きな影響を及ぼします。

SQLチューニングに関わるオプティマイザの詳細については、こちらの記事もぜひご参照ください。
https://atmarkit.itmedia.co.jp/ait/articles/0901/29/news152.html

オプティマイザはOracle Databaseのバージョンを追うごとに大きく進化していますが、選択される実行計画がまれにパフォーマンスに影響する場合があります。

例えば、毎日バッチで実行している計画が自動的に変更され、パフォーマンスが逆にダウンする結果になると困りますよね。そんな時には、固定化する方法があります。以下を参照してください。

▼実行計画を固定化する方法まとめ
https://www.ashisuto.co.jp/db_blog/article/201809_execution_plans.html

また、実行計画は統計情報をもとに立てられますが、同一表の複数列にANDやORが含まれていると、実行計画がうまく生成できない可能性もあります。そんな時は以下の記事を参照してください。

▼SQL実行計画を改善する拡張統計（列グループ統計）の取得方法
https://www.ashisuto.co.jp/db_blog/article/20160714_extended_stats.html


また、パフォーマンスは、高負荷となっているSQLの実行計画を確認→調整→テスト・効果測定を繰り返し行い、改善していきます。しかし、本番環境でいきなり調整結果をテストすることはできません。そこで、可能な限り本番環境に合わせたテストを行うために、表、索引、制約などの定義情報、データ、実行SQL文などの「再現ケース」を検証環境に作成しなければなりません。ここで問題となるのは、これらの情報を個別に取得するとかなりの手間がかかり、作業的に抜け・漏れが発生する可能性が高まるということです。

再現ケースに必要な情報を容易に取得できる「SQLテスト・ケース・ビルダー」は、Oracle Database 11gR1から追加された機能で、テストを迅速に行い、問題解決までの時間を短縮できます。以下で使用方法を詳細にご紹介していますので参考にしてください。

▼再現ケースを簡単に作成！SQLテスト・ケース・ビルダーの使用方法
https://www.ashisuto.co.jp/db_blog/article/20160921_testcase_builder.html


SQLに関するパフォーマンスを効率的に向上させるためには、Oralce Databaseの構造をしっかり理解した上で実践できるアシストの「SQLパフォーマンス・チューニング」研修コースがお勧めです。是非ご利用ください。

▼「SQLパフォーマンス・チューニング」を体系的に学びたい場合はこちら
https://www.ashisuto.co.jp/ojt/course/oracle/list/sql-tuning.html

SGAではメモリ上に保持されているデータの共有率を高めることが重要です。基本的な対応として、まずは利用頻度の高いデータがメモリ上に残るように調整し、効果が得にくい場合はサイズを調整するという2段構えで行います。基本的な対策としては、それぞれのメモリに関連する待機イベントや統計情報を調査し、適切なサイズに調整します。

共有プール

SQL解析結果の共有率を高めること、断片化の防止・解消が重要です。そのためにはSQLの記述を統一化し、共有プールを適切なサイズに調整します。これにより、CPU使用量を低減できます。

REDOログ・バッファ

REDOログ・バッファ、オンラインREDOログ・ファイルでは、REDOログレコードの書き込み待機、オンラインREDOログ・ファイルへの書き込み遅延、ログ・スイッチの待機が発生しないようにします。

データベース・バッファ・キャッシュ

保持されているデータの共有率を上げることが重要です。そのためにはSQLを先に改善し、データベース・バッファ・キャッシュを適切なサイズに調整します。これにより、ディスクI/Oを低減できます。

データベースサーバのメモリ使用率が高騰した場合の有効な調査方法は、メモリを使用しているセッションを確認することです。この場合、先にOS側（Windowsの場合はパフォーマンスモニター、UNIX/Linuxの場合はpsやtopコマンドなど）からOracle Databaseのプロセスがメモリを使用しているかどうかを確認し、Oracle Databaseのプロセスが原因だとわかった場合は、どのセッションや処理が原因になるのかを確認することで早期解決につなげることができます。

▼メモリを使用しているセッションを調べる方法
https://www.ashisuto.co.jp/db_blog/article/20170322_session_memory.html

また、PGAで完結できなくなるぐらい処理が遅くなる場合は、一時表領域内の一時セグメントを使用します。原因究明のためには、一時表領域を使用していたプロセスの特定が重要です。以下では調査方法を3つ紹介していますので是非ご参照ください。

▼一時表領域を使用していたセッションを調べる方法
https://www.ashisuto.co.jp/db_blog/article/201706_temp_session.html

データファイル

特定のデータファイルに対するI/Oが集中しないようにします。そのため全表スキャンを行っているSQLを索引スキャンに変更してI/O量を削減したり、複数ディスクにアクセス頻度の高いデータを持つファイルを分散配置するなどの対応を行います。

オンラインREDOログ・ファイル

LGWRによるREDOログレコードの書き込みが待機しないようにします。そのために、複数ディスクにオンラインREDOログ・ファイルを分散配置したり、オンラインREDOログ・ファイルのサイズや数を調整します。

メモリ、データベースの具体的なチューニング方法については、アシストの研修コース「Oracle パフォーマンス・チューニング」をぜひお試しください。

▼「Oracleパフォーマンス・チューニング」を体系的に学びたい場合はこちら
https://www.ashisuto.co.jp/ojt/course/oracle/list/oracle-tuning.html

事前に情報を取得しておくことにより、パフォーマンスダウンが発生した場合の原因特定の確率は大幅に上がります。トラブルは起きないことが一番ですが、平常時のベースラインを常日頃から把握しておき、有事の際には本記事でご紹介したTipsなどをぜひチューニングに生かしてください。本記事がOracle Databaseを管理される方のお役に立てれば幸いです。

▼弊社サポートに関する詳細はこちらもご覧ください。
https://www.ashisuto.co.jp/support/

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