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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Apr 2020 19:02:40 +0000
Message-ID: <LNXP265MB15620A13F1C6F8A86ABDD400A5C90_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>

Sorry,

I completely missed the intent of your query. My hypothesis about RAC and BLOCK=1 was wrong. If you look at the gv$lock info you sent me, the holders of the blocking TX mode 6 do have BLOCK=1. (the waiters have block = 0, and everything else is block=2).

OTOH - dba_blockers does only look at the instance that it's accessed on: s.inst_id = userenv('Instance'). That will have an impact when you join it to a GV$. DBA_WAITERS does the same.

Regards
Jonathan Lewis

From: Chris Taylor <christopherdtaylor1994_at_gmail.com> Sent: 01 April 2020 19:15
To: Jonathan Lewis
Cc: ORACLE-L
Subject: 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<mailto: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<mailto: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<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><mailto: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><mailto: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>><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: 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

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>>><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:

But then I realised that I'd forgotten to change the name of the table owner and table

Regards
Jonathan Lewis

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>>>><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: 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>>>><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 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>>>>><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<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>>>>><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<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>>>>><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<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>>>>><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<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

Received on Wed Apr 01 2020 - 21:02:40 CEST

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message