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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Andy Sayer <andysayer@xxxxxxxxx>
Sent: 03 January 2020 16:40
To: gogala.mladen@xxxxxxxxx
Cc: Oracle-L Freelists; rakeshra.tr@xxxxxxxxx
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@xxxxxxxxx<mailto:gogala.mladen@xxxxxxxxx>> 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@xxxxxxxxx<mailto:rakeshra.tr@xxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
