RE: Performance issue with locking which appears to be fixed by increasing system tablespace.
Date: Tue, 15 Mar 2011 16:19:43 -0400
Message-ID: <027001cbe34e$52caae80$f8600b80$_at_rsiz.com>
What Gaja wrote seems right to me. I suppose if the LGNCC_LOCK table was IN the system tablespace and the holder of the row lock was trying to expand the row such that it was trying to migrate to a new block that required space that did not exist and it was waiting for more space to be available, then a whole pileup of others wanting that row could burn a lot of wait in parallel waiting versus the one waiting for space, so it wouldn't show up. But I'm grasping at straws with that, trying to think of ANY reason why increasing the system tablespace would alleviate the problem. That is the only corner case I can think of and I'm not sure whether that would throw something else obvious into the report.
So Gaja is probably dead bang on. And problems that go away by themselves come back by themselves.
Oh, did the DBA bounce everyone out to increase the SYSTEM tablespace (whether or not he had to), so the problem had to recur from ground zero? Just not reaching a level of concurrency (YET) that drives the problem matches that action as well (as for any reason to have everyone stop working for a while tends to temporarily eliminate contention issues.).
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Gaja Krishna Vaidyanatha
Sent: Tuesday, March 15, 2011 10:00 AM
To: Oracle-L List
Subject: Re: Performance issue with locking which appears to be fixed by
increasing system tablespace.
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> 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-lReceived on Tue Mar 15 2011 - 15:19:43 CDT