Oracle | ASH: Active Session History

Oracle Active Session History - ASH Version 20c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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).

Dependent Objects

CDB_HIST_ACTIVE_SESS_HISTORY	DBMS_ASH_INTERNAL	WRH$_ACTIVE_SESSION_HISTORY_BL
CDB_HIST_ASH_SNAPSHOT	GV$ACTIVE_SESSION_HISTORY	WRI$_REPT_ASH
DBA_HIST_ACTIVE_SESS_HISTORY	GV$ASH_INFO	WRM$_SNAPSHOT
DBA_HIST_ASH_SNAPSHOT	WRH$_ACTIVE_SESSION_HISTORY	WRR$_ASH_TIME_PERIOD

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; SQL_ID COUNT(*) PCTLOAD ------------- ---------- ---------- 362 .98 1jbhwmbh7bp2n 3 .01 7vukkk6k166q2 1 0 3dbzmtf9ahvzt 1 0 7qudmah047src 1 0 22356bkgsdcnh 1 0 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; SQL_ID COUNT(*) PCTLOAD ------------- ---------- ---------- 2 1

Most Active I/O SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1; WAIT_CLASS ---------------- Administrative Application Cluster Commit Concurrency Configuration Idle Network Other Queueing Scheduler System I/O User I/O 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; SQL_ID COUNT(*) ------------- --------- 4 7qudmah047src 1 set linesize 121 -- modify the above query, if needed, until the condition yields a SQL_ID SELECT * FROM TABLE(dbms_xplan.display_cursor('7qudmah047src'));

ASH Demo

Demo preparation as the active user conn uwclass/uwclass@pdbdev SELECT UNIQUE at.tablespace_name FROM all_tables at, all_indexes ai WHERE at.tablespace_name = ai.tablespace_name; TABLESPACE_NAME ---------------- SYSTEM SYSAUX UWDATA -- 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 sys@pdbdev 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); SID SERIAL# ----- ---------- 150 54624 SELECT ash.sample_time, ash.event, ash.wait_time FROM gv$active_session_history ash WHERE ash.session_id = 150 AND ash.session_serial# = 54624;

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; SAMPLE_TIME -------------------------- 19-MAR-20 07.43.59.808 PM

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) = '19-MAR-20 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