3

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;
0

1 Answer 1

3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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