Oracleのパフォーマンス改善にはSQLアドバイザか、メモリー関連アドバイザを推奨する

Oracleには、稼働し続けるために自己監視機能が備わっています。Oracleという世界一の知名度を誇るデータベースソフトウェアであっても、何かしらの障害によりパフォーマンス悪化に陥る場合があります。そうならないために、日々パフォーマンスの状態を把握し、何かしらの黄信号が灯れば、その原因を突き止めて、対策を施す必要があります。この一連の流れを司る診断する機能がADDM(自動データベース診断モニター)です。

覚えるべきキーワード
・AWR(自動ワークロードリポジトリ)
・MMON
・AWRスナップショット
・ADDM
・SQLチューニングアドバイザ
・メモリーアドバイザ
・SGAアドバイザ
・PGAアドバイザ
・バッファキャッシュアドバイザ

ADDMはSQLやメモリーを解析して、パフォーマンス悪化のおおよその原因を突き止めます。それがSQLか、SGAか、PGAか、などのレベルで分析結果を伝えてくれます。

AWRスナップショットがADDMの分析対象

ADDMの分析対象はAWRスナップショットです。AWRスナップショットはシステムの状態に関する統計データです。
バックグラウンドプロセスのMMONがスナップショット形式で収集します。AWRスナップショットはOracleのデフォルト設定では60分に1回のペースでMMONが取得します。(60分に1回の設定はEM Cloud ControlやPL/SQLで変更が可能です。)

ADDMは、60分前のAWRスナップショットと今取ったAWRスナップショットを比較して、システム状況の変化を分析します。AWRスナップショット取得後は自動的にADDMが自動実行されます。
AWRスナップショットは事前構成済み表領域である、SYSAUX表領域のAWR(自動ワークロードリポジトリ)に格納されますので、ADDMは、このAWRからAWRスナップショットを取得し、分析をします。ADDM分析では、まず大まかな症状を識別した後に根本原因が分かるまでひたすら分析します。

ADDMの分析指標

ADDMは、あらかじめ設定されている特定のシステム属性の統計セットを基準にシステム状況の善し悪しを判断します。●●が一定数値以上だったら、▲▲がおかしいなどと分析する基準(メトリック)があらかじめ設定されていて、それに沿ってADDMが診断をします。これは、Oracleが自動でやってくれるので、DB管理者がすることは何もしません。しいて言えば、ADDMによる分析結果を待つだけです。

ADDMの分析結果

ADDMの分析結果はEM Expressから確認ができます。(ADDM結果の表示場所は他サイトでご確認ください。たしかパフォーマンス・タブページだったような。)
EMexpressで表示されるADDMの分析結果は、一般的なADDM、リアルタイムADDM、最新のADDM結果の3点です。
一般的なADDM:従来のADDMです。デフォルトだと60分に1回ペースです。
リアルタイムADDM:大きな影響を与える問題をリアルタイムで検出するADDMです。
最新のADDM結果:5分間のADDM分析結果です。

ADDMの分析結果はADRに格納されます。ADDMは分析結果をもとに、適切なアドバイザの実行を推奨します。自動実行するのではなく、あくまで最適なアドバイザの実行をおすすめします。
仕組みとしては、ADDMが「高負荷SQLが原因っぽいぞ」と考えたら、SQLチューニングのアドバイザ、「SGAが限界っぽい」と感じたら、メモリー関連のアドバイザを実行するよう推奨してくれます。それに沿ってアドバイザを実行すると、さらに深い分析が実行されるので、直接的な原因が判明します。

ADDMがすすめるパフォーマンスのアドバイザは?

ADDMが提供するパフォーマンス関連のアドバイザには、SQLチューニングアドバイザ、メモリーアドバイザ、SGAアドバイザ、PGAアドバイザ、バッファキャッシュアドバイザです。けっこうありますが、DBのメモリー管理方法により、使えないアドバイザもありますので、各DBで使えるアドバイザは2、3個になります。

SQLチューニングアドバイザ

SQLチューニングアドバイザは高負荷のSQLをチューニングするアドバイザです。対象はSELECT文のみです。チューニングの方法は主に4つあります。

具体的なチューニング方法

①SQLプロファイルの作成・変更
性能を上げるためにオプティマイザが実行計画を作成しますが、その際にオプティマイザはSQLプロファイル情報を考慮します。
SQLプロファイルとは作成したSQLの補足情報をまとめたものです。「AというSQLはテーブルの結合方法を変えたら、性能が上がる可能性がある」などの情報です。オプティマイザが実行計画を作成する際にはSQLプロファイルを参考にするので、事前に用意しておけば、質の高い実行計画が作成できます。
②オプティマイザ統計のリフレッシュ
オプティマイザはリフレッシュしてから反映されます。新オプティマイザが作成した実行計画などを反映させたい時はリフレッシュが必要です。
③索引の作成
索引とは、本の目次のようなものです。例えば、本の読みたい箇所が何章目かを分かっているなら、一般的な読者は目次情報からその章に飛んで、そこから該当箇所を探すでしょう。この場合、最初から(1ページ目から)1ページずつ探すと長時間かかります。Oracleも同じです。大量のデータをイチから選別すると長時間かかるので、索引に設定したカラムをWHERE句に使用することで、とても速くデータを取得できます。SQLパフォーマンスアドバイザが索引を必要と判断したなら、索引作成を推奨します。
④SQLの再構築
SQLを再度構築し直すアドバイスもあります。見たことはないのですが、おそらく結合やwhere句の設定などではないかと思います。

SQLチューニングアドバイザが対象とするSQL

SQLチューニングアドバイザはすべてのSQLを解析はしません。改善効果が高いSQLを集中的に解析します。具体的にはトップアクティビティ、履歴SQL、SQLチューニングセットの3つを重点的に調査します。
①トップアクティビティ
一時間以内に実行されたSQLの中で最もリソースが集中していたSQL。直近の性能が悪いとしたら、このSQLが原因かもしれません。
②履歴SQL
AWRに記録されたSQL。AWR保存期間中に実行された負荷の高いSQLはAWRに記録されます。
③SQLチューニングセット
ユーザーが指定したSQL文とそれに関連するメタデータを1つのデータオブジェクトとしてグループ化したものです。

自動SQLチューニングアドバイザ

本来、SQLチューニングアドバイザは手動で実行しますが、11g以降は自動実行されるようになりました。これを自動SQLチューニングアドバイザと言います。
さらに推奨事項(SQLプロファイルの作成・変更、オプティマイザ統計のリフレッシュ、索引の作成、SQLの再構築)のうち、SQLプロファイルの作成・変更も自動実行できるようになりました。(デフォルトは無効なので有効にする必要あり)ただ、自動実行には条件があって、SQLプロファイルの作成・変更でSQLの性能が3倍以上アップする場合に限って(つまり、確実に性能アップする場合に限って)自動実行します。

メモリー関連のアドバイザ

SQLではなく、メモリーがパフォーマンス悪化の原因だと判断した場合はメモリー関連のアドバイザを実行します。
メモリーには大きく3つの管理方法があります。

メモリーアドバイザ

一番シンプルなメモリー管理が自動メモリー管理です。ざっくり合計メモリーを初期設定しておけば、あとはでデータベースが調整して、SGAとPGA間で適度にメモリー容量を割り当てて、合計メモリーを越えないように管理してくれます。
メモリー不足により、メモリーアドバイザがメモリー割り当てを増やすよう推奨した場合には、管理者はMEMORY_TARGET(メモリーの初期化パラメータ)値を大きな値に変更します。ただ、どこまで大きくすべきかが分からないと思いますので、その時はEM Expressを使ってください。メモリー設定値とそれによって節約される時間予測が表示されているページがあるので、MEMORY_TARGET設定値の参考になります。

SGAアドバイザ+PGAアドバイザ

自動メモリー管理をOFFにすると、メモリー(SGAとPGA)は自動共有メモリー管理と自動PGAメモリー管理で管理されます。SGAの不足が原因の場合はSGAアドバイザがSGA_TARGET(SGAの初期化パラメータ)、PGAの不足が原因の場合はPGAアドバイザがPGA_AGGREGATE_TARGET(PGAの初期化パラメータ)の変更を推奨するので、管理者は大きな値に変更します。

バッファキャッシュアドバイザ+PGAアドバイザ

自動メモリー管理も、自動共有メモリー管理もOFFにすると、メモリーは手動共有メモリー管理と自動PGAメモリー管理で管理されます。この場合、バッファキャッシュサイズの不足が原因の場合はバッファキャッシュアドバイザがDB_CACHE_SIZE(バッファキャッシュの初期化パラメータ)、PGAの不足が原因の場合はPGAアドバイザがPGA_AGGREGATE_TARGET(PGAの初期化パラメータ)の変更を推奨するので、管理者は大きな値に変更します。

もちろん、最終的な実行決定権はDBA(データベース管理者)ですが、アドバイザがアクションを推奨する場合は素直に実行するのが得策です。DB障害が発生してからでは遅いので、前のめりで対策を実行していきましょう。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です