Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking locks with no blocker
Date: Wed, 1 Apr 2020 19:26:33 +0000
Message-ID: <LNXP265MB1562FD59765630645C10495EA5C90_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
I nearly answered that maybe it didn't have a blocking_instance to go with it, but gv$session (in 12.2.0.1) has 6 columns:
BLOCKING_SESSION_STATUS VARCHAR2(11) BLOCKING_INSTANCE NUMBER BLOCKING_SESSION NUMBER FINAL_BLOCKING_SESSION_STATUS VARCHAR2(11) FINAL_BLOCKING_INSTANCE NUMBER FINAL_BLOCKING_SESSION NUMBER
So it has places to record the information. Have you checked the FINAL_XXX in case they are populated ?
Regards
Jonathan Lewis
From: Chris Taylor <christopherdtaylor1994_at_gmail.com> Sent: 01 April 2020 20:15
To: Jonathan Lewis
Cc: Oracle L
Subject: Re: Weird Situation (12.1.0.2 Exadata Cloud _at_ Customer) - Blocking locks with no blocker
Oh , that's good to know. I should have caught that (as I've wondered about it in the back of my mind but didn't check).
Any idea/thoughts why GV$SESSION.BLOCKING_SESSION wouldn't catch the SID it was blocked by?
Chris
On Wed, Apr 1, 2020 at 3:03 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
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<mailto: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><mailto: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><mailto: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><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>><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:
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>><mailto: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>>><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: 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>><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 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>>><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:
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>>><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: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>>>><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: 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>>>>><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 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>>>>>><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<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>>>>>><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<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>>>>>><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<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>>>>>><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<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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 01 2020 - 21:26:33 CEST