Oracle10g AWRに関する検証 その１
＜Oracle10g AWR(Automatic Workload Repository)に関する検証 その１＞
～自動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日単位でパーティション化しているようです。
システムに負荷を与えないようにログを管理という観点で見ればグッドですね。
以上、優勝おめでとう！グリーンペペ！ベロ！茅ヶ崎にて