| Resolving Latch Free Waits [message #450352] |
Tue, 06 April 2010 11:12  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
Are there any guidelines to resolve 'Latch Free' waits?
I am reading many articles which ends in classifying the waits into 1) cache buffer chains 2) Library Cache Latch Contention
For the latter, links point out finding hot block and adding free lists
However if I have ASSM then in above case how I can add free lists?
Could you brief me or suggest link to get following answers to resolve the issue?
1) How to find if latch free is because of library cache or hot block
2) If it is because of library cache what could be the reasons? And how to resolve it
3) If it is because of hot blocks how to resolve it?
4) Could there be any other reason apart from above?
Please help me getting these answers.
I have done googling even searched in this forum but haven't got exact material I needed.
Thanks and Regards,
prataps
|
|
|
|
|
|
| Re: Resolving Latch Free Waits [message #450357 is a reply to message #450352] |
Tue, 06 April 2010 11:51  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi BlackSwan,
2 things
1) I have already googled and have surfed many links but have not got exact answers to my questions posted here
2)I know the gudelines (i am using this forun for a long), but these are generic questions for which I could not got solutions more than once
Thanks and Regards,
prataps
|
|
|
|
|
|
|
|
| Re: Resolving Latch Free Waits [message #450427 is a reply to message #450352] |
Wed, 07 April 2010 03:06 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Pratapsz, perhaps I assumed too much knowledge in my answer; I apologize for that. A latch is a serialization mechanism, typically used to prevent concurrent access to memory structures or concurrent execution of critical code. There are many latches, each protecting a different structure. The first step in addressing your latch free wait event is to identify which latch has the problem. You need to query v$latch (or get the equivalent information from your statspack reports)
select name,gets,misses,wait_time from v$latch order by wait_time;
and only then, when we know which latch it is, can the problem be addressed. It might be a latch that has nothing to do with the library cache or freelist blocks.
If you've already done this, then I did misunderstand your question. Sorry about that.
|
|
|
|
| Re: Resolving Latch Free Waits [message #450432 is a reply to message #450352] |
Wed, 07 April 2010 03:47 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hello John
It's fine.
If we search 'latch free' on google, thousands of links appear. I visited many and none of which completely answers my queries
This is not surface talk, I have really experienced this as per as this particular wait event is considered. If somebody thinks it is wrong, please provide me with the link. It will really help me.
Many links refer statspack report (in 9i context)
While some user is complaining about performance issue and it is 'latch free', will we take statspack report in that case? Statspack would be preventive and won't be reactive action in this case.
I agree that there could be different scenario but there must be a method to diagnose the waits and resolve, which I don't know.
I would like to share one example where I faced it and resolved but this scenario isn't covered in whatever limited I have read about the wait event
One fine we were constantly facing 'latch free' waits.
I diagnosed and it turns to be a particular statement which hanged in multiple sessions causing this issue.
I traced one of the session and found that one sql statement was executing in loop and it was taking values of bind variables dynamically and when bind variable was getting NULL value the statement was stalling and the session was experiencing 'latch free' wait event.
Select x,y,z from t where c1=:b1 and c2=:b2
So there are various scenarios but there could be a method which I am looking at the moment
Regards,
prataps
|
|
|
|
| Re: Resolving Latch Free Waits [message #450440 is a reply to message #450432] |
Wed, 07 April 2010 04:51 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:I traced one of the session and found that one sql statement was executing in loop
Most of the time latch waits come from a bad designed or implemented application. Having such a statement in a loop is a sign this is the case. Anything you do in the database to workaround this is useless, you will eventually go to the same waits. The ONLY solution is to fix the application.
Regards
Michel
[Updated on: Wed, 07 April 2010 04:51] Report message to a moderator |
|
|
|
|
|
| Re: Resolving Latch Free Waits [message #450443 is a reply to message #450352] |
Wed, 07 April 2010 05:04 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Hello, again. Why don't you try to find out which latch it is? Common problem latches are the shared pool latch, the library cache latch, the cache buffers chains latch (you can have many of them), the cache buffers lru chain latch. The causes, and possible solutions, of the latch free event is totally different for each of these.
|
|
|
|
| Re: Resolving Latch Free Waits [message #450446 is a reply to message #450352] |
Wed, 07 April 2010 05:08 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
And another thing, can you post a statspack report? I don't understand your statement Quote:Statspack would be preventive and won't be reactive action in this case. It might give the diagnostic information needed.
|
|
|
|
|
|
| Re: Resolving Latch Free Waits [message #450452 is a reply to message #450352] |
Wed, 07 April 2010 05:44 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
John,
Thanks for your feedback
I am not facing the 'latch free' waits issue at the moment.
And I stated about the statspack because while a user has complained about performance issue, dba tracks his session (sometimes it is possible sometime not, with connection pooling)
and founds that the session is stalling with 'latch free' wait event.
At this fire fighting scene, I think referring statspack report will not be immediate solution since it is specific for time interval but overall generic as far as sessions are concerned
Yasir -> Thanks for the link
I am unable to access the link at the moment but will refer it at the first chance.
Thanks and Regards,
prataps
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
