0 Active Session History (ASH)

ASH collect V$SESION and V$SESSION_WAIT every seconds and store in the Active Session History Buffer with SGA. The default size for Active Session History buffer is 5% of Shared pool or 5% of SGT_TARGET .

• When SGA usage reach 2/3, the MMNL write the information to AWR.
• Every 60 mins, the MMON sampling the ASH data ( 1/10 ) and write to the AWR.

 

Show Active Session history buffer size and configuration

select POOL, name , bytes/1024/1024 as MB from V$SGASTAT

where name like 'ASH%';

select A.KSPPINM,B.KSPPSTVL,A.KSPPDESC

from x$KSPPI A, X$KSPPCV B

where A.INDX = B.INDX and a.KSPPINM like '_ash%';

The _ash_size here is in bytes  and it is the min size of the Active session history buffer.

Most CPU intensive SQL in past 1 hour

SELECT A.SQL_ID,

  A.SESS_COUNT,

  A.CPU_LOAD,

  B.SQL_TEXT

FROM

  (SELECT sql_id,

    COUNT(       *) SESS_COUNT,

    ROUND( COUNT(*)/SUM(COUNT(*) ) over(),2)CPU_LOAD

  FROM V$active_session_history

  WHERE SAMPLE_TIME > SYSDATE -1/24

  AND session_type <>'BACKGROUND'

  AND session_state = 'ON CPU'

  GROUP BY SQL_ID

  ORDER BY COUNT(*) DESC

  ) A,

  V$SQLAREA B

WHERE A.SQL_ID=B.SQL_ID

MOST IO INTENSIVE SQL in PAST 1 hour

SELECT A.SQL_ID,

  A.SESS_COUNT,

  B.SQL_TEXT

FROM

  (SELECT c.sql_id,

    COUNT( *) SESS_COUNT

  FROM V$active_session_history c,

    v$event_name d

  WHERE c.SAMPLE_TIME > SYSDATE -1/24

  AND c.event_id      = c.event_id

  AND c.wait_class    ='User I/O'

  GROUP BY c.SQL_ID

  ORDER BY COUNT(*) DESC

  ) A,

  V$SQLAREA B

WHERE A.SQL_ID=B.SQL_ID

ASH Report

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

 

Enable OEM Could control to monitor ASH

Performace –> ASH Analytics

We need to install the package when first time using it.

 

Reference

• http://www.orafaq.com/wiki/ASH
• http://psoug.org/reference/ash.html
• _ash_size and ASH data buffer size: http://www.dba-oracle.com/t_ash_size_data_buffer_sizing_limit.htm
• ASH - Active Session History: https://sites.google.com/site/embtdbo/wait-event-documentation/ash---active-session-history