Monday, February 18, 2013

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%';

image

select A.KSPPINM,B.KSPPSTVL,A.KSPPDESC
from x$KSPPI A, X$KSPPCV B
where A.INDX = B.INDX and a.KSPPINM like '_ash%';

image

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

image

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

image

ASH Report

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

image

 

Enable OEM Could control to monitor ASH

Performace –> ASH Analytics

image

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

image

image

image

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates