Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking locks with no blocker

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 1 Apr 2020 11:36:32 -0400
Message-ID: <CAP79kiRkHu1RnF=pXFD3YRGGgw0D3ZT6rtc+imnLv-wTu0jNpg_at_mail.gmail.com>



Here's the query

It joins:
 gv$locked_object.session_id = gv$session.sid and gv$locked_object.instance_id = gv$session.inst_id and gv$locked_object.process = gv$session.process

WITH T1 as

               (
               SELECT lo.inst_id, lo.object_id, lo.session_id,
lo.oracle_username, lo.os_user_name, lo.process, lo.LOCKED_MODE
                  FROM gv$locked_object lo
                 WHERE object_id IN
                           (SELECT object_id
                              FROM dba_objects
                            WHERE OWNER = 'OPTIREV'
                            and object_name like 'CLAIM'
                            )
                )

SELECT
v.logon_time, v.inst_id, v.sid,v.serial#, v.program,
v.wait_time_micro/1000000 as wait_time_secs,event, v.wait_class, v.status,
v.process, t1.object_id,t1.locked_mode, do.owner,
do.object_name,do.SUBOBJECT_NAME ,do.object_type,'alter system kill session '''||v.sid||','||v.serial#||',_at_'||v.inst_id||''' immediate;'   FROM gv$session v, t1, dba_objects do
 WHERE v.inst_id = t1.inst_id
 and v.sid = t1.session_id
 and v.process = t1.process
 and t1.object_id = do.object_id
ORDER BY wait_time_secs desc
/

I'll run your thing below but I'll have to use gv$session and gv$lock and add the inst_id

Thanks,
Chris

On Wed, Apr 1, 2020 at 11:28 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

> What was the query you ran to generate the output ?
>
> Can you run a query that joins v$session for the waiting sessions with
> v$lock on SID.
> Order by sid, type
> Break on sid skip 1
>
>
> Looking at the view dba_blockers it identifies blockers by BLOCK = 1.
>
> I don't have a RAC with me to check but ALL held locks (v$lock) in RAC are
> flagged with BLOCK = 2 (as "potential blockers") and MAY NOT go to 1 when
> they are blocking cross instance. So the problem may be the definition of
> dba_blockers - but I'd have to run up a couple of RAC instances to check -
> and you've got at least 3 handy to do a quick test on.
>
>
> Regards
> Jonathan Lewis
>
>
>
> ________________________________________
> From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
> Sent: 01 April 2020 16:07
> To: Jonathan Lewis
> Cc: ORACLE-L
> Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) -
> Blocking locks with no blocker
>
> Right now I've got 60 sessions waiting on that enqueue lock.
>
> Running this SQL (redacted a bit):
>
> UPDATE <table> SET REVERSED_ON = SYSTIMESTAMP, REVERSING_ID = :B1 WHERE
> SOME_ID IN (SELECT * FROM TABLE(:B2 )) ;
>
> _at_jonathan - I've attached an Excel sheet with locks, sessions and
> locking-mode.
>
> We do have some invoicing jobs running when are working on the same
> partitions that we're trying to update. But I'm still curious why
> blocker/blocking_session is null? If you could help me understand that
> part, I'd be most appreciative. I'm beginning to 'feel' like maybe this is
> expected type of locking but I want to understand the 'why'.
>
> Also included is a csv of the same if you don't want to crack open the
> excel file.
>
> Chris
>
>
> On Wed, Apr 1, 2020 at 10:30 AM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
> Since you're looking at gv$ does that mean you're running RAC ?
> TX - Row lock contention should be reporting mode 6 I think, but could you
> check that in case you're waiting for mode 4.
>
> When a session is waiting, are there other sessions also waiting for the
> same TX enqueue ?
>
> Regards
> Jonathan Lewis
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on
> behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>>
> Sent: 01 April 2020 14:38
> To: ORACLE-L
> Subject: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking
> locks with no blocker
>
> We've got a situation where we have sessions experiencing "enq: TX - row
> lock contention" with no blocking session.
>
> GV$SESSION.BLOCKING_SESSION is null
> DBA_WAITERS is empty
> DBA_BLOCKERS is empty
>
> I've gotten around this by joining gv$locked_object to gv$session where
> session.wait_class='Idle' and wait_time_micro/1000000 > 120 (seconds).
>
> Some of the locks are for sessions with thousands of wait seconds waiting
> on sqlnet.
>
> *BUT* the issue is, why isn't oracle able to find the blocking sessions?
> How can I dump/trace the blocking session manually?
>
> In Grid Control we see stuff like: "lock deadlock retry" in the wait
> events for the sessions waiting on "enq: TX - row lock".
>
> In the session trace files, we see stuff like "unable to determine final
> blocker" .
>
> Any thoughts?
>
> Chris
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 01 2020 - 17:36:32 CEST

Original text of this message