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