Search This Blog

Total Pageviews

Monday, 3 October 2011

Oracle who is taking more CPU in last 15 Min !!!!!!!!!!!!!

most CPU in last 15 minutes
Sql Taking more CPU
last wait in 15 min


event last 15 Min

SELECT a.event, sum(a.wait_time + a.time_waited) total_waits
FROM v$active_session_history a
WHERE a.sample_time between sysdate-1/24/4
AND sysdate
GROUP BY a.event
ORDER BY 2 desc;


EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
control file parallel write 5718745
4459398
log file parallel write 3522504
os thread startup 2449356
db file sequential read 624895
control file sequential read 108942
db file async I/O submit 96203
db file scattered read 0




SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= 'ON CPU'
AND sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
ORDER BY count(*) desc
)
where rownum <= 5;




for 15 row

SELECT * FROM
(
SELECT s.username, s.module, s.sid, s.serial#, s.sql_id,count(*)
FROM v$active_session_history h, v$session s
WHERE h.session_id = s.sid
AND h.session_serial# = s.serial#
AND session_state= 'ON CPU'
AND sample_time > sysdate - interval '15' minute
GROUP BY s.username, s.module, s.sid, s.serial#,s.sql_id
ORDER BY count(*) desc
)
where rownum <= 15;





SELECT SQL_TEXT FROM V$SQL WHERE sql_id = '&sql_id';

No comments:

Oracle DBA

anuj blog Archive