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

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Tue, 15 Mar 2011 06:59:40 -0700 (PDT)
Message-ID: <264224.87755.qm_at_web83604.mail.sp1.yahoo.com>

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_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.

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 15 2011 - 08:59:40 CDT