Performance Monitoring: Active Session History at work

New Feature Active Session History (ASH)  in oracle 10g. That was one major improvement – together with the Automatic Workload Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) – of the 10g version. Way better than STATSPACK was before!

Imagine you are a DBA on a production system and get an emergency call like “The Database is dead slow!”. You are supposed to spot the cause as soon as possible. ASH kicks in here: We sample the Wait-Events of active sessions every second into the ASH-Buffer. with little effort from the command line like this :

-----------------------------------------
--
-- Top 10 CPU consumers in last 5 minutes
--
-----------------------------------------
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
--------------------------------------------
--
-- Top 10 waiting sessions in last 5 minutes
--
--------------------------------------------
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING'  and  sample_time >  sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
--------------------
--
-- Who is that SID?
--
--------------------
select  serial#,username, osuser, machine, program, resource_consumer_group, client_info
from v$session where sid=SID;
-------------------------
--
-- What did that SID do?
--
-------------------------
select distinct sql_id, session_serial# from v$active_session_history
where sample_time >  sysdate - interval '5' minute
and session_id=SID;
----------------------------------------------
--
-- Retrieve the SQL from the Library Cache:
--
----------------------------------------------
select sql_text from v$sql where sql_id='SQL_ID';


By  Uwe Hesse ....