Re: what row_wait_obj#, row_wait_file#, row_wait_block#

niy38_at_hotmail.com wrote:

>

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 Received on Thu Jun 29 2006 - 17:16:44 CDT