| General Information |
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 |
gv$ash_info |
| dba_hist_ash_snapshot |
wrh$_active_session_history |
| gv$active_session_history |
wrm$_snapshot |
|
| ASH Buffers |
SELECT *
FROM gv$sgastat gvs
WHERE gvs.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 ash
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_type = 'BACKGROUND'
GROUP BY ash.sql_id
ORDER BY COUNT(*) DESC;
SELECT ash.sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history ash
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_type = 'FOREGROUND'
GROUP BY ash.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 at.tablespace_name
FROM all_tables at, all_indexes ai
WHERE at.tablespace_name = ai.tablespace_name;
-- Note: do not close session during the balance of this demo |
| Demo preparation as the DBA |
conn / as sysdba
SELECT gs.sid, gs.serial#
FROM gv$session gs
WHERE gs.username = 'UWCLASS'; |
| To find out the wait events for which this session |
SELECT ash.sample_time, ash.event, ash.wait_time
FROM gv$active_session_history ash
WHERE ash.session_id = 147
AND ash.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 gs.sql_text, gs.application_wait_time
FROM gv$sql gs
WHERE gs.sql_id IN (
SELECT ash.sql_id
FROM gv$active_session_history ash
WHERE TO_CHAR(ash.sample_time) = '04-DEC-07 08.36.09.094 AM'
AND ash.session_id = 147
AND ash.session_serial# = 1715); |
Sample ASH Report generated with DBMS_WORKLOAD_REPOSITORY
ASH_REPORT_HTML |
Click Here |
