|
Oracle 10g AWR (Automatic Workload Repository) -Automatic SQL Tuning Feature-
|
Hello, everyone. This time, we would like to confirm the features of v$active_session_history. This dynamic performance view is based on the table x$ash, so it is called ASH.
<Confirm the table that v$active_session_history is based on>
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
?Ehere)
WHERE s.sample_addr = a.sample_addr
and s.sample_id = a.sample_id
and s.sample_time = a.sample_time
|
The followings are the characteristics of ASH.
(A). It stores the history of active sessions in v$session every second.
(B). The sampling interval can be changed by the hidden parameter _ash_sampling_interval.
(C). 1/10 of the old data will be written into dba_hist_active_sess_history (wrh$_active_session_history).
(D). The value in TIME_WAITED row is updated whenever wait event get finished.
This time let's focus on (D). The data of active sessions are inserted every second; moreover, the records inserted will also be updated.
We would like to create lock watis to show the record of v$active_session_history is inserted and the record is updated.
OS :Linux 2.4.9-e.40smp
Oracle:Release 10.1.0.2.0
<Create lock waits>
SESSION A
SQL> delete from emp where empno = 7369;
SESSION B
SQL> delete from emp where empno = 7369;
<Data in 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
--------------------- ------------- ------- -----------
E
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 of the latest record is zero.)
|
By confirming the row at the bottom (04-08-30 23:08:39.446), the value in TIME_WAITED column is 0. Let's check v$active_session_history again.
<Data in v$active_session_history>
SAMPLE_TIME SQL_ID STATE TIME_WAITED
--------------------- ------------- ------- -----------
E
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
(Updated to 498202)
|
The second row from the bottom has a TIME_WAITED value updated to 498202.
The manual of Oracle 10g Version 1 says as follows:
When the stanby event continues over one second and turns into stanby
in multiple session sampling rows, the time wasted on the standby of
the standby event will be moved into the end of session sampling rows.
Such information is not displayed in the latest session sampling row.
|
When it continues over one second, the information will be displayed not in the latest row but in the last row. The following is the result in another environment. Out of the three waits, only the last row is updated.
OS :HP-UX hp11i B.11.11
Oracle:Release 10.1.0.2.0
<The first time>
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
%lt;The second time>
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
|
Next time we would like to start a new topic, "Oracle10g Cost Base Optimizer." Please look forward to it!
Yoshihiro Uratsuji
|
|
