Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Krishnaprasad.

Asked: February 22, 2018 - 7:09 am UTC

Last updated: February 25, 2018 - 7:54 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Team,

when latch contention is seen in database , how to identify type of latch and how to proceed for further troubleshooting?

Additionally , if we want find latch type from ash , how to figure it from ASH , i see P1,P@ value are provided but only latch free wait events are seen , how to identify which latch is was this ??


Request you to provide some light on this and some hand holding .





and Connor said...

First thing to look at is:

Is "latch free" a dominant part of your overall response time. If my application takes 5 seconds to respond, and 0.01 seconds of that is "latch free", then why waste time looking there.

Lets assume it *is* a dominant part. If you are waiting for latch free, the someone else is *holding* that a latch that you want. So the first two things to do:

1) what latch are you waiting for ?
2) who is holding it

For (1), check your AWR reports. There's a section which shows each latch, and how many gets/misses/sleeps are occurring. The most common ones I see are "shared pool" and "library cache", which is typically indicative of excessive parsing in queries.

For (2), there is a view v$latchholder. You can sample this with Tanel's excellent script

https://blog.tanelpoder.com/files/scripts/latchprof.sql

to deduce *who* is taking those latches so aggressively.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krishnaprasad Yadav, February 24, 2018 - 4:34 pm UTC

Hi ,

thanks for responding.

If I want to find latch details after issue , let say from ash or awr want to find then from ash/awr how we can get this ???
Connor McDonald
February 25, 2018 - 7:54 am UTC

After the event, look at the latch section in the AWR report. High sleeps will indicate that session waited on this latch.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database