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

It's not that I cannot find the blocker. It's that blocking_session is NULL on gv$session and no rows in DBA_BLOCKERS. Which is puzzling.

Some of these more advanced scripts can isolate the HOLDER & WAITER but I'm puzzled why BLOCKING_SESSION wouldn't be populated in the database.

I "think" you kind of circled around it a bit in a comment about being a RAC and lock modes in an earlier comment.

Chris

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

>
> Chris,
>
> I think I may have missed the same point from (at least one) of the
> previous outputs you sent me.
> I've just checked the flat file you sent me joining gv$lock and
> gv$sessions - and that's the one which excluded BACKGROUND and therefore
> made it look as if you waiters without holders.
>
> As far as you're concerned - do you still have any blocking locks with no
> blocker?
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
> Sent: 01 April 2020 18:14
> To: Jonathan Lewis; ORACLE-L
> Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) -
> Blocking locks with no blocker
>
> _at_Jonathan Lewis<mailto:jonathan_at_jlcomp.demon.co.uk> I'm a little
> confused.
>
> I included outputs EXCEPT for the one you asked for that was a join
> between gv$lock and gv$session.
> Those sids are in most of the previous outputs I sent?
>
> But yes, those 3 are database scheduler jobs.
>
> Thanks,
> Chris
>
>
> On Wed, Apr 1, 2020 at 1:06 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> <mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
>
> If you look at rows 23,51.84 there are your three long-time blockers
>
>
> INST_ID ADDR KADDR SID TYPE
> ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
> 1 0000002AFF0ABF18 0000002AFF0ABF98 6956 TX
> 2555927 56779000 6 0 12960 1 0
> 3 0000002AFF40C238 0000002AFF40C2B8 4871 TX
> 92864542 2439787 6 0 29356 1 0
> 3 0000002AFEC73E18 0000002AFEC73E98 3499 TX
> 93782036 2876653 6 0 39862 1 0
>
>
> So now you have to work out why instance 1, sid 6956; 3/4871, and 3/3499
> aren't showing up on your other reporting mechanisms.
> It's also a little odd that they acquired their locks a long time ago -
> much longer than the current waiters started waiting.
> Are they background processes (like jobs, perhaps) that you omit from your
> query ?
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>>
> Sent: 01 April 2020 17:43
> To: Jonathan Lewis
> Cc: Oracle L
> Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) -
> Blocking locks with no blocker
>
> Attached are the results for that.
>
> Chris
>
>
> On Wed, Apr 1, 2020 at 12:15 PM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>> wrote:
> I had intended the replies to go to Oracle-l as well - so copying them
> back in.
>
> The query against gv$session join gv$lock shows that there are no sessions
> holding the TX locks that are being waited for - and the waits are mode 6.
> Could you just do a listing of
>
> select * from gv$lock where type = 'TX' order by id1, id2, lmode;
>
> Just in case there's a way in which a lock could be held for a session
> that doesn't exist.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com>>>
> Sent: 01 April 2020 17:04
> To: Jonathan Lewis
> Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) -
> Blocking locks with no blocker
>
> Also have this query which the output is a bit nicer:
>
> SELECT /*+ RULE */
> k.inst_id,
> ss.username,
> DECODE (request, 0, 'Holder: ', ' Waiter: ') || k.sid
> sess,
> ss.sql_id,
> k.id1,
> k.id2,
> k.lmode,
> k.request,
> k.TYPE,
> SS.LAST_CALL_ET,
> SS.SECONDS_IN_WAIT,
> SS.SERIAL#,
> SS.MACHINE,
> SS.EVENT,
> ss.status,
> P.SPID,
> CASE
> WHEN request > 0
> THEN
> CHR (BITAND (p1, -16777216) / 16777215)
> || CHR (BITAND (p1, 16711680) / 65535)
> ELSE
> NULL
> END
> "Name",
> CASE WHEN request > 0 THEN (BITAND (p1, 65535)) ELSE NULL END
> "Mode"
> FROM GV$LOCK k, gv$session ss, gv$process p
> WHERE (k.id1, k.id2, k.TYPE) IN (SELECT ll.id1, ll.id2, ll.TYPE
> FROM GV$LOCK ll
> WHERE request > 0)
> AND k.sid = ss.sid
> AND K.INST_ID = SS.INST_ID
> AND ss.paddr = p.addr
> AND SS.INST_ID = P.INST_ID
> ORDER BY id1, request;
>
>
>
> On Wed, Apr 1, 2020 at 11:56 AM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>>> wrote:
>
> But then I realised that I'd forgotten to change the name of the table
> owner and table
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: Chris Taylor <christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>>>>
> Sent: 01 April 2020 16:47
> To: Jonathan Lewis
> Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) -
> Blocking locks with no blocker
>
> Here's the output for this modified version of what you asked for. Wasn't
> sure what you'd like to see from gv$session
>
> select l.*, v.inst_id, v.sid, v.serial#,v.program, v.event,
> v.wait_time_micro/1000000 as wait_time_secs, v.wait_class
> from gv$lock l, gv$session v
> where v.inst_id = l.inst_id
> and v.sid = l.sid
> and v.type != 'BACKGROUND'
> and v.wait_time_micro/1000000 > 30
> and v.wait_class != 'Idle'
> Order by l.sid, l.type
> /
>
>
>
> On Wed, Apr 1, 2020 at 11:28 AM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto: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<mailto:
> christopherdtaylor1994_at_gmail.com><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com><mailto:
> christopherdtaylor1994_at_gmail.com<mailto: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><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>><mailto:
> jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk><mailto:
> 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
> ><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org>><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> >>><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> ><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org>>>><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> >><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>>><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> ><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org>><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org><mailto:
> 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
> ><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org>><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> >>><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> ><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org>>>><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> >><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>>><mailto:
> oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org
> ><mailto:oracle-l-bounce_at_freelists.org<mailto:
> oracle-l-bounce_at_freelists.org>><mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org><mailto:
> 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><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> >>>><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>>><mailto:
> christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com
> ><mailto:christopherdtaylor1994_at_gmail.com<mailto:
> christopherdtaylor1994_at_gmail.com>><mailto:christopherdtaylor1994_at_gmail.com
> <mailto:christopherdtaylor1994_at_gmail.com><mailto:
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l