Re: Negative ROW_WAIT_OBJ#

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Jan 2020 17:50:46 +0000
Message-ID: <CWLP265MB15409C3866F82E0EFF96A75BA5230_at_CWLP265MB1540.GBRP265.PROD.OUTLOOK.COM>

I could have phrased that better. "32 bit rollover" in this case is the effect of one piece of code use a 4-byte value as unsigned 32-bit which means large and positive, while another treats it as signed 32-bit and reports it as negative. The value is consistent with values used for reporting object row_wait_obj# for materialized WITH subqueries - if you look at execution plans involving TEMP TABLE TRANSFORMATIONS you get object names like  SYS_TEMP_0FD9D669C_E3EB2E7, and if you take the 0FD9D669C that turns into 4254951068 (a little under 2^32) as unsigned or -40016228 (which is fairly close to the OP's value).

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Andy Sayer <andysayer_at_gmail.com> Sent: 03 January 2020 16:40
To: gogala.mladen_at_gmail.com
Cc: Oracle-L Freelists; rakeshra.tr_at_gmail.com Subject: Re: Negative ROW_WAIT_OBJ#

Just to note that the session probably wasn’t currently in a wait at this point (or you have seriously slow IO at 25 seconds!) The docs say that these row_wait% columns are only valid if you are being blocked by a transaction, meaning this could just be garbage.

I don’t think -40002177 is the right scale for a 32 bit rollover. We crossed over with object_id a while ago and in order to fix the number you can convert it to binary, take the signed 2’s complement and then reconvert that to decimal. For -40002177, this gives you <2^32.

Sorry that’s not much help,
Andy

On Fri, 3 Jan 2020 at 16:15, Mladen Gogala <gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>> wrote: You can always check v$session_wait. Since the event is scattered read, the event arguments will be the file id, block# and the number of blocks to read. You can then for out the object# from dba_extents. The session view wouldn't be my first choice for investigating such events.

On Fri, Jan 3, 2020, 08:32 Rakesh Ra <rakeshra.tr_at_gmail.com<mailto:rakeshra.tr_at_gmail.com>> wrote: Hi Listers,

I am monitoring one of the SQL session which is having a long running SQL . In order to identify the object in wait I am using the below SQl against v$session and I am getting a negative obj#. What does this mean? Can someone

select sid,serial#,event,ROW_WAIT_OBJ#,status,seq#,seconds_in_wait from v$session where sid=400;

  SID    SERIAL# EVENT                                ROW_WAIT_OBJ# STATUS         SEQ# SECONDS_IN_WAIT


---------- ---------- ------------------------------------ ------------- -------- ---------- ---------------


       400      16615 db file scattered read                   -40002177 ACTIVE          497              25

BR,
RRA
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 03 2020 - 18:50:46 CET