Oracle10g AWRに関する検証 その1

<Oracle10g AWR(Automatic Workload Repository)に関する検証 その1>
~自動SQLチューニング機能編~
ペンネーム:ちょびひげ

今回からOracle10g の自動管理シリーズのSQLチューニング機能に関連して
AWR(Automatic Workload Repository)仕組みに関しての検証をおこなってい
きます。

■SQLチューニングのやり方
読者の皆さんは日頃どのようにしてSQLをチューニングしているでしょうか?

エンドユーザから「レスポンスが遅い!」という苦情が来た場合は、初めに
レスポンスが遅い原因であるSQLの調査を行い(負荷の高いSQL文の抽出)、
そのSQL文のアクセスパスを確認してからSQLの書き換えやインデックスの作
成等(SQL文チューニング)を行うケースが多いのではないでしょうか。

以下のステップを踏みます。

(1)負荷の高いSQL文の抽出
(2)SQL文のチューニング

上記の作業を自動的に行える機能が10gで備わっています。ただし、「自動」
とは言っても何もしなくても問題が改善されるわけでは有りません。

さて、この2つの作業で難しい(時間がかかる)のは、負荷の高いSQLの抽出
ではないでしょうか。アプリケーションをよく理解しているデータベース管
理者であれば、直ぐにどのSQL文がレスポンスが低下の原因となっているのか
見当が付くかも知れません。しかし、多くのデータベース管理者は、エンド
ユーザにもう一度、遅い処理を実行してもらって、そのタイミングでOracle
のディクショナリ等を参照して負荷の高いSQLの特定を行います。つまり再現
性がなければなりません。

■いざという時のためにログ情報を取得
しかし、ユーザから苦情が来てからアクションを起こしていては遅すぎます!
そこで、経験を積んだデータベース管理者であれば日頃から負荷の高いSQLが
実行されていないかどうかのチェックを行います。スクリプトを組んだりツー
ルなんかを使って負荷の高いSQLが存在した場合はログに残したりします。
こうすることで以下のメリットがあります。

・ユーザからの苦情が来る前に対応出来る。
・過去のレスポンスダウンに対して、その時点に遡って分析できる。

プロアクティブに対応して、最悪なケースでも過去に遡って調査が可能です。
SQL文に限らず、いろいろなログ情報を残しておく事は、データベース管理者
として必須ではないでしょうか。

■10gでのログ情報
このログ情報の取得が10g からデフォルトで行なわれるようになっています。
今回、検証するAWR(Automatic Workload Repository)です。負荷の高いSQL
の情報をするには以下の表が使えそうなので、この先詳しく見ていく予定で
す。

・v$active_session_history
・dba_hist_active_sess_history

■AWR(Automatic Workload Repository)とは
これは、10gからの新しいプロセスmmonが様々な統計情報をメモリ上から取得
してログ情報として貯めておく機能です。
ポイントは以下の2つの情報をもつ点です。

(1)メモリ上にログ情報を保持
(2)ディスク上にログ情報を保持(テーブルに書き込まれた情報)

(1)の主な表として、v$active_session_historyやv$segment_statistics 等が
存在します。これらのv$表の元は、x$表です。当メールマガジンでもおなじ
みのx$bh表と同様にオン・メモリの情報です。

(2)の情報はSYSAUX表領域に書き込まれ、主にDBA_HIST_[???????]表に保持さ
れます。厳密には元表である、WR[?]_$[???????]表に保持されています。
なぜディスクに書かれるのかと言えば、メモリ上に保持しきれないからです。

v$active_session_historyのディスク上の情報は、
dba_hist_active_sess_historyです。

この、dba_hist_active_sess_historyの元表をみてみると、以下の2つのテー
ブルであることが分かります。

・WRM$_SNAPSHOT
・WRH$_ACTIVE_SESSION_HISTORY

□DBA_HIST_ACTIVE_SESS_HISTORY(VIEW)の元表を確認

SQL> select text from dba_views
  2* where view_name = 'DBA_HIST_ACTIVE_SESS_HISTORY';

select ash.snap_id, ash.dbid, ash.instance_number,
   [省略]
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash
where      ash.snap_id          = sn.snap_id
      and  ash.dbid             = sn.dbid
      and  ash.instance_number  = sn.instance_number
   [省略]
union all
select ash.snap_id, ash.dbid, ash.instance_number,
   [省略]
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY_BL ash
where      ash.snap_id          = sn.snap_id
      and  ash.dbid             = sn.dbid
      and  ash.instance_number  = sn.instance_number
   [省略]

ちなみにこの情報はSYSTEM表領域に入っているのでしょうか?
WRH$_ACTIVE_SESSION_HISTORYが入っている表領域を調べてみましょう。

□DBA_HIST_ACTIVE_SESS_HISTORYのセグメント情報を検索

SQL> select tablespace_name, segment_name, partition_name
  2  from dba_segments
  3* where segment_name = 'WRH$_ACTIVE_SESSION_HISTORY'

TABLESPACE_NAME  SEGMENT_NAME                 PARTITION_NAME
---------------- ---------------------------- ------------------------------
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1536
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1560
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1584
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1608
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1632
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1680
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1704
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_511456179_1728
SYSAUX           WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_SES_MXDB_MXSN

表領域はSYSAUX表領域です。AUXはAuxiliary(補助)の略なので、SYSTEMの補
助表領域に格納されています。

ちなみに、なるほど!と思った方もいると思いますが、実はこれらの表はパ
ーティション(PARTITION_NAME)で管理されています。

アプリケーションでメンテナンス性を考慮してパーティション化するのと同
様に、古いデータはパーティションごと削除してしまおう、ということだと
思います。ディスク上のログ情報の保持期限は日単位で設定できます。そう
すると、これらのパーティションも日単位で管理されているのではないでし
ょうか。

ついでにちょっと見てみましょう。

□各パーティションのキー(HIGH_VALUE)を検索

SQL>  select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from dba_tab_PARTITIONS
  2  where table_name = 'WRH$_ACTIVE_SESSION_HISTORY'

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ---------------------
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1536     511456179, 1560
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1560     511456179, 1584
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1584     511456179, 1608
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1608     511456179, 1632
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1632     511456179, 1680
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1680     511456179, 1704
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1704     511456179, 1728
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_511456179_1728     511456179, MAXVALUE
WRH$_ACTIVE_SESSION_HISTORY    WRH$_ACTIVE_SES_MXDB_MXSN      MAXVALUE, MAXVALUE

□上記で調べたキーを条件にサンプリング時間(SAMPLE_TIME)を検索

SQL>  select snap_id ,max(SAMPLE_TIME)
  2  from WRH$_ACTIVE_SESSION_HISTORY
  3  where dbid=511456179
  4  and snap_id in (1560,1584,1608,1632,1680,1704,1728)
  5  group by snap_id

   SNAP_ID MAX(SAMPLE_TIME)
---------- ------------------------------
      1560 04-08-19 02:00:25.013
      1584 04-08-20 02:00:36.383
      1608 04-08-21 02:00:52.894
      1632 04-08-22 02:00:17.953
      1680 04-08-24 02:00:37.378
      1704 04-08-25 02:00:39.370
      1728 04-08-26 02:00:48.546

1日単位でパーティション化しているようです。
システムに負荷を与えないようにログを管理という観点で見ればグッドですね。

以上、優勝おめでとう!グリーンペペ!ベロ!茅ヶ崎にて