10 Useful queries Active Session History(Oracle ASH)

What is Active Session History(ASH)

Active Session History (ASH) was introduced in Oracle 10g. It samples the activity of each active database session every second. The data is held in a buffer in memory in the database.The design goal is to keep about an hour (your mileage will vary). If a session is not active it will not be sampled. The in-memory buffer is exposed via a view called v$active_session_history.
When an AWR snapshot is taken, 1 row in 10 from the ASH buffer is copied down into the AWR repository. It can also be flushed to disk between snapshots when the buffer reaches 66% full, so there is no missed data.The data is stored in WRH$_ACTIVE_SESSION_HISTORY and it is exposed via dba_hist_active_sess_history.it  is enabled by default, but before you rush off to use it, be aware that it is a licence feature.
You can disable ash by setting the oracle underscore parameter

alter system set "_ash_enable"=False;

I want to emphasize that if the session is not active it will not be sampled. You can actually set a parameter _ash_enable_all = TRUE to force all sessions, including idle sessions, to be sampled

10 Useful queries Active Session History(Oracle ASH)

(1)Oracle has provided a package called DBMS_APPLICATION_INFO  This allows you to set two attributes; MODULE and ACTION for a session. That value then appears in v$session, and can be very useful to help you identify what database sessions relate to what part of an application. These values are then also captured by ASH.

(2) Most active session in last one hour can be found using active session history

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'BACKGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

(3) To find out the oracle wait events for which this session

SELECT sample_time, event, wait_time
FROM gv$active_session_history
WHERE session_id = &1
AND session_serial# = &2

(4) Most active session in last one hour

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'BACKGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1/24
AND session_type = 'FOREGROUND'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

(5) Most I/O intensive sql in last 1 hour

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;

(6) Locking information in Oracle in last 1 min

col event for a22
col block_type for a18
col objn for a18
col otype for a10
col fn for 99
col sid for 9999
col bsid for 9999
col lm for 99
col p3 for 99999
col blockn for 99999
select
to_char(sample_time,'HH:MI') st,
substr(event,0,20) event,
a.session_id sid,
mod(a.p1,16)  lm,
a.p2,
a.p3,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,
CURRENT_FILE# fn,
CURRENT_BLOCK# blockn,
a.SQL_ID,
BLOCKING_SESSION bsid
from v$active_session_history a,
all_objects o
where event like 'enq: TX%'
and o.object_id (+)= a.CURRENT_OBJ#
and sample_time > sysdate - 40/(60*24)
Order by sample_time
/

(7) top sqls spent more on cpu/wait/io

select
ash.SQL_ID ,
sum(decode(a.session_state,'ON CPU',1,0)) "CPU",
sum(decode(a.session_state,'WAITING',1,0)) -
sum(decode(a.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(a.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(a.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history a,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#

(8)  A particular session sql analysis

SELECT C.SQL_TEXT,
B.NAME,
COUNT(*),
SUM(TIME_WAITED)
FROM v$ACTIVE_SESSION_HISTORY A,
v$EVENT_NAME B,
v$SQLAREA C
WHERE A.SAMPLE_TIME BETWEEN '&starttime' AND
'&endtime' AND
A.EVENT# = B.EVENT# AND
A.SESSION_ID= &sid AND
A.SQL_ID = C.SQL_ID
GROUP BY C.SQL_TEXT, B.NAME

(9) Top session on CPU in last 15 minute

SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= 'ON CPU' AND
sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
ORDER BY count(*) desc
)
where rownum <= 10;

How To Generate ASH Report

Oracle Provide a standard script just like AWR to analyze the ASM data

I will explain how to generate ASH(Active Session History) reports from SQL*PLUS. The same can be done using Enterprise Manager also.

Script Location is same as other standard script $ORACLE_HOME/rdbms/admin/

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
848748484 TECHDB 1 TECHDB

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: html
..
..
Defaults to -15 mins
Enter value for begin_time: 01/11/15 00:00:00
Report begin time specified: 01/11/15 00:00:00

Enter value for duration: 10
Report duration specified: 10

Using 11-Jan-15 00:00:00 as report begin time
Using 11-Jan-15 00:10:00 as report end time

Enter value for report_name: Example

Summary of All User Input
————————-
Format : HTML
DB Id : 848748484
Inst num : 1
Begin time : 11-Jan-15 00:00:00
End time : 11-Jan-15 00:10:00
Slot width : Default
Report targets : 0
Report name : Example

End of Report
</body></html>
Report written to Example

Active Session History Archive

Active Session History collects information on active sessions from the database instance every second. Depending on the database activity, that will lead to a lot of data collected inside the ASH buffer, but because the ASH buffer is a memory-resident structure, it has only a finite amount of space. In addition, when the instance goes down, the instance’s memory vanishes with it. Therefore, Oracle Database archives the information from the ASH buffer to a database table to make it persistent. This archived table data is visible in a view called DBA_HIST_ACTIVE_SESS_HISTORY. If you don’t find the data in the V$ACTIVE_SESSION_HISTORY view, check for it in the DBA_HIST_ACTIVE_SESS_HISTORY view

Related Articles

Automatic Workload Repository
Optimizer hints
Oracle Performance tuning Glossary
Oracle Explain Plan
Oracle Dynamic Performance Views