Oracle Active Session History - ASH
Version 12.1.0.1

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
CDB_HIST_ACTIVE_SESS_HISTORY DBMS_ASH_INTERNAL WRI$_REPT_ASH
CDB_HIST_ASH_SNAPSHOT GV$ACTIVE_SESSION_HISTORY WRM$_SNAPSHOT
DBA_HIST_ACTIVE_SESS_HISTORY GV$ASH_INFO WRR$_ASH_TIME_PERIOD
DBA_HIST_ASH_SNAPSHOT WRH$_ACTIVE_SESSION_HISTORY  
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, v$event_name evt
WHERE ash.sample_time > SYSDATE - 3/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'System I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

set linesize 121

-- modify the above query, if necessary, until the condition yields a SQL_ID
SELECT * FROM TABLE(dbms_xplan.display_cursor('424h0nf7bhqzd'));
 
ASH Demo
Demo preparation as the active user conn uwclass/uwclass@pdborcl

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. Open a new SQL*Plus window to continue
Demo preparation as the DBA conn / as sysdba

SELECT gs.sid, gs.serial#
FROM gv$session gs
WHERE gs.username = 'UWCLASS';
Find Wait Events for the Current Session SELECT sid, serial#
FROM v$session
WHERE sid IN (SELECT sid FROM v$mystat WHERE rownum = 1);

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;
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 the 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) = '01-JUL-13 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

Related Topics
Automated Workload Repository (AWR) Report
DBMS_WORKLOAD_REPOSITORY
DBMS_XPLAN
ORADEBUG

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved