Oracle10g AWRに関する検証 その6

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

今回はちょっと一息ついて、v$active_session_history の特徴に関して再確
認させて頂きたいと思います。ちなみにこの動的パフォーマンスビューの元
表がx$ashであることからアッシュと呼ばれています。

□v$active_session_historyの元表を確認

select *
from v$fixed_view_definition
where view_name ='GV$ACTIVE_SESSION_HISTORY'

VIEW_NAME
------------------------------
VIEW_DEFINITION
----------------------------------------------------------------------
GV$ACTIVE_SESSION_HISTORY
SELECT  /*+ no_merge ordered use_nl(s,a) */ 
a.inst_id, s.sample_id, s.sample_time, a.session_id
, a.session_serial#, a.user_id, a.sql_id, a.sql_child_number
, a.sql_plan_hash_value, a.sql_opcode, a.service_hash
, decode(a.session_type, 1,'FOREGROUND', 2,'BACKGROUND', 'UNKNOWN')
, decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.qc_session_id
, a.qc_instance_id, a.event, a.event_id, a.event#, a.seq#, a.p1
, a.p2, a.p3, a.wait_time, a.time_waited, a.current_obj#
, a.current_file#, a.current_block#, a.program, a.module, a.action
, a.client_id 
FROM  x$kewash s, x$ash a 
                    ↑【これこれ】
WHERE s.sample_addr = a.sample_addr
  and s.sample_id   = a.sample_id
  and s.sample_time = a.sample_time

アッシュの特徴として以下の点が挙げられます。

A. 1秒間隔でv$session のアクティブなセッションに関する履歴を保持
B. サンプリング間隔は隠しパラメータ_ash_sampling_intervalにて変更可能
C. 古くなった情報は全体のデータ量の1/10がdba_hist_active_sess_history
   (wrh$_active_session_history)に書き込まれる
D. TIME_WAITED列の値は待ちイベントが終わった段階で更新される

今回注目して頂きたいのはD. の項目です。当然、1秒おきにアクティブなセ
ッションの情報がINSERTされますが、実はINSERTしたレコードの更新も行な
われています。

以下はロック待ちを発生させて、v$active_session_historyのレコードがINS
ERTされたレコードが更新される様子です。

*********************************************************************
OS :Linux 2.4.9-e.40smp
Oracle:Release 10.1.0.2.0

□ロック待ちを発生させる
SESSION A

SQL> delete from emp where empno = 7369;

SESSION B

SQL> delete from emp where empno = 7369;

□v$active_session_historyの情報

SELECT
sample_time
, sql_id
, DECODE(wait_time, 0, 'WAITING', 'ON CPU') STATE
, time_waited
FROM v$active_session_history
WHERE
event = 'enq: TX - row lock contention'
and sample_time > sysdate - 1/24/60
order by sample_time


SAMPLE_TIME           SQL_ID        STATE   TIME_WAITED
--------------------- ------------- ------- -----------
・
04-08-30 23:08:37.406 1v0rfd8wb476a WAITING      498058
04-08-30 23:08:38.426 1v0rfd8wb476a WAITING      498019
04-08-30 23:08:39.446 1v0rfd8wb476a WAITING           0
                    最新のレコードのTIME_WAITEDはゼロ ↑

一番下の行(04-08-30 23:08:39.446)を確認するとTIME_WAITED列の値が0であ
ることが分かります。ではも一度、v$active_session_historyを検索してみ
ます。

□v$active_session_historyの情報

SAMPLE_TIME           SQL_ID        STATE   TIME_WAITED
--------------------- ------------- ------- -----------
・
04-08-30 23:08:38.426 1v0rfd8wb476a WAITING      498019
04-08-30 23:08:39.446 1v0rfd8wb476a WAITING      498202←--
04-08-30 23:08:40.466 1v0rfd8wb476a WAITING           0     |
                                            498202に更新!!|

下から2行目のTIME_WAITED列の値が498202に更新されているのが確認出来ます。

Oracleのマニュアル(10gリリース1)には以下の様に記載されています。

*********************************************************************
待機イベントが1 秒を超えて継続し、複数のセッション・サンプル行で待機
となった場合、その待機イベントの待機に実際に費やされた時間がそれらの
セッション・サンプル行の最後に移入される。この情報は、最新のセッショ
ン・サンプルでは表示されない。
*********************************************************************

1秒を継続した場合は、最新の行では表示されず最後の行に表示されるようで
ある。以下は別の環境で実施した結果ですが3行の待ちでは最終行だけが更新
されています。

*********************************************************************
OS :HP-UX hp11i B.11.11
Oracle:Release 10.1.0.2.0

□1回目

SAMPLE_TIME           SQL_ID         STATE    TIME_WAITED
--------------------- -------------- -------- -----------
04-08-23 18:53:04.191 4sgjnp0bvjb3p  WAITING            0
04-08-23 18:53:05.221 4sgjnp0bvjb3p  WAITING            0
04-08-23 18:53:06.251 4sgjnp0bvjb3p  WAITING            0

□2回目

SAMPLE_TIME           SQL_ID         STATE    TIME_WAITED
--------------------- -------------- -------- -----------
04-08-23 18:53:04.191 4sgjnp0bvjb3p  WAITING            0
04-08-23 18:53:05.221 4sgjnp0bvjb3p  WAITING            0
04-08-23 18:53:06.251 4sgjnp0bvjb3p  WAITING      3007734

最後の行というのが味噌のようです。

以上、秋刀魚が美味い(北海道産)!茅ヶ崎にて