ASH Script for Finding out Top Wait Events in Oracle

prompt ************************************ prompt **** ASH OVERALL WAIT PROFILE prompt ************************************ set lines 999 SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available duration FROM v$active_session_history; select * from ( select NVL(event,'CPU') event,count(*), round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history WHERE user_id0 AND sample_timetrunc(sysdate-1) group by event order by 2 desc ) where rownum<10;

ASH and AWR Scripts for Checking Wait Events and Top Consuming SQL’s

Active Session History 11g

Even when sessions are inactive they can be executing SQL's in the background. You have to identify the SQL's which are executing. Run this query as sysdba user. If you are using RAC then replace v$active_session_history with gv$active_session_history select ash.SQL_ID , sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" , sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User … Continue reading Active Session History 11g