Re: Can a deferred FK constraint cause "enq: TX - row lock contention" in Share (4) mode?

  • From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 04 Jul 2014 07:50:16 +0200

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


Other related posts: