RE: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <thomas.kellerer@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Jul 2014 12:58:39 -0400
If you're at a version subject to adaptive direct reads, one cheap shot gun
approach to this is to do a forced read into cache of the tables involved in
the query (all the tables, not just the ones being updated, since reads can
most certainly be involved in cleanouts).
If you fetch up one rowid per block and use that list of rowids to select some
non-indexed column, that is pretty effective at forcing sga cache reads, which
facilitate permanence of that particular cleanout. It gets a bit more
complicated for multi-block rows and out of line storage.
If the problem goes away, then you haven't proven root cause, but it would seem
an unlikely coincidence. Something of a treadmill could be driven by this
procedure, so you need to be vary of CTD (compulsive tuning disorder.)
If the problem does NOT go away, then something else is either driving
cleanouts or the original contention report.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Thomas Kellerer
Sent: Friday, July 04, 2014 1:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Can a deferred FK constraint cause "enq: TX - row lock contention"
in Share (4) mode?
Rich,
> Are there LOB(s) in the table being updated?
No LOBs in the table (and thus none being updated).
> I think CURRENT_OBJ# is set equal to -1 before being updated by the
> process with the actual object number (some of the sampling just caught it
> before it was populated).
Hmm, it's nearly half of the waits. Could it really be that?
> Are the rows being updated "clean"?
> (thinking it might be deferred block cleanout and/or transaction
> rollbacks?)
You might be onto something. The AWR report (that led me to investigate the
UPDATE statement) shows the following:
Statistic Total per Second
per Trans
--------------------------------------------------------------------------------------------
cleanout - number of ktugct calls 189,612 52.55
6.07
cleanouts and rollbacks - consistent read gets 62,875 17.43
2.01
cleanouts only - consistent read gets 40,159 11.13
1.29
transaction rollbacks 37 0.01
0.00
transaction tables consistent read rollbacks 1 0.00
0.00
transaction tables consistent reads - undo records applied 1 0.00
0.00
user commits 28,799 7.98
0.92
user rollbacks 2,437 0.68
0.08
The report covers 60 minutes
> Can you trace the update in question?
Unfortunately not.
Regards
Thomas
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
