Latch row cache objects

Oracle row cache lock wait event in rac environment. Frequent concurrent hash joins cause a contention for exclusive row cache objects latches bug 902396. This event was rapidly escalating whether load increased quickly bringing the entire cluster into unusable state as far as any frontend application were concerned. Us contention waits when using system managed undo with an auto tuned undo retention period. The first thing was to figure out whether most of the contention was contributed by a particular row cache objects child latch. Most of the time, row cache updates are infrequent since data dictionary information is. The row cache objects is a serialization latch that protects the access to the data dictionary cache in the sga. 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. I also had the chance of monitoring the system during issue time. I was looking into a system having serious performance issues. I hope this helped you understanding and resolving cache buffer chains latch related waits. The locks on the data dictionary rows are called row cache enqueue locks.

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. The row cache objects latch is a correlated latch to the shared pool latch is the row cache objects latch. This is not the same thing as a latch or an enqueue, it is a state object. Use locally managed tablespaces for your application objects especiallyindexes. This latch is acquired whenever you are referencing metadata objects in the data dictionary cache. Create test case to reproduce the wait event and detail cause of it. Upon a closer examination of ash data, it turned out that all slowdowns were due to latch. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on. The latch events was bit by bit dominating the environment during the last months and turn on an attention alarm to us.

Our data dictionaries is huge and has many roles and schemas. High us contention enqueue rowcache lock while trying. 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. This means, two or more entirely different queries can be hashed to a same child latch. Almost a year back, i was involved in a major performance issue caused due to latch. This problem may also be a symptom of excessive parsing ofsql statements that depend on public synonyms. This event is often associated with high parse activity but may occur for other reasons, such as manipulation of rollback segments. New versions of latchprof and latchprofx for latch. Contention for oracle shared pool and library cache latches. The row cache lock wait event is associated with a specific enqueue type on a data dictionary row. 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 sql they were executing was similar, but not same due to literal usage. 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.

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. 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 objects latch protects the dictionary cache. I did an ash analysis for the reported issue time and found out that the waits were mostly for cursor. Oracle database 12c release 2 support lessons learned from early adopters wrote. The purpose of this latch is to provide data protection for user processes that access the cached data dictionary area within oracle. These latches are generated at compile command in the competition of the objects. 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. 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. Each row cache lock will be on a specific data dictionary object. Latch protect the critical sections of the associated internal data structures from other threads. Oracle database cloud schema service version na and later. 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.

A child library cache latch protects multiple objects cursors. Latch free wait event is in the 10g, but specific latch waits for events to occur. Row cache latch contention testcase latch wait elapsed time. 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. So that was oracle database row cache lock concurrency troubleshooting using sql, ash view and systemstate dump. Hi from last year we faced a recurring problem with latches in row cache objects class concurrency. A latch wait on a row cache object often means that there is a point of contention within the data dictionary. Andrey nikolaev oracle database performance expert rdtex russia. The origins are all different businesses channels that access objects through the use of vpd. The row cache is an area in oracles shared pool that keeps data dictionary information in memory. 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. 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. 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.

Of course i am telling you the end before i tell you the beginning. Whereas the other processes which are desperately requesting this latch are starving the cpu, but cant get this latch. The row cache objects latch contentionusually means there is contention in the data dictionary. The row cache lock prevents two sessions from updating the same information in the row cache simultaneously. Takes less instructions to mutex get is about 3035 instructions latch get is 150200 instructions.

Ask tom locks not getting released even after the session. If object compiles are occurring this can require an exclusive lock which will block other activity. The other day, we found high occurrence of latch events in our principalcore environment 11. I saw more often row cache lock issues on oracle 11. In 12cr1, waits in awr top 10 foreground events by total wait time is close to sleeps in latch sleep breakdown. Contentions for the shared pool and library cache latches are mainly due to intense hard parsing.

New versions of latchprof and latchprofx for latch contention troubleshooting and tuning. Oracle latches are interesting items that can protect, and sometimes hinder, object access. Tune by examining invalid objects and dependencies. Excessive latch row cache objects causing database hung. Result cache latch contention bobby durretts dba blog. A quick look at the session waits during the issue showed around 100 sessions on each instance waiting for latch. I recently saw a dramatic example of result cache latch contention. Row cache objects latch contention a data loading process was running on ultrasparc t2 cpu. This event occurs when another session is modifying the contents of part of the dictionary cache or row cache.

The row cache objects latches protect the data dictionary. Increasingthe shared pool usually solves this latch problem. 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. Indicates the data pieces of the object which need to be loaded. 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 environment was a 4node rac of 64bit oracle 10. Excessive hard parsing is common among applications that primarily use sql statements with literal values.