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
0 comments:
Post a Comment