Skip to Main Content
  • Questions

  • Committed to fully understanding v$session_wait (wait_time, state & wait_time_micro)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, John.

Asked: January 24, 2016 - 2:40 pm UTC

Last updated: January 26, 2016 - 12:57 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Good Morning,

The method to figure out what a session is currently doing from v$session_wait is not too clear. I wish Oracle would improve that so that we could simply query v$session_wait and it would say:

session A is has been waiting for a lock for 10 seconds.
session B has had lock for 60 seconds.
session C is idle.


In the meantime, I want to understand the "state" column. It seems like the value for it is always "waiting". If i have a session that just logs in, the value for the state is waiting. If a session just performed a commit, the value is waiting. If a session is waiting on a lock to be released, its the value is waiting. This column is only causing confusion. What is up with this column?

Thanks,

John



and Connor said...

In all three cases, the session is *indeed* waiting on *someone*:

"If i have a session that just logs in, the value for the state is waiting. "

Yes. It is waiting on 'you' to ask it to do something. When I get a request from the client to do something I will do it... in the mean time, I will wait for them to do that. If that client is coming in over the network, I am waiting for a meesage to come in over SQL Net from the client, hence "SQL*Net message from client" as the wait name.


"If a session just performed a commit, the value is waiting. "

I've just asked the LGWR to guarantee that my changes are recorded the redo logs. So I've asked, and now I'm waiting for lgwr to tell its done. I am waiting for the lgwr to tell me that the log file is in sync (hence "log file sync" as the wait name)

"If a session is waiting on a lock to be released, its the value is waiting. "

Same as above. I am waiting on someone else to do something (in this case, release the lock). I am in a queue waiting for my turn to get access to the resource (probably a table row). Hence the "enqueue" wait.

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

State column's purpose?

John Cantu, January 25, 2016 - 1:26 pm UTC

Hi Connor,

It seems like that column is unnecessary since the session state is almost always "waiting". That field isn't providing any useful information to the DBA or am I missing something?

The event field provides the wait event while the wait_time_micro field provides the amount of time it waits. Now those two fields provide valuable information.
Chris Saxon
January 25, 2016 - 3:41 pm UTC

Chris here. From the docs, state can be:

- WAITING - Session is currently waiting

- WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false

- WAITED SHORT TIME - Last wait was less than a hundredth of a second

- WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column


http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3024.htm#REFRN30229

John Cantu, January 25, 2016 - 5:41 pm UTC

Hi Chris,

I have seen those possible values for the 'state' column. It is just seems useless and it would confuse a DBA trying to figure out what the session is doing.

If session isn't doing anything, status is waiting.
If session is "waiting" on a lock, the status is waiting.
If session is has a lock, the status is waiting.

Its like saying the obvious like
you are a human.
i am a human
connor is a human.


- WAITING - Session is currently waiting

- WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter
TIMED_STATISTICS is set to false


- WAITED SHORT TIME - Last wait was less than a hundredth of a second.
What does it really matter if it was less than a hundredth of a second or a more.

- WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column
Okay, i planned to look at that column anyway. it is pointing out the obvious.

thanks, but it is clear to me that 'state' column provides little if any help.
please correct me if i am wrong.

thanks for your help, Chris.
Chris Saxon
January 26, 2016 - 12:57 am UTC

Dont forget that a session may change state 100's of times in a second, and v$session_wait is merely a instantaneous snapshot of that.

So a session may be on cpu, or it may have been waiting for a long time or short time etc... *Most* of the time, you'll probably see it as waiting because that whats most sessions typically do (unless the are batch processes etc and a belting away at your CPU)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.