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>
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'.
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!) :
- 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).
- When ROW_WAIT_OBJ# = 0
- if EVENT = 'db file sequential read' then session is accessing UNDO tablespace
- 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
- When ROW_WAIT_OBJ# = -1 (the only negative value I could get)
- 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
- 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-lReceived on Thu Apr 03 2008 - 08:49:58 CDT