Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand the row cache lock waiting event in the database". In the daily operation, I believe that many people have doubts about how to understand the row cache lock waiting event in the database. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubt of "how to understand the row cache lock waiting event in the database". Next, please follow the editor to study!

1. Basic description of ROW CACHE LOCK

The ROW CACHE LOCK wait event is a shared pool-related wait event. This is due to access to dictionary buffers. Each row buffer queue lock corresponds to a specific data dictionary object, which is called the queue lock type and can be found in the V$ROWCACHE view. In AWR, you need to check the Dictionary Cache Stats section to determine the problem.

Parameter

P1 = cache-ID of the dictionary cache

P2 = mode-Mode held

P3 = request-Mode requested

Mode-Mode held

The mode the lock is currently held in: KQRMNULL 0 null mode-not locked KQRMS 3 share mode KQRMX 5 exclusive mode KQRMFAIL 10 fail to acquire instance lock

Request-Mode requested

The mode the lock is requested in: KQRMNULL 0 null mode-not locked KQRMS 3 share mode KQRMX 5 exclusive mode KQRMFAIL 10 fail to acquire instance lock

two。 Cause

(1)。 If the wait is found to be very high, generally speaking, it may be due to two reasons: one is that the shared pool is too small and needs to be increased.

Check to see if there is still free space in share pool

(2)。 Another situation is that SQL parse is too frequent and there are too many concurrent visits to the shared pool.

3. Analysis process

(1)。 Confirm if there is any free space in share pool

Select * from v$sgastat where pool='shared pool' and name like 'free memory'

(2)。 Query the reason why row cache lock is waiting

-get cache id

# View the current session waiting information

Select * from v$session_wait where wait_class = 'row cache lock'

# View the history of ASH waiting information

SELECT event,p1,p1text FROM v$active_session_history ash WHERE ash.sample_time > '22-JUN-16 14.00.00.00 PM' AND ash.sample_time <' 22-JUN-16 16.00.00.00 PM' and event='row cache lock'

# query the name of rowcache and find different solutions according to different cache needs

Select * from v$rowcache where cache# = & p1

# query AWR Dictionary Cache Stats

4. The meaning of different cache

① DC_SEQUENCES: a buffer queue lock for that line occurs when a sequence is used. The tuning method is to check whether the sequence specifies a buffer option and to determine that the buffer value can withstand the expected concurrent insert operations.

② DC_USED_EXTENTS and DC_FREE_EXTENTS: this buffer queue lock can occur when space management encounters a table space split or does not have sufficient extents. The tuning method is to check whether the tablespace is split, whether the extent size is too small, or whether the tablespace is managed manually.

③ DC_TABLESPACES: the buffer queue lock of the row occurs when the new area is allocated. If the area size is set too small, the program will often apply for new areas, which will lead to conflicts. The tuning method is to quickly increase the number of zones.

④ DC_OBJECTS: this buffer queue lock occurs when the object is recompiled. An exclusive lock is applied to block other behaviors when the object is compiled. Tune by checking for illegal objects and dependencies.

⑤ DC_SEGMENTS: the buffer queue lock of this line occurs when the segment is allocated, and observe what the session holding the queue lock is doing.

⑥ DC_USERS: a session is GRANT a user while the user is logging in to the database, which may cause a deadlock or cause "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!".

⑦ DB_ROLLBACK_SEGMENTS: this may be due to the allocation of rollback segments. As with dc_segments, determine who holds the lock and collect errorstack for diagnostics. Keep in mind that on a multi-node system (RAC), the holder may be on another node, so the systemstate of all nodes is required.

⑧ DC_AWR_CONTROL: this enqueue is related to control of AWR (Automatic Workload Repository). Any operation that manipulates the AWR database will hold it. To analyze this problem, you need to find out which processes are blocking them.

At this point, the study on "how to understand the row cache lock waiting event in the database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.