Re: Reading UNDO and displaying it in v$session

From: Milen Kulev <makulev_at_gmx.net>
Date: Thu, 03 Apr 2008 15:49:58 +0200
Message-ID: <20080403134958.204570@gmx.net>

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

ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#

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" <kylelf_at_gmail.com>
  > An: makulev_at_gmx.net
  > CC: oracle-l_at_freelists.org
  > Betreff: Re: Reading UNDO and displaying it in v$session
  

> ROW_WAIT_OBJ#
> ROW_WAIT_FILE#
> ROW_WAIT_BLOCK#
> 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
> http://perfvision.com
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 03 2008 - 08:49:58 CDT