what row_wait_obj#, row_wait_file#, row_wait_block#
ni...@hotmail.com
Sybrand Bakker
>mean where lockwait is null in v$session?
They mean : the data_object_id of the object being locked
the file_id of the file being locked, and the block number of the
block being locked.
Pretty obvious and described in the v$session reference note
in the Reference manual on http://tahiti.oracle.com
Why people never bother to read the manual, but instead ask here, has
always escaped me.
--
Sybrand Bakker, Senior Oracle DBA
ni...@hotmail.com
Sybrand Bakker wrote:
> On 23 Jun 2006 12:07:56 -0700, ni...@hotmail.com wrote:
>
> >mean where lockwait is null in v$session?
>
> They mean : the data_object_id of the object being locked
> the file_id of the file being locked, and the block number of the
> block being locked.
> Pretty obvious and described in the v$session reference note
> in the Reference manual on http://tahiti.oracle.com
>
what's the difference when lockwait is not null?
interesting! is this your forum? if you don't like anwer question,
just go to sleep.
Sybrand Bakker
I like people to stop being lazy and not doing anything to resolve
their own problems. It will also be faster, and the effort on your
side would have been minimal.
Regrettably, you have demonstrated over and over again, you require
this forum to do your work.
I would suggest you switch job.
Jonathan Lewis
<ni...@hotmail.com> wrote in message
news:1151089676.3...@c74g2000cwc.googlegroups.com...
> mean where lockwait is null in v$session?
>
Somewhere in 9i, the code changed so that
any wait on a block seems to result in the row_wait_x
columns being populated (or partially populated)
and not being completely cleared down afterwards.
If the lockwait is null, all you can say is that some
wait event happened to the object file (and possibly
block and row) listed - some time in the recent past.
The wait MAY have been a lock wait (row lock) but
it may have been a buffer busy wait, etc.
The manuals have not yet caught up with this change.
--
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Mark D Powell
Jonathan Lewis wrote:
> <ni...@hotmail.com> wrote in message
> news:1151089676.3...@c74g2000cwc.googlegroups.com...
> > mean where lockwait is null in v$session?
> >
>
>
> Somewhere in 9i, the code changed so that
> any wait on a block seems to result in the row_wait_x
> columns being populated (or partially populated)
> and not being completely cleared down afterwards.
>
> If the lockwait is null, all you can say is that some
> wait event happened to the object file (and possibly
> block and row) listed - some time in the recent past.
>
> The wait MAY have been a lock wait (row lock) but
> it may have been a buffer busy wait, etc.
>
> The manuals have not yet caught up with this change.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
>
N, in the past if the v$session.lockwait column is null then it means
that the session is NOT currently waiting on a lock. When the
lockwaited column is not null is when a problem exists as the session
is waiting.
In the past you would have expected the row_wait_x columns to be null
when the lockwait column was null and perhaps to have values when it
was non-null. Now, at least according to the 0gR2 Reference manual
Oracle lists the blocking session in v$session.blocking_session. (time
to rewrite lock blocking scripts)
HTH -- Mark D Powell --
ni...@hotmail.com
when there is ITL wait, though lockwait has value, but
all row_wait_obj#, row_wait_file#, row_wait_block# has
no value
ni...@hotmail.com
might be a bug?
yon...@yahoo.com
For the sake of argument, let's be more accurate. When you say "no
value", you really mean their values are -1, 0, 0, respectively.
Documentation about these row_wait_xxx columns are a little tricky.
They say for each of row_wait_file#, row_wait_block# (as well as
row_wait_row#), "This column is valid only if the session is currently
waiting for another transaction to commit and the value of
ROW_WAIT_OBJ# is not -1." Because row_wait_obj# *is* -1, they're not
responsible for putting meaningful numbers in there. The question
becomes why not populate row_wait_obj#. The problem is circular logic
in documentation, "Object ID for the table containing the row specified
in ROW_WAIT_ROW#". That is, should the row# or obj# be populated first?
In fact, all these columns center around row_wait_row#, "current row
being locked". But with ITL wait, you don't say which row is locked.
You probably can say which block is locked (in the sense of ITL
shortage). For practical purposes, though, just find the SQL this
ITL-waiting session is executing. Then you know which block has ITL
shortage. But of course if the SQL shows bind variables and the version
of Oracle doesn't have v$sql_bind_capture, you resort to guess work.
Yong Huang