Not Waiting

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.