Oracle10g introduces the
v$active_session_history
view that keeps a history for recent active sessions’ activity.
Oracle takes snapshots of active database sessions every second
without placing serious overhead on the system. A database session
is considered active by Oracle when it is consuming CPU time or
waiting for an event that does not belong to the idle wait class.
This view contains a considerable amount of information that is
available in the
v$session view, but it also has the
sample_time column that points to a time in the past when a session was doing
some work or waiting for a resource.
v$active_session_history view contains a single row
for each session when sampling was performed.
An interesting possibility becomes available with the introduction
of the
v$active_session_history
view in Oracle10g. With this tool,
Oracle DBAs are now able to trace sessions without the need to use
the well known 10046 event to perform extended tracing.
All tracing can be performed now using only SQL queries without the
need to review raw trace files and format them using the TKPROF
utility.
Oracle keeps session history in the circular memory buffer in the
SGA. This means that the greater the database activity is,
the smaller the amount of time session history available in the ASH
view is. In this instance, it might help that the AWR
dba_hist_active_sess_history view stores the ASH history for a longer time; however, the
dba_hist_active_sess_history view stores ASH data
snapshots only for the times the AWR snapshots were taken.
How can the information available through the
v$active_session_history view be used?. If a session that is experiencing delays or hangs
has been identified and the goal is to identify the SQL statement(s)
the session is issuing, along with the wait events being experienced
for a particular time period, a query similar to this one can be
issued:
SELECT C.SQL_TEXT,
B.NAME,
COUNT(*),
SUM(TIME_WAITED)
FROM v$ACTIVE_SESSION_HISTORY A,
v$EVENT_NAME B,
v$SQLAREA C
WHERE A.SAMPLE_TIME BETWEEN '10-JUL-04 09:57:00 PM' AND
'10-JUL-04 09:59:00 PM' AND
A.EVENT# = B.EVENT# AND
A.SESSION_ID= 123 AND
A.SQL_ID = C.SQL_ID
GROUP BY C.SQL_TEXT, B.NAME
The
current_obj# column
can be joined with the
dba_objects view to get name of the object, or it can be joined with the current_file# column using dba_data_files to
see the name of datafile that was accessed. Even a particular block
that caused a wait event can be identified using the
current_block# column.
It is also possible to identify hot datafiles, objects, or even data
blocks that are being accessed by sessions more frequently than
others and thus could be candidates for additional investigations.
The
hot_files_ash.sql
query shows hot datafiles that caused the most wait times during
session access:
SELECT
f.file_name “Data File”,
COUNT(*) “Wait Number”,
SUM(h.time_waited) “Total Time Waited”
FROM
v$active_session_history h,
dba_data_files f
WHERE
h.current_file# = f.file_id
GROUP BY f.file_name
ORDER BY 3 DESC
The sample output looks like:
Data File Wait Number Total Time Waited
-------------------------------------- ----------- -----------------
D:\ORACLE\ORADATA\DBDABR\SYSAUX01.DBF 5514 994398837
D:\ORACLE\ORADATA\DBDABR\SYSTEM01.DBF 2579 930483678
D:\ORACLE\ORADATA\DBDABR\UNDOTBS01.DBF 245 7727218
D:\ORACLE\ORADATA\DBDABR\USERS01.DBF 141 1548274
To be fair to the 10046 trace, the
v$active_session_history does not catch session activity that is extremely fast, but it
should catch activity that causes the most waits and resource
consumption and will, therefore, be useful to the DBA.
Statistically, the
v$active_session_history
does catch extremely fast operations if they occur sufficiently
often to contribute to user time.
The following text includes several helpful queries that run against
the
v$active_session_history
view. The first query,
events_waits_hr_ask.sql, reports a list of resources
that were in high demand in the last hour. This query does not
reflect Idle wait events.
SELECT
h.event "Wait Event",
SUM(h.wait_time + h.time_waited) "Total Wait
Time"
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time BETWEEN sysdate - 1/24 AND sysdate
AND h.event_id = e.event_id
AND e.wait_class <> 'Idle'
GROUP BY h.event
ORDER BY 2 DESC
The output looks like the following:
Wait Event Total Wait Time
------------------------------- ---------------
Queue Monitor Task Wait 10,256,950
class slave wait 10,242,904
log file switch completion 5,142,555
control file parallel write 4,813,121
db file sequential read 334,871
process startup 232,137
log file sync 203,087
latch free 36,934
log buffer space 25,090
latch: redo allocation 22,444
db file parallel write 714
db file scattered read 470
log file parallel write 182
direct path read temp 169
control file sequential read 160
direct path write temp 112
SEE CODE DEPOT FOR FULL SCRIPTS
