Note: Active session history is snapped once each second in
gv_$active_session_history, held for approximately 30 minutes and then stored in dba_hist_active_sess_history. It is designed
for an hour of online storage, based on 2MB per CPU, but may fill and flush sooner. when written to disk it is further
sampled (1 out of 10)
Thank you, Job Miller at Oracle, for the additional information found at this site:
Click Here
|
| Dependent Objects |
| dba_hist_active_sess_history |
wrh$_active_session_history |
| gv_$active_session_history |
wrm$_snapshot |
|
| ASH Buffers |
SELECT *
FROM gv$sgastat
WHERE name = 'ASH buffers';
|
| Most Active SQL in the previous hour |
desc
gv$active_session_history
SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'BACKGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
|
| Most Active I/O
|
SELECT
DISTINCT wait_class
FROM gv$event_name
ORDER BY 1;
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
set linesize 121
SELECT * FROM TABLE(dbms_xplan.display_cursor('gpv3kb4n2f2q1'));
|
| |
| ASH Demo |
| Demo preparation as the active user |
conn uwclass/uwclass
SELECT t.tablespace_name
FROM all_tables t, all_indexes i
WHERE t.tablespace_name = i.tablespace_name;
-- Note: do not close session during the balance of
this demo |
| Demo preparation as the DBA |
conn / as sysdba
SELECT sid, serial#
FROM gv$session
WHERE username = 'UWCLASS'; |
| To find out the wait events for which this session
|
SELECT
sample_time, event, wait_time
FROM gv$active_session_history
WHERE session_id = 147
AND session_serial# = 1715; |
| To find recent sample
times
|
SELECT
sample_time
from gv$active_session_history
WHERE session_id = 147
AND sample_time > SYSDATE-10/1440
ORDER BY 1; |
| Find SQL statement identified above |
SELECT sql_text, application_wait_time
FROM gv$sql
WHERE sql_id IN (
SELECT sql_id
FROM gv$active_session_history
WHERE TO_CHAR(sample_time) = '04-DEC-07 08.36.09.094 AM'
AND session_id = 147
AND session_serial# = 1715); |
