津島博士のパフォーマンス講座　Indexページ ▶▶

 

皆さんこんにちは、今年は梅雨明けが早かったので、より厳しい夏になりそうですね。夏バテに注意しましょう。
今回は、AWR（Automatic Workload Repository）を使用した分析が難しいとよく聞きますので、AWRの様々なレポートを使用した分析の手順について説明しようと思います。後半に、Oracle Database 12c（Oracle12c）からのAWR関係の拡張機能についても説明していますので、参考にしてください。

1. AWRを使用した分析の流れ
AWRレポートだけで分析する方が多いですが、これが難しいと感じる要因だと思います。AWRレポートにも限界があるので、慣れていないと分からない部分もあります。そのため、他の情報を効果的に使用して、分析を容易にすることを覚えてください。そのような分析の手順を、以下の①～⑥にまとめてみました（AWRを使用するには、Oracle Diagnostics Packが必要ですが、有効な情報が多く提供されているので、是非ともこれを参考にAWRを使用してください）。

  • ① 最初にADDMレポートを参照して参考になるものがないか確認します。 AWRスナップショットを使用して、データベースが自動的に診断を行ったものなので、参考になるものがあるかもしれないので、とりあえず確認してみるのが良いと思います。
  • ② 次に問題がないAWRがあればAWR期間比較レポートを使用します。 正常なときと比較すると問題の特定がしやすいので、AWR期間比較レポートを使用して問題を特定します。
  • ③ そしてAWRレポートを見てデータベース全体の稼働状況を分析します。 最初からAWRレポートを分析するのは大変なので、参考になる情報として①と②を確認して、それでも特定できないのであれば、第3回を参考に問題を特定していきます。
  • ④ インスタンスの問題でなければSQL統計を分析します。 インスタンス・チューニングで改善できないのであれば、第32回を参考に問題のSQLを特定します。
  • ⑤ AWRレポートだけで特定できないときはASHレポートを使用します。 短い期間の遅いSQLは、AWRレポートから特定できない場合があります（スナップショット間で時間がある程度の割合でないと上位にならないからです）。
  • ⑥ SQLが特定できたら第38回のSQLチューニングを行います。

これまで説明していなかったADDMレポート、AWR期間比較レポート、ASHレポートについてもう少し説明します。Enterprise Managerから行うと簡単に行えるので、ここでは出力方法については載せないことにします。

（１）ADDM（Automatic Database Diagnostic Monitor）レポート
ADDMは、AWRスナップショットを取得後に自動的に診断を行い、検出結果として推奨事項（アクションと理論的根拠）を出力します。どのようにチューニングするか判断できない方以外にも、分析する時間を短縮するために、参考にできるものがないか確認してみてください。そのため、Oracle12cからのAWRレポートにも「ADDM Reports」セクションが出力されるようになっています。ただし、SQLについては、高負荷なものを特定するまでとなり、第38回で説明したSQLチューニング・アドバイザを実行するように推奨されます。

（２）AWR期間比較レポート
最適と思われるデータベースでも、時間の経過により徐々にパフォーマンスを低下する場合があります。このような場合には、最適に動作していたときのAWRレポートと比較して、異なるパフォーマンス属性を識別するのが有効です。そのため、二つのAWR期間を比較するAWR期間比較レポートが提供されているので、まずはこれを使用して分析してみてください。ただし、AWRスナップショットは、デフォルトで8日を超えると自動的に削除されるので、正常なAWRスナップショットのペアをベースラインに設定して、削除されないようにしてください。

（３）ASH（Active Session History）レポート
AWRスナップショットは、デフォルト間隔が1時間なので、短い期間にだけ実行された遅いSQLを特定できない場合があります。このような場合に、SQLごとにAWRスナップショットを取得すれば良いのですが、これを行えない場合も多いと思います。そのようなときはASHデータを利用します。ASHは、1秒間隔のアクティブなセッション状態（CPU状態または非アイドル待機状態のセッション時間）を格納しています（10秒間隔にしたものはAWRスナップショットの一部としても出力しています）。そのため、任意の期間のレポートを出力できるようになっています（ただし、これからADDMは実行できないので、必要な方はAWRスナップショットを取得してください）。
主な情報として、以下のような「Top SQL with Top Events」や「Top SQL with Top Row Sources」などがありますが、これが上位SQL（Elapsed Timeの長いSQL）に対する上位イベント（CPUまたは待機イベント）や上位行ソース（実行計画情報）になります。これから特定期間の上位SQLを特定することができます。また、SQL（実行計画のOperation）とイベントを紐づけるのにも使用できます。

img-1

Oracle12cからのAWRレポートには、「Active Session History (ASH) Report」セクションが出力されているので、SQLとイベントを紐づけるのが簡単に行えます。

2. Oracle Database 12cからの拡張機能
ここでは、Oracle12cからAWR関係で追加された機能の中から、便利そうなものをいくつか紹介します。
Oracle12cからは、以下の機能が拡張されて、より分析しやすくなっているので、利用してみてください。

  • ASH分析（ASHの拡張）
  • 期間比較ADDM（ADDMの拡張）
  • リアルタイムADDM（ADDMの拡張）
  • リアルタイム・データベース操作監視（リアルSQL監視の拡張）
  • AWRからのSQL監視レポート（リアルSQL監視の拡張）

（１）AWRからのSQL監視レポート
私は、これが一番嬉しいので、これから説明しようと思います。
AWRから分析するときに、第32回で説明したSQLレポートで実行計画は確認できますが、「Actual Rowsがない」や「イベントが紐づけられていない」などで、分析するには勘と経験が必要でした。ASHレポートでもある程度分かりますが、リアルタイムSQL監視レポートの方が簡単に分析できます。これを後から見るためには、事前（共有プール上にデータがあるとき）に出力しておく必要があり多少面倒でした。Oracle12cからは、AWRスナップショットからSQL監視レポートが出力できるようになっています（ただし、Oracle Tuning Packが必要です）。これを意外と知らない方が多いようなので、出力方法を簡単に載せておきます。
以下のように、dba_hist_reportsビューにcomponent_name=’sqlmonitor’を指定してレポートIDを取得（このときkey1に出力したいSQL_IDを指定）してから、DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAILファンクションにレポートIDとレポート形式を指定して出力します。

SQL> SELECT snap_id, report_id, TO_CHAR(generation_time,’YY-MM-DD HH24:MI:DD’), key1
  2    FROM dba_hist_reports WHERE component_name=’sqlmonitor’ AND key1=’f07zaxy71dfp5′;

   SNAP_ID  REPORT_ID TO_CHAR(GENERATIO KEY1
———- ———- —————– ——————————
      1038       2124 18-06-26 17:48:26 f07zaxy71dfp5
      1038       2125 18-06-26 17:58:26 f07zaxy71dfp5

SQL> set pages 0
SQL> set linesize 1000
SQL> set long 1000000
SQL> set longchunksize 1000000
SQL> select DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(2125, ‘TEXT’) report from dual;

SQL Monitoring Report

SQL Text
——————————
select /*+ monitor */ c2,max(c3),sum(c1) from tab02 group by c2 order by 2
Global Information
——————————
…
SQL ID          :  f07zaxy71dfp5
…
Global Stats
…
SQL Plan Monitoring Details (Plan Hash Value=774632932)
…
 

（２）リアルタイム・データベース操作監視
リアルタイムSQL監視は、単一のSQLだけを監視していましたが、データベース操作として単一のSQLと複合データベース操作のどちらも監視できるようになりました。
単一のSQLは、5秒以上またはパラレル実行されたSQLが自動的に監視されますが、複合データベース操作は、データベース・セッション内に定義された2つの時点間のアクティビティになるので、以下のように開始と終了を指定して監視を行います（例えば、バッチ処理内のSQLを識別して監視するときなどに便利です）。デフォルトでは、5秒以上またはパラレル実行されたSQLが監視されますが、以下のようにDBMS_SQL_MONITOR.BEGIN_OPERATIONファンクションにforced_tracking=>DBMS_SQL_MONITOR.FORCE_TRACKINGを指定すると、MONITORヒントを使用せずに、すべてのSQLを監視することがでます。

SQL> VARIABLE l_dbop_eid NUMBER;
SQL> exec :l_dbop_eid := DBMS_SQL_MONITOR.BEGIN_OPERATION(dbop_name=>'db_op_1', dbop_eid=>:l_dbop_eid,
  2                                                    foced_tracking=>DBMS_SQL_MONITOR.FORCE_TRACKING);

…<SQLの実行>…

SQL> exec DBMS_SQL_MONITOR.END_OPERATION(dbop_name=>'db_op_1', dbop_eid=>:l_dbop_eid);

リアルタイム・データベース操作監視レポートは、DBMS_SQL_MONITOR.REPORT_SQL_MONITORファンクションを使用して生成します（このファンクションで、リアルタイムSQL監視レポートも生成できます）。

（３）リアルタイムADDM
AWRスナップショットごとのADDMよりも細かい間隔で診断できるようになりました。
これまでのADDMは、AWRスナップショットが取得されたときに実行されていましたが、MMONプロセスによって3秒間隔で自動的に実行され、メモリー内のASHデータを使用して、データベース内のパフォーマンス・スパイクが診断されるようになりました。ただし、常に診断すると負荷が大きいので、MMONプロセスが以下の問題（多大な影響を与える一時期な問題）を検出すると、リアルタイムADDM分析をトリガーするようになっています。

問題 条件
高負荷 平均のアクティブ・セッション数が、CPUコア数の3倍を超えている
I/Oバウンド 単一ブロック読取りパフォーマンスがアクティブ・セッションの基準を超えている
CPUバウンド アクティブ・セッションの合計負荷が10%、CPU使用率が50%を超えている
メモリーの過剰割当て メモリー割当てが物理メモリーの95%を超えている
インターコネクト・バウンド 単一ブロックのインターコネクト転送時間の基準を超えている
セッション制限 セッション制限が100%に近い
プロセス制限 プロセス制限が100%に近い
ハング・セッション ハング・セッションが合計セッションの10%を超えている
デッドロックの検出 デッドロックが検出された

 

格納されたレポートは、SQL監視レポートと同じようにdba_hist_reportsビューで確認できます（以下のようにcomponent_name=’perf’を指定します）。

SQL> SELECT snap_id, report_id, TO_CHAR(generation_time,’YY-MM-DD HH24:MI:DD’)
  2    FROM dba_hist_reports WHERE component_name=’perf’;

   SNAP_ID  REPORT_ID TO_CHAR(GENERATIO
———- ———- —————–

リアルタイムADDMレポートは、Enterprise Managerの「リアルタイムADDMレポート」または以下のスクリプトやファンクションで出力します。通常のADDM以外に、これも有効な情報がないか確認してみてください。

  • rtaddmrpt.sql（レポートIDを指定して実行するリアルタイムADDMレポート）
  • rtaddmrpti.sql（データベースとレポート期間を指定して実行するリアルタイムADDMレポート）
  • DBMS_ADDM.REAL_TIME_ADDM_REPORTファンクション（直近5分間のXML形式のリアルタイムADDMレポート）

（４）期間比較ADDM
AWR期間の比較を診断して、期間比較ADDMレポートを生成できるようになりました。
時間の経過により遅くなった場合は、AWR期間比較レポートを分析する必要がありましたが、AWR期間を比較して、パフォーマンスの変化を診断したレポートを出力できるようになりました。そのため、AWR期間比較レポートを見る前に、これを参照して有効な情報がないか確認するのが良いと思います。ただし、これまでのADDMとは異なり、デフォルトでは生成されないので、DBMS_ADDM.COMPARE_DATABASESファンクションやDBMS_ADDM.COMPARE_INSTANCESファンクションなどで行う必要があります。

（５）ASH分析
Enterprise ManagerでASHの分析を行うときに、期間を任意に（5分固定ではなく）できるようになり、ASHレポートもグラフィカルに画面で見れるようになりました。これにより様々な角度から分析できるようになり、より分析しやすくなっています。

（６）AWRレポートの変更履歴
最後に、AWRレポートがバージョン・アップでいろいろ変更されているので、情報を整理するために変更内容をまとめてみました。

(a)Report Summaryの変更
Oracle Database 11gR2(11.2.0.4)から以下のように情報が追加されています（Top 5からTop 10になったのが嬉しいです）。

11.2.0.3までのセクション 11.2.0.4以降のセクション Oracle12cから拡張された情報
Cache Sizes – –
Load Profile Load Profile In-Memory統計が追加
Instance Efficiency Percentages Instance Efficiency Percentages ‘Flash Cache Hit %’が追加
Shared Pool Statistics – –
Top 5 Timed Foreground Events Top 10 Foreground Events by Total Wait Time ‘Wait Avg(ms)’が小数点以下2桁まで表示
(0になるものを削減）
– Wait Classes by Total Wait Time –
Host CPU Host CPU –
Instance CPU Instance CPU –
– IO Profile –
Memory Statistics Memory Statistics –
– Cache Sizes In-Memory統計が追加
– Shared Pool Statistics –

 

(b)Main Reportの変更
Oracle12cから以下の3つのセクションが追加されています（前半でも説明していますが、「ASH Report」と「ADDM Reports」を追加されたのが大きいです）。

  • Replication Statistics (GoldenGate, XStream)
  • Active Session History (ASH) Report
  • ADDM Reports

Oracle12cR2からの「Wait Events Statistics」セクションに、CDBレベルのみ「Top 10 Channel Waits」が追加されています（Reliable Message待機イベントが上位に来ているときの分析用に追加されています）。
Reliable Messageは、データベース内の様々なチャネル通信を追跡する待機イベントになるので、そのチャネル通信を特定するのに手動でgv$channel_waits/v$channel_waitsを検索していましたが、それが必要なくなりました。

(c)全体的なレベルの変更
Oracle12cからExadata用として以下のセクションが追加されています。

  • Exadata Configuration and Statistics

Oracle12cR2から以下がサポートされています。

  • PDBレベルのスナップショット
  • 時間が短いものが0にならないように、’Avg Wait’にns(ナノ秒)やus(マイクロ秒)の出力

3. おわりに
今回はAWRの分析について説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。

ページトップへ戻る▲ 

 

津島博士のパフォーマンス講座　Indexページ ▶▶