Oracleデータベースのパフォーマンスチューニング徹底解説：DBAが押さえるべき診断ツールと最適化テクニック

データベースのパフォーマンスチューニングは、すべてのデータベース管理者（DBA）が継続的かつ定期的に実施すべき、最も基本的でありながら最も重要な業務の一つです。専門家は、データベースを定期的にファインチューニングしてパフォーマンスを最適化することを推奨しています。適切なチューニングを行えば、ユーザーはレポートやクエリを高速に実行し、より迅速に結果を取得できるようになります。本記事では、Oracle®データベースのトラブルシューティングとチューニングに役立つ実践的なテクニックを紹介します。

チューニングの基本ステップ

まず最初に行うべきは、問題が発生している領域の特定です。問題の原因となり得る領域としては、オペレーティングシステム、データベース自体、メモリ不足などが挙げられます。問題領域を特定した後は、その領域を重点的にチューニングすることで、最大限の効果を得られます。本記事では、その中でもパフォーマンスチューニングに焦点を絞って解説します。

OracleはDBA向けに、問題の診断やパフォーマンス障害のトラブルシューティングを支援する複数のツールを提供しています。代表的なものが、Automatic Database Diagnostic Monitor（ADDM）とAutomatic Workload Repository（AWR）レポートです。これらは問題が発生していた時間帯を対象に生成でき、分析に活用できます。これらのツールを使って、偏りのあるコンポーネントや調整可能な箇所を見つけ出しましょう。

AWRレポートの「Top Timed Events」セクション

AWRは、特定のインスタンスにおけるメモリ使用状況などを統計形式でまとめたレポートを生成できるツールです。AWRレポートを生成するには、sqlplusプロンプトで以下のファイルを実行します。

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

これにより、事前登録されたテンプレートをもとに、HTML形式のAWRレポートが生成されます。レポート作成後は、Top Timed Eventsセクションをすぐに確認しましょう。このセクションには、時間を最も消費しているイベント、つまり問題領域が表示されます。以下の画像をご参照ください。

ADDMチューニングセッション

通常、問題が報告されると、DBAはその箇所だけを調査したり、目の前の問題のみを解決したりしがちです。しかし、問題が正しく特定・対処されていなければ、将来的により深刻な障害につながる恐れがあります。ここでDBAは全体像を見失いがちです。そこでOracleは、DBAがデータベースの問題を包括的かつ正確に把握できるよう、ADDMというツールを用意しています。ADDMチューニングセッションは、手動のチューニング作業とほぼ同じ手順で進められます。以下の画像は両者の比較を示したものです。

画像出典：Oracle 11G Performance Tuning トレーニングマニュアル

ADDMレポートは、SQLまたはOracle Enterprise Manager（OEM）経由で取得できます。以下の画像はサンプルSQLの例です。

不良SQLと実行計画の確認

AWRやADDMのレポートで問題のあるSQL／SQLIDが特定できたら、DBMS_XPLANを使って詳細情報を収集します。DBMS_XPLANパッケージは、実行計画（exec plan）を取得・表示するための以下の表関数を提供しています。

DISPLAY
DISPLAY_AWR
 select plan_table_output from table (DBMS_XPLAN.DISPLAY_AWR('fs22b3fgfh8xc'));
DISPLAY_CURSOR

以下の画像は、EXPLAIN PLANコマンドの出力例です。クエリがフル表スキャン（全表走査）を行っているのか、あるいはインデックスを使ってデータを絞り込んでいるのかを確認できます。

ADDMレポートを見れば、新しいインデックスを作成することでパフォーマンス向上が見込めるかどうかも判断できます。さらに、OEMからSQL Tuning Advisorを実行すれば、クエリを微調整し、より優れた実行計画を採用できる可能性があります。多くの場合、問題のあるSQLにより良い実行計画を適用するだけで、主要なパフォーマンス問題は解決します。

SQL Tuning Advisorの活用

SQL Tuning Advisorは、SQLID（SQL認証ID）をもとにSQL文を分析し、パフォーマンス改善のための推奨策を提示してくれるツールです。分析対象となるソースは以下の3種類です。

• Top activity： 現在実行中の上位SQL文を分析します。
• SQL tuning sets： ユーザーが指定した一連のSQL文を分析します。
• Historical SQL（AWR）： AWRスナップショットで収集された過去のSQL文を分析します。

以下のスクリーンショットは、それぞれの使用例を示したものです。

長時間実行リクエストへの対処

Oracle E-Business Suiteのデータベースでは、一部の同時実行リクエストが延々と走り続けることで、長時間実行リクエストの問題が発生することがあります。これを解決するには、該当する同時実行リクエストに関連づけられたデータベースセッションの詳細情報を収集する必要があります。以下の画像は、情報収集の手順を示したものです。

Oracleデータベースのメモリパラメータ

AWRレポートを確認すれば、キャッシュヒット率が他と比べて低いキャッシュを容易に特定でき、どのキャッシュにチューニングが必要かが分かります。以下の画像は、インスタンス全体のメモリチューニングにおいて検討すべき主要なデータベースパラメータの一部です。

画像出典： https://ora-performance-tuning.blogspot.com/2014/02/automatic-shared-memory-management.html

よく見られる待機イベント

以下の表は、代表的な待機イベントとその考えられる原因をまとめたものです。

表出典：Oracle Performance Tuning 11G OCP トレーニングマニュアル 第20章 24ページ

DBAが直面する10大チューニング課題

顧客環境でよく見られる、代表的な10のチューニング課題を紹介します。

• 不適切な接続管理： 開発者はアプリケーション内でデータベース接続のコードを書いたり、データ取得のためのクエリを実行したりします。データ取得が完了し、それ以上の処理が不要になった時点で、接続はクローズすべきです。しかしこれが実行されないケースが多く、結果としてデータベース内に非アクティブセッションが増加します。これらのセッションは、本来アクティブな接続に使われるべき貴重なリソースを浪費します。

• カーソルと共有プールの不適切な利用： カーソルは開発者が持つ強力なツールですが、実際にはほとんど活用されていません。カーソルを使わないと、Oracleはコードが実行されるたびにハードパースを行うことになり、繰り返し実行されるSQLクエリのパフォーマンスに大きな悪影響を及ぼします。DBAは、AWRレポートのInstance Efficiency Percentages - Execute to Parse %セクションを確認することで、この問題を検出できます。

• 不良SQL： SQLクエリの書き方（結合条件など）は、実行時のパフォーマンスに大きく影響します。大規模なテーブルに対するフル表スキャンは避けるべきです。SQLを作成したら、開発者とDBAがexplain planを実行し、データベース上での実行コストを把握しておきましょう。カーソル、バインド変数、インデックスを適切に使うことで、効率を大幅に高められます。

• 非標準の初期化パラメータの使用： DBAは常に標準または推奨される初期化パラメータのみを使うべきです。非標準のパラメータは、Oracle Service Request（SR）で明示的に提案された場合に限定して使用してください。

• データベースI/O設計の誤り： DBAがデータベース用ハードウェアを選定する際は、データベースを複数のディスクに分散配置するとともに、ネットワークチームと連携し、データベースサーバーとエンドユーザー間のデータ転送速度についても協議すべきです。ボトルネックやパフォーマンス劣化を防ぐため、ネットワークスイッチやルーターの性能も考慮に入れましょう。

• REDOログ設定の問題： REDOログは、REDOバッファのデータを保存しておくために必要であり、クラッシュ発生時にOracleがトランザクションをやり直せるようにする仕組みです。REDOログのサイズが不十分だと、ログスイッチが頻発してパフォーマンス問題を引き起こします。アーカイブ生成の負荷増大にもつながります。

• バッファキャッシュ内データブロックの直列化： これはフリーリストグループやUNDOセグメントの不足によって発生します。アクティブユーザー数が多いのにUNDOセグメントが少ない、挿入処理中心のデータベースで起こりやすく、最終的にパフォーマンス劣化につながります。

• フル表スキャン： explain planを実行して、クエリにフル表スキャンが含まれていないか確認しましょう。通常、フル表スキャンが発生するクエリはSQL設計の不備を示しており、インデックスの追加や取得データの絞り込みによって改善できます。ただし、小規模なテーブルの場合など、フル表スキャンがむしろ有効なケースもあります。

• 再帰SQL： 再帰SQLは、正しく使えば開発者にとって強力な味方ですが、諸刃の剣でもあります。適切に実装すれば効率的に結果を返せますが、誤った使い方をするとデータベース全体のパフォーマンスに深刻な悪影響を与えます。

• ディスクソート： ディスク上でのソートは、データベースにとって非常にコストの高い処理です。SQL設計の甘さや最適化不足を示すサインです。Instance Activity Stats – Sorts (disk)のAWRレポートセクションで、この問題を特定できます。

まとめ

DBAはパフォーマンスチューニングにおいて多方面に配慮する必要がありますが、データベースの世界では、チューニングはデータベースおよびアプリケーションの設計段階から始まるものです。パフォーマンスを意識して設計されたデータベースやアプリケーションは、そうでないものに比べてはるかに高い拡張性を持ちます。

本記事で紹介したパフォーマンスチューニングのポイントは、いわば氷山の一角にすぎません。データベース全体のパフォーマンスを維持・向上させるためにも、DBAの皆さんには今後も継続的に学習を深めていくことをおすすめします。

ご意見やご質問がある場合は、フィードバックタブからお気軽にお寄せください。

データベースサービスの詳細については、こちらをご覧ください。

RackspaceはOracle製品に関する豊富な知識と経験を持っています。Oracle投資を最大限に活かす方法について、ぜひご相談ください。