Oracle Active Session History - ASH
Version 20c

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

Related Topics
Built-in Functions
Built-in Packages
Automated Workload Repository (AWR) Report
DBMS_WORKLOAD_REPOSITORY
DBMS_XPLAN
ORADEBUG
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx