Oracle ASH(Active Session History) Useful queries

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 licenced feature.
You can disable ash by setting the 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 sampledSome important points in this regard
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 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 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#

 

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