Re: Reading UNDO and displaying it in v$session

From: Milen Kulev <>
Date: Thu, 03 Apr 2008 15:49:58 +0200
Message-ID: <>

Hello Kyle,
many thanks for you prompt answer.
Yes, I can confirm that


are NOT dependable on P1 and P2.
There a perfect match between the values of P1 <-> ROW_WAIT_FILE# and P2 <-> ROW_WAIT_BLOCK# of v$session view ONLY when wait event is either 'db file sequencial read' or 'db file scattered read'. When the session waits on 'direct path read temp' , the columns ROW_WAIT_FILE# and ROW_WAIT_BLOCK# seems to display the last P1 and P2 and the last ROW_WAIT_OBJ# accessed when the last wait event was 'db file sequencial read' or 'db file scattered read'.

A short recap od the findings (any feedback and corrections are welcome!) :  

  1. When ROW_WAIT_OBJ# > 0 and v$session.event is either 'db file sequential read' or 'db file scattered read' then ROW_WAIT_OBJ# could be used to fully resolve the database object being accessed at the moment (indexes, table, partitions etc).
  2. When ROW_WAIT_OBJ# = 0
  3. if EVENT = 'db file sequential read' then session is accessing UNDO tablespace
  4. if EVENT != 'db file sequential read' then the session HAD accessed UNDO tablespace In this case ROW_WAIT_FILE# <-> P1 and ROW_WAIT_BLOCK# <-> P2 doesn't match anymore
  5. When ROW_WAIT_OBJ# = -1 (the only negative value I could get)
  6. the process is waiting for some background job to do its work. For example when EVENT = 'log file sync', the session is waiting for LGWR process to write the redo vector
  7. when the process is a background process (DBWR, LGWR), then ROW_WAIT_OBJ# ALWAYS equals -1. In this case EVENT is clearly identifiable through its wait event ('log file parallel write' -> LGWR, for example)

This is what I have found so far.

Best Regards. Milen

  • Original-Nachricht --------
    > Datum: Wed, 2 Apr 2008 14:29:37 -0700
    > Von: "kyle Hailey" <>
    > An:
    > CC:
    > Betreff: Re: Reading UNDO and displaying it in v$session

> don't seem to be as dependable as P1 and P2 for IO events. Not sure
> why. My guess is that sometimes when they are not filled out, then the
> values are left over from previous usage (like a db file sequential
> read from UNDO in your case)
> What are the value of P1 and P2 for your direct path write temp waits
> ( ie your File and Block)?
> Best
> Kyle
> --

GMX startet Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein:
Received on Thu Apr 03 2008 - 08:49:58 CDT

Original text of this message