Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I'm trying to track the different wait time classes for current Oracle sessions but I must be misunderstanding something. When I compare the sum(time_waited)/1000 for a given session in gv$session_wait_class its often greater than the value of seconds_in_wait from gv$session and sometimes even greater than its last_call_et which I thought would be the total time...

What am I missing here? Why aren't they the same? I'm comparing numbers with this query:

select last_call_et,wait_time,seconds_in_wait,wait_time_micro/1000000,
(SELECT SUM(time_waited)
  FROM gv$session_wait_class ot WHERE
  ot.sid = ses.sid
  AND ot.serial#                          = ses.serial#
  AND ot.inst_id                          = ses.inst_id
  )/1000 as wait_time_class from gv$session ses where sql_id is not null;
share|improve this question
I know nothing about Oracle, but could that be due to multiple cores? – Max Vernon Nov 9 '12 at 3:10

1 Answer

up vote 3 down vote accepted

From the description of v$session:

SECONDS_IN_WAIT
If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.
This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO.

WAIT_TIME_MICRO
Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait.

These aren't cumulative measures, they're only related to the current or last wait event.

v$session_wait_class on the other hand sums up the times a session has been in different wait classes.

V$SESSION_WAIT_CLASS displays the time spent in various wait event operations on a per-session basis.

TIME_WAITED
Amount of time spent in the wait class by the session

So it's understandable that the times indicated in [g]v$session_wait_class are larger than the ones you're looking at in [g]v$session.
The opposite could be true too though, depending on when a wait is accounted in the wait class view. If it's accounted once the wait event is over and not dynamically (I think that's what happens), the current wait could be larger than the sum of previous waits.

share|improve this answer
v$session_wait_class is updated dynamically. You can see this easily by updating some data one session, leaving it uncommitted and updating the same data in other session (which will be blocked). You'll see the time waited for the 'Application' wait class increasing continually for the second session. – Chris Saxon Nov 9 '12 at 9:21
Interesting. Doesn't seem to update the Idle class continuously though here (11gR2). – Mat Nov 9 '12 at 9:34
Ok, that makes sense. That answers all except the last bit - why is is more than last_call_et which according to the docs seems to be transaction time. – rfusca Nov 9 '12 at 13:23
last_call_et measures the time since the last switch between active/inactive states. I don't see why that would be larger or smaller than the accumulated wait times (modulo idle time accounting). – Mat Nov 9 '12 at 13:30
Hmm, I'll have to think on that some, but it makes sense - thanks! – rfusca Nov 9 '12 at 13:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.