Re: Performance issue with locking which appears to be fixed by increasing system tablespace.
Date: Tue, 15 Mar 2011 06:59:40 -0700 (PDT)
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.
Gaja Krishna Vaidyanatha,
Founder/Principal, DBPerfMan LLC
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_at_btinternet.com> To: oracle-l_at_freelists.org
Sent: Tue, March 15, 2011 4:10:09 AM
Subject: Performance issue with locking which appears to be fixed by increasing system tablespace.
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 ?
FredReceived on Tue Mar 15 2011 - 08:59:40 CDT