Oracle Database 性能対策機能 〜 StatspackとDiagnostics Packを使いこなす 〜〜 StatspackとDiagnostics Packを使いこなす 〜

名前NTTコムウェア株式会社
プロジェクト管理統括部 技術SE部門 DB技術グループ
坂下 徹

Oracleデータベースの性能対策の重要な機能、「Statspack」や「Oracle Enterprise Manager 10g Diagnostics Pack」(※オプションライセンス)の利用ポイントをお伝えします。使わないと"もったいない"機能です!(Diagnostics Packの有無による違いはここ)

第1回 StatspackとDiagnostics Packの概要と使用方法

1 はじめに 

 ご存じの通り、Oracleは様々な優れた機能を備えています。しかし、それらの機能も各システムの業務要件によって、使うケースと使わないケースに分かれます。ただし、こと性能問題に関しては全てのシステムで起こる可能性があるため、性能対策機能と、その機能に対するデータベース管理者の知識は必須といえます。もちろん、Oracleには優れた性能対策機能も備わっています。その1つがOracle8iで登場したStatspackです。Oracle Database 10gではStatspackを進化させたAWRやADDMという機能が追加され、Oracle Enterprise Manager 10g Diagnostics Pack(以下、Diagnostics Pack)で利用することが可能になりました。しかし、データベース管理者の方はこれらの機能をうまく使いこなしているでしょうか。せっかくよい機能があっても、うまく使いこなせていないのでは意味がありません。

 今回、合計3回の連載の中でStatspackとDiagnostics Packに焦点をあて、それらの機能の特徴、使い方、実際の分析に関するポイントを説明したいと思いますので、皆さんもぜひご活用ください。

 次回の2回目の記事では実際にあるワークロードを実行し、Statspackを用いて分析します。3回目では2回目のワークロードと同じものをDiagnostics Packを用いて分析します。ここではDiagnostics PackがStatspackよりもどれだけ使いやすいかをイメージしていただきたいと思います。また、「オラクル通信No.107」(2007年3月1日発行)にも関連記事を掲載していますので、あわせてご覧ください。

 なお、今回説明する内容やスクリプトの実行結果はOracle Database 10g Release 2(以下、10g R2)のバージョンをベースに説明しています。バージョンに依存する部分は一部ですが補足説明したいと思います。また、Oracle Enterprise Managerは10g Release 2 Database Controlを使用しています。Grid Controlや他のバージョンのOracle Enterprise Managerの場合、若干画面表示やリンクが異なることもありますのでご注意ください。

 では、早速StatspackとDiagnostics Packの概要と使い方について説明します。

インデックスに戻る

2 性能対策概要 

 性能にかかわる業務は「分析」と「対処」の2種類に大きく分かれるかと思います。後者はまさに、性能を意識した物理設計であり、SQLチューニングであり、またはOSのチューニング等の実施そのものです。しかし、これらの対処をするにも、どこを改善すべきかを的確に判断しなければなりません。たとえば、ある性能問題に対して、まったく関係のないSQLをチューニングしても意味がありません。そこで、問題となる箇所がどこかを的確に判断するために重要となるのが前者の「分析」になります。分析さえ的確にできれば、その分析過程の中で対処案もおのずと見えてくることが多いようです。

 性能を分析するにも、その分析対象となる情報を取得する必要があります。たとえばOSのリソース分析をする際には事前にvmstatなどの情報を取得しておかなければなりません。同じく、Oracleでも性能情報を事前に取得しておくための準備が必要になります。それがStatspackであり、Diagnostics Packになります。ただし、Diagnostics PackはStatspack以上のことがStatspackより簡単にできるため、Statspackよりもメリットが大きいといえます。では、まずは基本となるStatspackを説明し、次にDiagnostics Packを説明していきたいと思います。

インデックスに戻る

3 Statspack 

 StatspackはOracleの性能分析をするためのレポートを作成するOracleデータベース標準のツールで、Oracle8iから登場しました。OracleデータベースのEnterprise Editionだけでなく、Standard Editionでも使用できるため、全ての環境で使用することができます。Statspackは指定する期間のデータベース関連の様々な統計情報をレポートとして作成します。データベース管理者はこのレポートを用いて性能を分析することになります。統計情報を取得する期間を指定するために必要となるのがスナップショットです。このスナップショットはSQL*Plusから手動で取得することもできますが、Jobやcronなどを用いて定期的に自動取得するのが一般的です。逆に、スナップショットがなければStatspackレポートも作成することができません。スナップショットさえあれば、過去のどの時点のレポートを作成するかは自由に選ぶことができます(図1-1)。

図1-1 Statspack概要
図1-1 Statspack概要(拡大画像を表示)

 スナップショットの処理そのものは、メモリ(SGA)上にある性能関連の統計データをテーブルへ保存しているだけです。よって、スナップショットの処理そのものもあまり大きな負荷はかからず、また、スナップショットを実行していない時間帯は何も負荷がかかりません。したがって、SQL Traceに比べてオーバーヘッドがとても小さいといえます。もちろんSQL Traceとは取得できる情報が異なるため、うまく使い分けましょう。

 なお、Statspackには様々なスクリプトを使用しますが、Statspackが登場したばかりの8.1.6のバージョンではそれ以降のバージョンとスクリプト名が異なります。本書では8.1.6のスクリプトは紹介しませんのでご注意ください。ただし、それぞれのバージョン毎にある後述のStatspackマニュアルには8.1.6のスクリプト名の説明もありますので、そちらを参照してください。

3.1 インストール 

データベースを作成した直後のままではStatspackを使用することはできません。事前にStatspackをインストールする必要があります。StatspackはPERFSTATと呼ばれるスキーマの、STATS$の文字列で始まる名前の数十個のテーブルから構成されます。このインストールはSYSユーザでspcreate.sqlを実行することで可能です(図1-2)。スクリプト実行の途中、PERFSTATユーザのパスワードやデフォルト表領域、デフォルト一時表領域を指定する必要があります。spcreate.sqlを実行する前に決めておきましょう。

図1-2 Statspackのインストール
図1-2 Statspackのインストール(拡大画像を表示)

 Statspackのインストールをしていないシステムも実はあるのではないでしょうか。既に運用を開始しているシステムの場合、性能を分析するためにまずStatspackをインストールしなければならないのですが、システム管理者やお客様などにStatspackをインストールする許可を得なければならなくなります。また、設計書にもその変更内容を記載しなければなりません。Statspackのインストール作業そのものはとても簡単なのですが、いろいろと面倒なことが多く、肝心の性能分析がすぐに始められないなどの問題が生じます。よって、Statspackは忘れずに運用開始前からインストールしておきましょう。

(補足)スクリプト実行時の「?」マーク
図1-2ではSQL*Plusからスクリプトを呼び出す際に 「@?/…」のように「?」マークを用いていますが、これは$ORACLE_HOMEを表しています。文字入力が楽になるので知っておくと便利です。

3.2 メンテナンス 

 前述の通り、Statspackレポートを作成するためには2つ以上のスナップショットが必要になります。スナップショットはパラメータを変更することで、取得できる情報を変更することも可能です。また、Statspackのスナップショットもテーブルに保存されるため、他の表領域と同様に、いっぱいにならないように古いデータを定期的に削除しましょう。

■ スナップショット
Statspackレポートは2点のスナップショット間の分析をすることになるため、レポートを作成するためにはインスタンス起動後に最低2つのスナップショットが必要になります。スナップショットはPERFSTATユーザでstatspack.snapを実行して作成します。

実行例
SQL> connect perfstat/********
SQL> execute statspack.snap

PL/SQLプロシージャが正常に完了しました。

 この処理を図1-1のようにJobやcronでstatspack.snapが定期的に実行されるように設定しましょう。各システム要件にもよりますが1時間に1回程度の周期で実行するのが一般的です。より細かい時間単位での分析が必要な場合は、その周期を短く設定しましょう。もちろん、スナップショットの回数が増えるので、その分オーバーヘッドは大きくなります。

■ スナップショットパラメータ
statspack.snapプロシージャは様々なパラメータを指定することが可能ですが、特に重要となるのがi_snap_levelです。i_snap_levelを変更することで、スナップショットが取得する情報を変更することができます(表1-1)。

表1-1 i_snap_levelの違い

i_snap_level 取得データ
基本統計 アドバイス SQL統計 SQL詳細 セグメント 親・子ラッチ
Level 0        
Level 5      
Level 6    
Level 7  
Level 10

(補足)statistics_level = TYPICAL または ALL 、かつtimed_statistics = TRUE

Level 6は9.0.1のバージョンから使用可能になり、Level 7は9.2.0のバージョンから使用可能になりました。パラメータを何も指定せずにデフォルトのまま実行するとLevel 5になります。お薦めはLevel 7です。このパラメータの存在に気づかずにデフォルト以外の値を試されたことのない方も多いのではないでしょうか。ご存じなかった方はぜひ試してみてください。

実行例(Level 7 での取得例)
SQL> connect perfstat/********
SQL> execute statspack.snap(i_snap_level => 7)

PL/SQLプロシージャが正常に完了しました。

■ データの監視・削除
Statspackも他のスキーマと同じようにテーブルの集まりから構成されています。スナップショット実行時に各テーブルのレコードが増加します。PERFSTATスキーマの表領域の使用率も他の表領域と同様に監視して、表領域がいっぱいにならないように注意しましょう。また、いらなくなった古い期間のデータも定期的に削除しましょう。削除にはsppurge.sqlかsptrunc.sqlを使用します。sppurge.sqlは削除したいデータの期間を指定することができます。sptrunc.sqlは全ての期間のデータを削除します。なお、sppurge.sqlでも全ての期間を削除することはできますが、sptrunc.sqlは内部的にTRUNCATE処理を行っているためsppurge.sqlよりも高速です。

実行例
SQL> connect perfstat/********
SQL> @?/rdbms/admin/sppurge.sql
または
SQL> @?/rdbms/admin/sptrunc.sql

3.3 レポート作成 

 では、ここから本題のStatspackレポートの作成方法です。レポートは2種類あります。通常のStatspackレポートに加え、スナップショットのi_snap_levelを6以上にしていれば、SQLの詳細情報がわかるレポート(Statspack SQLレポート)も作成することができます。

■ Statspackレポート
全ての分析の始まりは、このStatspackレポートの作成作業から始まります。Statspackレポートの作成にはspreport.sqlを使用します。途中で、レポート対象としたい期間の開始時点と終了時点を入力するように求められます。「Listing all Completed Snapshots」欄にSnap Id(スナップショットを識別するID)とそれに対応する時間の一覧が表示されるので、その中からそれぞれのSnap Idを選びます。また、出力ファイル名も入力します。Statspackレポートは出力ファイル名にパスを指定しなければ、SQL*Plusを実行したカレントディレクトリに作成されます。次の例では2007年1月16日19:38〜2007年1月16日19:44の期間のStatspackレポートを作成しています(図1-3)。

図1-3 Statspackレポートの作成
図1-3 Statspackレポートの作成(拡大画像を表示)

実際に作成したレポートはこちらです。図1-3のSnap Levelの欄を見ていただくとわかる通り、今回作成したレポートはLevel 5のレポートです。同じ時間帯を対象としてLevel 7で作成したレポートがこちらになります。若干スナップショットの時間が異なるため、各種統計情報の値が微妙に異なりますが、大きな違いといえば、Level 7のレポートには「Segments by …」の欄があることです。これが表1-1で説明しているLevel 7のセグメント情報になります。

セグメント情報抜粋
Segments by Logical Reads  DB/Inst: O102EE1/o102ee1  Snaps: 814-816
-> End Segment Logical Reads Threshold:     10000
-> Pct Total shows % of logical reads for each top segment compared with total
   logical reads for all segments captured by the Snapshot

      Subobject Obj. Logical Pct
Owner
----------
Tablespace
-----------------
Object Name
--------------------
Name
------------
Type
---------
Reads
------------
Total
-------
SCOTT USERS EMP   TABLE 126,592 86.1
SYS SYSAUX SYS_IOT_TOP_8813   INDEX 5,264 3.6
SYS SYSTEM TS$   TABLE 3,776 2.6
SYS SYSTEM USER$   TABLE 2,944 2.0
SYS SYSTEM I_OBJ1   INDEX 1,392 .9
--------------------------------------------------------------------------------------

Level 5では表領域単位、データファイル単位にしかわからなかったI/Oの量が、Level 7ではセグメント単位(表や索引単位)でわかるようになります。たとえば、同じ表領域に複数のテーブルが存在する場合、I/Oの量を調べてもLevel 5では表領域内のどのテーブルにアクセスが集中しているのかがわかりませんが、Level 7では一目瞭然となります。(補足:上記抜粋にはPhysical Reads欄のみ記載。詳細はこちらをご覧ください)
今回のLevel 7のレポートではLogical ReadsとPhysical Readsのセグメント情報しか表示されていませんが、その他のセグメントに関係するイベントが発生していれば、その情報もレポートに表示されます。

表示されるセグメント情報
Logical Reads per Segment
Physical Reads per Segment
Buffer Busy Waits per Segment
Row Lock Waits per Segment
ITL Waits per Segment
etc.

■ Statspack SQLレポート
先ほどは、Level 5と7の違いしか説明しませんでしたが、Level 5と6の違いがSQL詳細情報です。Level 6ではStatspack SQLレポートを作成することでSQLの詳細情報を知ることができます。 Statspack SQLレポートを作成するには、既に作成済みのStatspackレポートを参照しながらsprepsql.sqlで作成します。ここでは先ほど作成したLevel 7のレポートを参照してStatspack SQLレポートを作成しています(図1-4)。Statspackレポートの「SQL ordered by …」の欄にはそれぞれの単位で悪かった順にSQLの一覧が表示されます。ここではCPUの処理時間が最も長かったSELECT文を分析することにしましょう。sprepsql.sqlを実行すると、spreport.sqlと同様に開始・終了のSnap Idと出力ファイル名の入力を要求されます。違いはhash_valueの入力です。分析したいSQLのHash Value(9i までは単純にHash Valueであったが、10gでは“Old”Hash Valueと表示が変わった)をStatspackレポートから調べ、その値を入力しましょう。

図1-4 Statspack SQLレポートの作成
図1-4 Statspack SQLレポートの作成(拡大画像を表示)

実際に作成したレポートがこちらになります。
いかがでしょう。実行されたSQLの処理時間(Elapsed Time)、検索されたレコード数(Rows processed)等の情報に加え、実行計画まで確認することができます。SQL Traceで実行計画を確認したい場合、SQL Traceをセットすることによるオーバーヘッドが無視できませんでした。また、Explain Planによって、実行計画を調査することも可能ですが、実際のSQLが実行された時と、Explain Planを実行した時とでは既に実行計画の内容が変わってしまっていることもあります。これらの課題をStatspack SQLレポートが解決してくれました。ただし、Statspackの場合は、Statspack自身が遅いと判断したSQLしかスナップショットに保存されません。つまり、対象期間内に実行された全てのSQLを分析できるわけではありません。またSQL TraceのほうがStatspackのSQLレポートより詳細な情報を取得することができます。StatspackとSQL Traceは要件に応じて使い分けましょう。

(補足)Statspackのマニュアルについて
Statspackの詳細説明は9iまではマニュアルの「パフォーマンス・チューニング・ガイドおよびリファレンス」に記載されていますが、$ORACLE_HOME/rdbms/admin/spdoc.txtも一度参照してみてください。全て英語で書かれているのですが、「パフォーマンス・チューニング・ガイドおよびリファレンス」に記載されていないことも一部記述されています。また、10gからはStatspackの記述がマニュアルから削除されてしまったため、spdoc.txtがより重宝です。
8.1.6ではstatspack.docというファイル名で保存されています。今回8.1.6の各種スクリプト名や操作方法を紹介しませんでしたが、8.1.6を使用される方はこちらをご覧ください。ただし、そもそも8.1.6は既にサポートを終了しているので、その点はご注意を。

3.4 Statspackが不向きなケース 

 既に説明している通り、Statspackはある2つの時点の間の各種統計をレポートとして出力します。しかし、瞬間的に発生する事象はStatspackでは分析しきれないなどの弱点があります。たとえば、Statspackのスナップショットは1時間に1回単位なのに、あるイベントが数秒間に集中して起こった場合などです(図1-5)。実際にはその数秒間の集中が性能トラブルの原因となっているのですが、Statspackでは1時間の合計としてその事象を捉えるため、そのイベントがある特定の時間に集中していることを見つけ出すことができないのです。

図1-5 Statspackの弱点
図1-5 Statspackの弱点(拡大画像を表示)

 これらの事象を見つけ出すためにはV$SESSIONやV$SESSION_WAITなどのセッション情報をStatspackのスナップショットより細かい時間間隔で監視することが必要です。

 セッションの分析の詳細についてはここでは省略しますが、以下の資料がとても参考になりますので興味のある方はご覧ください。

DBコンサルの”遅延している現場”をおさえるパフォーマンストラブルのシューティングテクニック
日本オラクル株式会社 コンサルティングサービス本部 シニアプリンシパルコンサルタント
小田 圭二

インデックスに戻る

4 Diagnostics Pack 

 Diagnostics Packは性能分析に役立つ機能を提供するオプションライセンスです。Diagnostics Packは9iのバージョンにもありましたが、10gでより進化した機能が提供されました。Diagnostics Packは主に以下の機能から構成されます。

 ここでは、これらの機能の特徴と使い方を説明したいと思います。

4.1 Automatic Workload Repository (AWR) 

 AWRはStatspackを進化させたものです。Statspackと同様に、各種レポートを作成することができますが、Statspackより種類も多く、見やすいレポートを作成することが可能です。また、Oracle Enterprise Manager(以下、Oracle EM)から操作、参照することができるため、初心者の方でも簡単に使うことができます。

■ インストール
Statspackはデータベース作成時にインストールする必要がありましたが、AWRはデータベース作成時に既にインストールされているため、余計な作業が不要です。AWRはSYSAUX表領域に作成されています。また、スナップショットの取得や古いデータの削除も自動的に実行されるようになっています。デフォルトでは1時間に1回のスナップショットで、7日間保存される設定です。設定内容の確認、変更にはOracle EMを利用します(図1-6)。

図1-6 AWRの設定確認・変更
図1-6 AWRの設定確認・変更(拡大画像を表示)

(補足)収集レベルについて
収集レベルは初期化パラメータのstatistics_levelを表しています。デフォルトはTYPICALです。より低いレベルのBASICでは分析に必要な様々な情報が不足したり、AWRとは関係のない他の機能が必要とする情報も欠落してしまいます。また、より高いレベルのALLにするとより多くの情報を取得するため、通常の処理への負荷が無視できなくなります。通常の分析であればTYPICALで十分です。これはStatspackでも同じことがいえます。なお、AWRではTYPICALの場合、Statspackのi_snap_level 7相当の情報が取得されます。

AWRも保存しているスナップショット数が多くなれば表領域の使用率が高くなります。SYSAUX表領域も忘れずに領域監視を行い、必要に応じてスナップショット周期や保存期間を調節しましょう。
SQL*Plusなどのコマンドプロンプトで内容確認、変更をしたい場合は、それぞれDBA_HIST_WR_CONTROLとDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGSを使用します。DBA_HIST_WR_CONTROLのSNAP_INTERVAL列がスナップショットの周期で、RETENTION列が保存期間です。次の例では1時間周期7日間保存の設定を30分周期10日間保存に変更しています。

実行例
SQL> select * from DBA_HIST_WR_CONTROL;
DBID
------------------
2384218455
SNAP_INTERVAL
---------------------------
+00000 01:00:00.0
RETENTION
---------------------------
+00007 00:00:00.0
TOPNSQL
--------------------
50000
SQL> BEGIN
  2   DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
  3   ( retention => 14400, -- 保存期間(分)
  4   interval => 30); -- スナップショット周期(分)
  5   END;
  6   /

PL/SQLプロシージャが正常に完了しました。

SQL> select * from DBA_HIST_WR_CONTROL;

DBID
------------------
2384218455
SNAP_INTERVAL
---------------------------
+00000 00:30:00.0
RETENTION
---------------------------
+00010 00:00:00.0
TOPNSQL
--------------------
50000

■ レポート作成
AWRのレポートには以下の3つがあります。

AWRレポートおよびAWR SQL レポートはStatspackにも同様のレポートがありますが、AWR期間比較レポートはAWRでのみ提供されるレポートです。では、それぞれのレポートの作成方法を見ていきましょう。

[AWRレポート]
AWRレポートの作成は次の通りです(図1-7)。AWRレポートの作成もOracle EMによるGUI操作で作成することができます。作成にはStatspackと同じように、スナップショットの開始、終了IDを指定します。

図1-7 AWRレポートの作成
図1-7 AWRレポートの作成(拡大画像を表示)

(補足)「アクション」メニューの注意点
レポート作成画面において、各種リンクやボタンをクリックしていると、「アクション」メニューの選択がデフォルトの「保存スナップショット・セットの作成」に戻ってしまうことがあります。当然そのまま実行してしまうと、「保存スナップショット・セットの作成」を行ってしまうことになります。「実行」ボタンをクリックする前に、選択されているメニューが「レポートの表示」になっているかどうかを確認しましょう。

(参考)「保存スナップショット・セットの作成」とは
既に説明している通り、デフォルトの設定ではスナップショットは7日間保存です。8日以上前のデータを保存しておきたい場合は保存期間を長くすればいいのですが、保存されるデータ量が増えてしまうため、当然表領域の使用率が高くなってしまいます。そこで、保存スナップショット・セットが役に立ちます。重要なイベントがあったとき(月末バッチの時間帯、性能トラブルが起こった時間帯、基本となる性能モデルのワークロードを流した時間帯、等)のスナップショットをこの機能で保存しておくことで、その期間のスナップショットは、保存期間を過ぎても削除されなくなります。無駄な領域を使用せずに、重要な情報だけ保存したい場合に便利です。コマンドラインから実行したい場合は、DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINEを使用します。

AWRレポートを作成すると、画面上にAWRレポートがHTML形式で表示されます(図1-8)。作成したAWRレポートは「ファイルに保存」ボタンをクリックして保存しておきましょう。

図1-8 AWRレポートの表示
図1-8 AWRレポートの表示(拡大画像を表示)

実際に作成したレポートはこちらです。
コマンドラインでレポートを作成する場合はSYSTEMユーザやSYSDBA権限のユーザでawrrpt.sqlを実行することで作成可能です(図1-9)。操作方法はStatspackとほぼ同じですが、num_daysの入力を求められるところが異なる点です。これはスナップショットの一覧表示期間を過去何日間にするか指定するための入力パラメータです。たとえば、今日のレポートを作成したい場合は、昨日以前のスナップショットIDを知る必要がないわけですから、スナップショット一覧を1日と指定すれば見やすくなります。また、awrrpt.sqlを使用すれば、HTML形式だけではなく、TEXT形式のレポートも作成可能です。

図1-9 AWRレポートの作成(スクリプト使用)
図1-9 AWRレポートの作成(スクリプト使用)(拡大画像を表示)

[AWR SQLレポート]
10g R2より、AWRでもSQLの詳細レポートを作成することが可能になりました。ただし、10g R2ではGUIによるSQLレポート作成には一部制限があるため、AWR SQLレポートの作成にはawrsqrpt.sqlを使用します。AWRレポートのスナップショットIDと分析したいSQLのSQL IDを事前に調べておき、awrsqrpt.sqlの入力パラメータとして指定します(図1-10)。

図1-10 AWR SQLレポートの作成(スクリプト使用)
図1-10 AWR SQLレポートの作成(スクリプト使用)(拡大画像を表示)

実際に作成したレポートはこちらです。

(参考)Oracle EMでSQLの詳細情報を参照したい場合
ここで紹介したAWR SQL レポートとは異なりますが、下記に関しては表示されるSQL IDをクリックすることにより、Oracle EMのGUI画面よりSQL詳細情報を参照することが可能です。
 -ADDMの分析で確認できたSQL文
 -トップ・アクティビティで確認できる上位SQL(後述の図1-27)
 -パフォーマンス(履歴)ページにリンクが現れる履歴SQL(AWR)

[AWR 期間比較レポート]
AWRには期間比較レポートというものもあり、10g R2の新機能です。これはある2つのAWRレポートを並べて比較することができるレポートです(図1-11)。たとえば、性能トラブルが起こった際に、通常に運用できていた期間と比較して何が原因なのかを分析したいときに便利です。また、性能改善の対処を実施した後で性能改善対処前のレポートと、改善後のレポートを比較して、その効果を確かめたいときにも便利でしょう。

図1-11 AWR期間比較レポート
図1-11 AWR期間比較レポート(拡大画像を表示)

AWR期間比較レポートの作成は次の通りです。まず比較対象となる1つ目のスナップショット期間を指定します(図1-12)。

図1-12 AWR期間比較レポートの作成(1/2)
図1-12 AWR期間比較レポートの作成(1/2)(拡大画像を表示)

次に、比較対象となる2つ目のスナップショット期間を指定します(図1-13)。

図1-13 AWR期間比較レポートの作成(2/2)
図1-13 AWR期間比較レポートの作成(2/2)(拡大画像を表示)

すると、指定した2つの期間を対象としたレポートが表示されます(図1-14)。このレポートも「ファイルに保存」ボタンで保存可能です。

図1-14 AWR期間比較レポートの表示
図1-14 AWR期間比較レポートの表示(拡大画像を表示)

実際に作成したレポートはこちらです。
また、スクリプトを使用する場合はawrddrpt.sqlを使用します(図1-15)。

図1-15 AWR期間比較レポートの作成(スクリプト使用)
図1-15 AWR期間比較レポートの作成(スクリプト使用)(拡大画像を表示)

4.2 Automatic Database Diagnostic Monitor(ADDM) 

 StatspackもAWRもレポートの内容を理解し、分析するためにはそれなりに高いレベルのOracleのパフォーマンスチューニングに関する知識が必要となります。そこでADDMが役に立ちます。ADDMはある程度のパフォーマンス分析を自動的に実施し、その分析結果をレポートとして提供する機能です。分析対象はAWRのデータをもとに分析しています。また、高度なOracle技術者にとってもまずADDMレポートを参照することで、状況の概要を把握することができるため、分析にかかわる作業効率を上げることができます(図1-16)。

図1-16 ADDMのコンセプト
図1-16 ADDMのコンセプト(拡大画像を表示)

 前述のようにADDMによって出力される分析レポートは、作業効率を上げることができます。しかし、実際のプロジェクトで利用する場合、ADDMのレポートの指示をそのまま実施するのではなく、開発環境でその効果を検証した上で本番環境に適用することが理想的です(図1-17)。いずれにしても、ADDMを有効活用することで分析時間やコストを削減することができるでしょう。

図1-17 ADDMとAWRの有効利用
図1-17 ADDMとAWRの有効利用(拡大画像を表示)

■ レポート作成
ADDMはアドバイスすべき事象が発生したと判断すると、自動的にOracle EMの画面上に表示されます。データベースのホームページの下のほうにパフォーマンス分析としてグラフが表示されます(図1-18)。影響の大きい事象順に表示されるため、一番上の事象から内容確認するとよいでしょう。

図1-18 EM上のADDM自動表示
図1-18 EM上のADDM自動表示(拡大画像を表示)

結果をクリックすると、その詳細が表示されます。ここでは遅いSQLが見つかった例を表しています(図1-19)。どのようなSQLであったか、その影響度はどのぐらいか、次にとるべきアクションは何かなどの情報が表示されます。今回の例ではSQLチューニング・アドバイザを実行するように指示が出されています。なお、SQLチューニング・アドバイザはDiagnostics Packに加え、Tuning Packが必要となります。

図1-19 ADDMの分析結果
図1-19 ADDMの分析結果(拡大画像を表示)

参考までにSQLチューニング・アドバイザの結果をご紹介します(図1-20)。今回の例では遅いSQLに対してどのテーブルのどのカラムに索引を作成するとよいといった指示が出されています。また、索引を作成する前と後の実行計画の違いも事前に確認することが可能です。

図1-20 SQLチューニング・アドバイザ
図1-20 SQLチューニング・アドバイザ(拡大画像を表示)

自動で表示されるADDMは最近のパフォーマンス状況に関してアドバイスしているものです。過去のある時間帯の分析をしてほしい場合、AWRと同様に分析してほしい時間の期間を指定することでレポートが作成されます。ADDMレポートの作成方法は図1-7のアクションメニューを「ADDMの実行」とすることでも可能ですし、「セントラル・アドバイザ」→「ADDM」とリンクをたどってADDMを実行することでも可能です。過去の期間を分析したい場合には次のように設定してADDMを実行します(図1-21)。

図1-21 ADDMの実行
図1-21 ADDMの実行(拡大画像を表示)

実際に作成したレポートはこちらです。
ADDMにもレポートを作成するスクリプトが用意されています。addmrpt.sqlを実行することで作成できます(図1-22)。AWRレポートの作成と同じように、分析対象とする開始と終了のSnap Idと、出力ファイル名を入力します。

図1-22 ADDMレポート作成(スクリプト)その1
図1-22 ADDMレポート作成(スクリプト)その1(拡大画像を表示)

なお、addmrpt.sqlはSnap Idの一覧がデフォルト3日間となっています。一覧は過去3日間分ですが、Snap Idさえ正しければ3日よりも前のレポートも作成可能です。また、addmrpti.sqlを使用することでこの問題を回避できます(図1-23)。addmrpti.sqlはSnap Idの一覧を過去何日分にしたいかを指定することができます。ただし、DB IDとInstance番号(Oracle Real Application ClustersのInstance番号)も入力する必要があります。

図1-23 ADDMレポート作成(スクリプト)その2
図1-23 ADDMレポート作成(スクリプト)その2(拡大画像を表示)

4.3 Active Session History (ASH) 

 Statspackは図1-5で説明したように瞬間的な事象を捉えるような分析には不向きでした。この瞬間的な事象を分析したいというニーズをカバーするために、Oracle Database 10gでは新しい機能が追加されました。それがASHです。

 ASHは名前の通り、アクティブなセッションに関するログを取得しています。V$SESSION(V$SESSION_WAIT)の情報を1秒間隔で自動的に取得しており、V$ACTIVE_SESSION_HISTORYビューを参照することで最近のアクティブなセッションの情報を確認することができます。V$SESSIONはまさに今の状態しか見ることができないため、分析に利用するためにはV$SESSIONの検索結果をログファイルなどに出力させる必要がありましたが、ASHではその必要がなくなりました。ただし、V$ACTIVE_SESSION_HISTORYはSGA内のASHバッファ内に保存されているため、そのバッファが足りなくなれば古いデータから削除されていきます。長い期間のASHを保存したい場合は、SGAの領域を拡張するか、V$SESSIONと同様にファイルに出力しておく必要があります。しかし、ASHはAWRのリポジトリ内へMMONプロセスとMMNLプロセスによって自動的に保存されます。ただし、リポジトリの容量を小さくするために、V$ACTIVE_SESSION_HISTORYの情報をそのまま保存せずに、ある程度フィルタリングし、レコードを減らして保存しています。保存されたレコードはDBA_HIST_ACTIVE_SESS_HISTORYビューから参照可能です。このデータの保存期間もAWRの保存期間と同じです。つまり、デフォルトでは過去7日分が保存されます。分析したい対象期間がSGA内に残っているときにはV$ACTIVE_SESSION_HISTORYを参照し、残っていない場合はDBA_HIST_ACTIVE_SESS_HISTORYを参照するなどして使い分けましょう(図1-24)。

図1-24 ASH概要
図1-24 ASH概要(拡大画像を表示)

■ 基本的なASH使用方法
ASHはOracle EMの「平均アクティブ・セッション」という欄に表示されています(図1-25)。さらにリンクを進むと「トップ・アクティビティ」(図1-26)や「SQLの詳細」(図1-27)も参照することが可能です。

図1-25 平均アクティブ・セッション
図1-25 平均アクティブ・セッション(拡大画像を表示)
図1-26 トップ・アクティビティ
図1-26 トップ・アクティビティ(拡大画像を表示)
図1-27 SQLの詳細
図1-27 SQLの詳細(拡大画像を表示)

SQLの詳細では、SQLの統計やプランも参照できます。また、AWRやADDMと同様に、ASHレポートも作成可能です(図1-28)。参照データはDBA_HIST_ACTIVE_SESS_HISTORYよりもV$ACTIVE_SESSION_HISTORYを優先しているため、どちらにデータがあるかをレポート作成時に意識する必要はありません。なお、ASHレポートは10g R2から使用可能です。

図1-28 ASHレポート
図1-28 ASHレポート(拡大画像を表示)

実際に作成したレポートはこちらです。ASHレポートの作成もスクリプト(ashrpt.sql)が用意されています(図1-29)。

図1-29 ASHレポート作成(スクリプト)
図1-29 ASHレポート作成(スクリプト)(拡大画像を表示)

■ ASH使用の応用
より詳細な分析をしたい方にとっては、分析の視点を自分なりにカスタマイズしたいこともあるでしょう。たとえば、先ほど紹介したOracle EMに表示されるアクティブ・イベントのグラフは、イベントのカテゴリ別(具体的にはV$EVENT_NAME.WAIT_CLASS)の集計となっており、同一カテゴリ内の違うイベント単位の違いをこのグラフから把握することはできません。そこで、V$ACTIVE_SESSION_HISTORYやDBA_HIST_ACTIVE_SESS_HISTORYを直接参照します。まずは以下のSQL例のようにデータをCSV形式に出力します。

select '"' || SAMPLE_TIME || '","' || EVENT || '"' from DBA_HIST_ACTIVE_SESS_HISTORY
where SAMPLE_TIME
between to_date('2006-07-18 14:00:00','YYYY-MM-DD HH24:MI:SS') -- 開始時間
and to_date('2006-07-18 15:00:00','YYYY-MM-DD HH24:MI:SS') -- 終了時間
and EVENT is not null
order by SAMPLE_TIME;

出力した結果を表計算ソフトでグラフにします。StatspackやAWRでは分析できなかった、イベント発生の時間推移との関係や、同時に実行されているトランザクションとの因果関係を推測することができるため、分析に役立てることができます(図1-30)。

図1-30 ASHからグラフを作成
図1-30 ASHからグラフを作成(拡大画像を表示)

ここでは参照しているカラムをSAMPLE_TIMEとEVENT列の2つのみとしましたが、よりきめ細かい分析をしたい場合は、その他のカラムも含めたグラフを作るとよいでしょう。

4.4 Automatic Workload Repository(AWR)ビュー 

これまで紹介したStatspackやAWR、ADDMのレポートはOracleによって決められた定型フォーマットのレポートです。しかし、高度な技術者にとっては、その定型フォーマットとは違う観点で分析したいこともあるでしょう。Diagnostics Packではそのような要望にこたえるために様々なビューが用意されています。そのビューがDBA_HIST_の文字列で始まる名前のAWRビューです。10g R2では78種類ほど用意されています。既にお気づきの方もいるかと思いますが、先ほど説明したDBA_HIST_ACTIVE_SESS_HISTORYはそのビューの中の1つです。V$ACTIVE_SESSION_HISTORYの過去のデータを参照できるのがDBA_HIST_ACTIVE_SESS_HISTORYビューです。これらのビューは様々な動的パフォーマンスビューの過去ログとして利用することができます。

その他のビューでは、次のようなビューがお薦めです(*は任意の文字列を表します)。
DBA_HIST_SQL* … V$SQL、V$SQL_PLANなどの履歴
DBA_HIST_*_STAT … V$SYSSTAT、V$SGASTATなどの履歴

 ここでDBA_HIST_SQL_PLANの使用例をあげてみます。SQL_ID(今回の例ではb5vcffd0br8mj)を指定して実行計画を表示しているSQL例です。

SQL> SELECT id,
  2   operation,
  3   options,
  4   object_owner,
  5   object_name,
  6   parent_id,
  7   depth
  8   FROM dba_hist_sql_plan
  9   WHERE sql_id = 'b5vcffd0br8mj'
 10   ORDER BY dbid,
 11   sql_id,
 12   plan_hash_value,
 13   id;
ID
---
OPERATION
--------------------------
OPTIONS
---------------------
OBJECT
------------
OBJECT_N
----------------
PARENT_ID
-----------------
DEPTH
----------
0 SELECT STATEMENT         0
1 NESTED LOOPS       0 1
2 TABLE ACCESS BY INDEX ROWID SCOTT DEPT 1 2
3 INDEX UNIQUE SCAN SCOTT PK_DEPT 2 3
4 TABLE ACCESS FULL SCOTT EMP 1 2

 実行計画そのものはawrsqrpt.sqlで作成したレポートを参照することで確認することができます。しかし、10g R1ではawrsqrpt.sqlは存在しないため、実行計画を表示することができません。そこで今回の例のようにDBA_HIST_SQL_PLANが役に立つわけです。

(参考)より実行計画を見やすくするために
先ほどの実行結果はやや見にくいので、編集したものが以下の例です。各行のOPERATION列にDEPTHの数ぶんのスペース文字を左側に入れてみてください。皆さんも見慣れた実行計画の表示となるはずです。
ID
---
OPERATION
-----------------------
OPTIONS
-----------------
OBJECT
-------------
OBJECT_N
-----------------
PARENT_ID
------------------
DEPTH
-----------
0 SELECT STATEMENT         0
1   NESTED LOOPS       0 1
2     TABLE ACCESS BY INDEX ROWID SCOTT DEPT 1 2
3       INDEX UNIQUE SCAN SCOTT PK_DEPT 2 3
4     TABLE ACCESS FULL SCOTT EMP 1 2

 また、以下のSQL例のように、EMP表をFULL SCANしているSQLのSQL_IDを検索するなどの使い方も可能です。

SELECT DISTINCT sql_id FROM dba_hist_sql_plan
WHERE object_name = 'EMP'
AND operation = 'TABLE ACCESS'
AND options = 'FULL';
(結果は省略)

 ここで紹介した以外のビューも皆さんで独自の分析方法に利用してみてください。自分だけのカスタマイズ分析手法を考えてみるのも面白いでしょう。

インデックスに戻る

5 まとめ 

 今回、StatspackとDiagnostics Packを紹介しました。ここでDiagnostics Packがある場合と、ない場合の特徴の差をまとめます(表1-2)。

  Diagnostics Packなし Diagnostics Packあり
評価 補足 評価 補足
準備 インストール必要 インストール不要
使いやすさ コマンドラインのみによる操作
テキスト形式のレポートのみ
GUIまたはコマンドラインによる操作
HTMLまたはテキスト形式のレポート
自動分析 × 自動分析機能なし ADDMによるある程度の自動分析
高度な分析は有識者が必要
セッション分析 V$SESSION(V$SESSION_WAIT)を分析することで可能
V$SESSIONを定期的に取得、保存する仕組みを作成する必要あり
ASHを分析することで可能
ASHによって自動取得
サンプル間隔の変更は原則としてできない
性能情報の種類 少ない 多い
ライセンス Enterprise Edition/Standard Editionともに可能 Enterprise EditionかつDiagnostics Packが必要

 Diagnostics Packがオプションライセンスであることや、Enterprise Editionが必須であることなどの制約はあるにせよ、基本的にはDiagnostics Packがあったほうがデータベース管理者にとってはいいことばかりです。ただし、Standard Editionの場合など、Statspackによる運用が必須となる環境もあるため、Statspackも重要な機能であることに変わりはありません。いずれにせよ、どちらもとても便利な機能です。「知らないから使ったことがない」というような、宝の持ち腐れとならないようにしましょう。

 今回はそれぞれの使用方法を中心に説明しましたが、実際の運用でいかにうまく使いこなせるかが重要となります。次回からはあるシナリオに照らし合わせ、どのような流れで使っていくものなのかを説明したいと思います。

インデックスに戻る