Monitor Session Waits

Home

Toolkit:

• Index
• Scripts
• Quick Tips
• Articles / Publications
• Links

My OraFAQ Blog

Contact me

Monitor session waits. If your application suddenly slows down, this is the first place you should look.

/* This shows waits */
select s.username, s.osuser, s.machine, s.serial#, w.* 
from v$session_wait w, v$session s
where s.sid=w.sid 
and w.event <> 'SQL*Net message from client' 
and w.event<>'rdbms ipc message'
order by s.username ;

RAC version:

select s.INST_ID, s.username, s.osuser, s.server, s.machine, s.serial#, w.* 
from gv$session_wait w, gv$session s
where s.sid=w.sid and w.event not in 
( 'SQL*Net message from client', 'rdbms ipc message', 'jobq slave wait')
order by s.username ;

Depending on the results, you may want to drill down further into:

• latch free waits
• library cache pins
• locks
• the SQL library (v$sqlarea)
• running jobs
• rollback segments

---

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...