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;

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 I/O’,1,0),0)) “IO” ,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id ;

You will see something like

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
8tfvwyvfm5cjn          0          1          0          1
1xpfbutrrzkph          0          1          0          1
aykvshm7zsabd          0          1          0          1
92f47aa2q2rmd          0          1          0          1
92b382ka0qgdt          0          4          0          4
1h50ks4ncswfn          0          3          1          4
f99a23s9aba0z          0          0          1          1
f6cz4n8y72xdc          0          0          1          1
cvn54b7yz0s8u          0          0          1          1
3s58mgk0uy2ws          0          1          0          1
fnk7155mk2jq6          0          1          0          1

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
c2p32r5mzv8hb          0          1          0          1
5p6a1yss527ap          0          1          0          1
6gvch1xu9ca3g          0          1          0          1

Now see which statement shows maximum value in Total column. Take the SQL ID  and run below query

SELECT sql_text FROM v$sqlarea WHERE sql_id = ‘1h50ks4ncswfn’;

This will give you an SQL statement. See which table is it running on and what is it doing. Maybe it is a poorly written SQL which is locking the table or looping on the table. Do an explain plan for this statement.

SELECT * FROM table(dbms_xplan.display_awr(‘1h50ks4ncswfn’));

This way you can identify the top consuming SQL’s running inside your Oracle database. This procedure was run on a 11gR2 database but is also valid for 10gR2.