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: 

select
	sid,
	state,
	event,
	wait_time,
	seconds_in_wait
from
	v$session_wait
;

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers