Following Query’ Tells about > SQL Query's in RAC which are waiting to get CPU (or) Query is waiting on CPU >
Depend on result of following query
we will dive in and find set of sql’s which is waiting to get CPU
or which is waiting on CPU :)
SQL> select * from (
select
SQL_ID ,
sum(decode(session_state,'ON CPU',1,0)) as CPU,
sum(decode(session_state,'WAITING',1,0)) -
sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),
0)) as WAIT,
sum(decode(session_state,'WAITING', decode(wait_class, 'User
I/O',1,0),0)) as IO,
sum(decode(session_state,'ON CPU',1,1)) as TOTAL
from v$active_session_history
where SQL_ID is not NULL
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))
)
where rownum <11;
following .......
Run following Query to get wait events on database and match Query is waiting on CPU
SQL> SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1;
Get wait_class from gv$event_name & replace wait_class and find out culprit SQL on database >
SQL> SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, v$event_name evt
WHERE ash.sample_time > SYSDATE - 3/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'System I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;
From gv$active_session_history" we will get sql_id and helps to check execution plan of sql statement :) and Investigate further to decrease waits on cluster
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('xxxxxxxxxxxx'));
---Nikhil Tatineni---
---Oracle Database Admin ---
