Oracle Scratchpad

May 15, 2009

Not Waiting

Filed under: Performance,Troubleshooting — Jonathan Lewis @ 6:55 pm BST May 15,2009

A recent posting on the OTN database general forum included the comment:

“… the wait event is : latch shared pool, and CPU is up to 100%, it has run over few hours and seems hang…”

This reminded me of a very common error that appears when people check v$session_wait … they forget to check the state column. The following query is the sort of thing you might check: 


When you review the results, remember that a session is only waiting if the state literally displays “WAITING”; otherwise the session is on the CPU or (if you want to split technical hairs) may be in the run queue.

If the state is anything else, then seconds_in_wait minus wait_time will tell you approximately (to around three seconds as the seconds_in_wait time is updated roughly every three seconds) how long you have been on the CPU (or in the run queue) since the last wait ended.

Note: you will appreciate from this that the column seconds_in_wait is very badly named – it should be more like “seconds_since_start_of_last_wait_with_an_error_of_up_to_three_seconds”, but there’s a 32 character limit on column names.

Note 2: from 10g, you could also query v$session instead of v$session_wait.

1 Comment »

  1. Jonathan,

    you forgot to mention root cause of that mistake: not reading documentation :)

    Comment by Timur Akhmadeev — May 18, 2009 @ 7:14 am BST May 18,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: