Latch row cache objects

A quick look at the session waits during the issue showed around 100 sessions on each instance waiting for latch. The row cache is an area in oracles shared pool that keeps data dictionary information in memory. The environment was a 4node rac of 64bit oracle 10. The latch events was bit by bit dominating the environment during the last months and turn on an attention alarm to us. So that was oracle database row cache lock concurrency troubleshooting using sql, ash view and systemstate dump. Contention for oracle shared pool and library cache latches. This event was rapidly escalating whether load increased quickly bringing the entire cluster into unusable state as far as any frontend application were concerned. Every few minutes or so, randomly on one or more nodes, the cpu spikes from around the regular 40% usage to 100% and the system grinds to a halt for a minute or two till normalcy returns. I think that the author means that the library cache latch contention occurs to check the parsed version of a sql statement. Increasingthe shared pool usually solves this latch problem. The purpose of this latch is to provide data protection for user processes that access the cached data dictionary area within oracle. In 12cr1, waits in awr top 10 foreground events by total wait time is close to sleeps in latch sleep breakdown.

Time consuming match for a query with several child cursors can also cause other sessions to wait on library cache latches for other queries, protected by this same latch, with 1 child cursor. A child library cache latch protects multiple objects cursors. Excessive hard parsing is common among applications that primarily use sql statements with literal values. I also had the chance of monitoring the system during issue time. Oracle database 12c release 2 support lessons learned from early adopters wrote.

Although little known, this is an extremely useful view for systematic latch contention troubleshooting as it provides one important bit of information which no other oracle view or trace provides the session id of the latch holder. Ask tom locks not getting released even after the session. Dictionary cache is one of the component of a shared pool and all the definitions, required to parse a cursor, is stored in this cache, in the form of a row, thus is protected by row cache object latches. This is because the library cache and row cache lock event are global in rac environmentcausing the row cache lock wait to be more pronounced. Takes less instructions to mutex get is about 3035 instructions latch get is 150200 instructions. Frequent concurrent hash joins cause a contention for exclusive row cache objects latches bug 902396. In the document on page no 12, the author states about the library cache latch contention that very generally and incomplete, the library cache kernel code is used to check if a sql statement is cached in the library cache. The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. Oracle database cloud schema service version na and later. Oracle latches are interesting items that can protect, and sometimes hinder, object access. The row cache lock prevents two sessions from updating the same information in the row cache simultaneously. This means, two or more entirely different queries can be hashed to a same child latch.

The row cache object latch can also indicate sql with excessive hard parsing or excessive reliance on data dictionary information such as views row level security, synonyms, etc. This is not the same thing as a latch or an enqueue, it is a state object. Row cache objects latch contention a data loading process was running on ultrasparc t2 cpu. The row cache objects latch is a correlated latch to the shared pool latch is the row cache objects latch. Us contention waits when using system managed undo with an auto tuned undo retention period. High us contention enqueue rowcache lock while trying. The row cache objects latches protect the data dictionary. Our data dictionaries is huge and has many roles and schemas. This latch is acquired whenever you are referencing metadata objects in the data dictionary cache. You learned why this latch is obtained by oracle and how to reduce the possibility that two processes will want the latch at the same time. Hi from last year we faced a recurring problem with latches in row cache objects class concurrency. Result cache latch contention bobby durretts dba blog. Whereas the other processes which are desperately requesting this latch are starving the cpu, but cant get this latch. I hope this helped you understanding and resolving cache buffer chains latch related waits.

To take advantage of the platform architecture or, id rather say, to avoid its limitations the loading process has been design to run with a massive amount of parallel query slaves in order to extract the maximum output from cmt architecture. Row cache objects latch protects the dictionary cache. A latch wait on a row cache object often means that there is a point of contention within the data dictionary. If object compiles are occurring this can require an exclusive lock which will block other activity. Most of the time, row cache updates are infrequent since data dictionary information is. Once you know the holders sid, you can focus on that session and see exactly what it is doing that it needs to hold the latch for so long. Row cache latch contention testcase latch wait elapsed time.

Andrey nikolaev oracle database performance expert rdtex russia. Each row cache lock will be on a specific data dictionary object. New versions of latchprof and latchprofx for latch. The origins are all different businesses channels that access objects through the use of vpd. Use locally managed tablespaces for your application objects especiallyindexes. This event occurs when another session is modifying the contents of part of the dictionary cache or row cache. Tune by examining invalid objects and dependencies. In addition to being able to report the sid of the latch holder and the kernel function why this latch was taken by the session, the newest latchprofx can also report the object what was protected by the latch under investigation. Create test case to reproduce the wait event and detail cause of it.

This event is often associated with high parse activity but may occur for other reasons, such as manipulation of rollback segments. I was looking into a system having serious performance issues. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on. The locks on the data dictionary rows are called row cache enqueue locks. I did an ash analysis for the reported issue time and found out that the waits were mostly for cursor. This latch comes into play when user processes are attempting to access or update the cached data dictionary values latch dictionary cache stats pct misses should be very low cache. The row cache objects latch contentionusually means there is contention in the data dictionary.

Indicates the data pieces of the object which need to be loaded. Oracle row cache lock wait event in rac environment. It is interesting to note that in the view, the owner column is not the owner of the lock, rather it is the owner of the object being locked. The sql they were executing was similar, but not same due to literal usage. Oracle needs to hold the row cache lock in 5srx mode when calling nextvalue on the nocached sequence, which means its very easy to replay the row cache. Latch protect the critical sections of the associated internal data structures from other threads. The other day, we found high occurrence of latch events in our principalcore environment 11. Of course i am telling you the end before i tell you the beginning. The row cache or data dictionary cache is a memory area in the shared pool that holds data dictionary information to reduce physical io on the data dictionary tables. The first thing was to figure out whether most of the contention was contributed by a particular row cache objects child latch. Contentions for the shared pool and library cache latches are mainly due to intense hard parsing. This problem may also be a symptom of excessive parsing ofsql statements that depend on public synonyms.

Upon a closer examination of ash data, it turned out that all slowdowns were due to latch. The enqueue lock structures are allocated from the shared pool as needed but when these requests wait and time out, we see the row cache lock wait event. The row cache objects is a serialization latch that protects the access to the data dictionary cache in the sga. I may see more than one copy of various objects here this is normal, and just means i have more than one thing im using in the shared pool that references these objects.

These latches are generated at compile command in the competition of the objects. New versions of latchprof and latchprofx for latch contention troubleshooting and tuning. Excessive latch row cache objects causing database hung. Latch free wait event is in the 10g, but specific latch waits for events to occur. Almost a year back, i was involved in a major performance issue caused due to latch. Create a sequence object with nocache attribute and let two sessions invoke the sequence.