RE: Performance issue with locking which appears to be fixed by increasing system tablespace.
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <gajav@xxxxxxxxx>, "'Oracle-L List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Mar 2011 16:19:43 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@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:
