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.
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 |