Re: Performance issue with locking which appears to be fixed by increasing system tablespace.

  • From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
  • To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 15 Mar 2011 06:59:40 -0700 (PDT)

Hi Fred,

An enq - TX row lock contention is ALWAYS an application related locking issue. 
This seems like an incredible coincidence that while the SYSTEM tablespace was 
re-sized, the locking issues were resolved. My gut feel says - Don't buy it!!! 
I'm really curious as to where in the sequence of an "application transaction" 
does the update to the LGNCC_LOCK table occur. Can you dig into the code and 
provide the sequence of events? I bet if you delve into this, you may discover 
that the application (vendor) has taken transaction management into their hands 
and implemented a "pessimistic locking model". And there was some other event 
that caused the locks to be released. Would love to see more detail on this.

Cheers,

Gaja
 Gaja Krishna Vaidyanatha,
Founder/Principal, DBPerfMan LLC
http://www.dbperfman.com
Phone - 001-(650)-743-6060
Co-author:Oracle Insights:Tales of the Oak Table - 
http://www.apress.com/book/bookDisplay.html?bID=314
Co-author:Oracle Performance Tuning 101 - 
http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-4625766




________________________________
From: Fred Tilly <ftilly@xxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Tue, March 15, 2011 4:10:09 AM
Subject: Performance issue with locking which appears to be fixed by increasing 
system tablespace.

Hi All,

Was recently asked to look at a performance issue on a customer site. 

I was not granted access to the server so asked for a statspack report covering 
a 15 minute period during poor performance.

I have shown below what I believe to be the relavent parts in the attached file:

Looking at this statspack output I started looking at the code in the 
application to see why it
would be holding locks for a long period of time.

While investigating the issue it was apparently resolved by the onsite DBA by 
increasing the size 

of the system tablespace.

Now what I would like to know is how increasing the size of the system 
tablespace would impact this type of
locking issue ?

Thanks

Fred

Other related posts: