| TX Lock Wait time disappears when gather stat is run [message #505983] |
Thu, 05 May 2011 17:45  |
 |
benodom
Messages: 4
Registered: May 2011
|
Junior Member |
|
|
Hi Guys,
We are experiencing tx row lock wait time over hours. There is no blocking session and it seems that the application hangs. What is funny is that when we gather_stats on the tables, those tx row lock wait are being released. Anyone as an idea on what is happening here?
Thanks
Dom
|
|
|
|
|
|
|
|
|
|
|
|
| Re: TX Lock Wait time disappears when gather stat is run [message #506888 is a reply to message #506035] |
Wed, 11 May 2011 10:49  |
|
benodom
Messages: 4
Registered: May 2011
|
Junior Member |
|
|
Sorry guys,
I am working a lot of hours and shift work and did not have time to get back to you since this problem has a lower priority. On top of this, I cannot display any data even though they are not confidential. This may help you understand my position better.
So, to give you more information on my system, the auto gather stats is turned off (required by the consultant company that developed the system). We have partitioned tables called DAYYYYMMDD which we write to on a daily basis. So, we start from o records to hundred of thousands and more... every partition gets written to with a different ratio meaning that some partitions will grow faster than others. Also, every day we change partition to the next day. We have 15 partitioned tables (same logic). We run stats once a day at night. This is a business requirement, no possible change there.
So, what I have discovered is that we have stale stats on those day partitions. By running stats on those, it seems to release the lock wait but not all the time. We suspect an error in the code (but again, do not have access to the code since it is contractor that has built our system). They are looking into it thought.
The problem with this is since we start every day with 0 records in those partitions, the stats become stale very often. It is clear to me that this is due to the factor of 10% (default) modifications being made on those partitioned tables... Let`s say we inserts 10 records, than 10% of this is 1 new insertion would bring the stats in stale....
I know I cannot provide you with a lot of information and I am very sorry. This is really difficult when trying to get help, but management knows about it. So, if you have any idea, let me know. At this point, I think I am to ask the consultant to put back in the auto gather stats. I believe they are scared of the performance since their entire system was not tested with this option turned on.
John, what I can tell you is that we would have a similar output as this:
sessionID process machine seconds_in_wait
X XX XXXXX 2120
you can tell the process is having a seconds_in_wait really high. Usually it should be runing between 0 and 10 sec.
Thank you guys for taking the time to write to me. Sorry again for being so vague. I do my best with what I can. 
[Updated on: Wed, 11 May 2011 10:50] Report message to a moderator |
|
|
|
|
|
|
|
| Re: TX Lock Wait time disappears when gather stat is run [message #506898 is a reply to message #506897] |
Wed, 11 May 2011 12:41 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Black Swan is dead right. You have to begin by making some observations, not by drawing conclusions. The most basic observation is that query I gave you. Why can't you post the results? THere is nothing remotely confidential, we all have the same session numbers and wait events.
And by the way, if your application developer is worried that updated statistics might cause SQLs to regress, tell him to attend an 11g DB Admin course. There are many ways of preventing this. Enabling SQL plan baselines, for instance. Or simply deferring the statistics publication until you've tested the effect.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
