row cache lock (ZT)

http://surachartopun.com/2009/11/investigate-row-cache-lock.html

Investigate ROW CACHE LOCK

My Oracle RAC had the problem, so I investigated... I found:

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

------------------------- ------------ ----------- ------ ------ ----------

row cache lock 509,761 1,259,315 2470 71.8 Concurrenc

ROW CACHE LOCK (DDL statements require "row cache lock" and session will wait "row cache lock" to lock the data dictionary)

And then check V$SESSION

SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock';

P1TEXT P1 P2TEXT P2 P3TEXT P3

-------------- --------- -------------- --------- -------------- ----------

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

cache id 13 mode 0 request 5

What I see? I need "cache id" to find on V$ROWCACHE (to check enqueue type)

Example: Enqueue Type

DC_TABLESPACES

Probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.

DC_SEQUENCES

Check for appropriate caching of sequences for the application requirements.

DC_USERS

Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.

DC_OBJECTS

Look for any object compilation activity which might require an exclusive lock and thus block online activity.

DC_SEGMENTS

This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.

To check on V$ROWCACHE

SQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=13;

PARAMETER COUNT GETS GETMISSES MODIFICATIONS

--------------------------- ---------- ---------- ---------- -------------

dc_sequences 13 746449 210320 746449

SQL> column pct_succ_gets format 999.9

SQL> column updates format 999,999,999

SQL> SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;

PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES

---------------- ---------- -------------- ------------- ------------

dc_constraints 4288 1455 66.1 4,288

dc_object_ids 8161040 118929 98.5 5,482

outstanding_alerts 2737095 2722712 .5 200

dc_awr_control 31108 526 98.3 457

dc_objects 21160173 145159 99.3 266,627

dc_usernames 9387743 1349 100.0 0

dc_table_scns 4658 4658 .0 0

dc_users 94113064 2174 100.0 16

dc_histogram_defs 7702201 783888 89.8 97,472

kqlsubheap_object 153 36 76.5 0

dc_profiles 1266752 13 100.0 0

dc_object_grants 44530796 7547 100.0 0

dc_histogram_data 2431665 377265 84.5 147,793

dc_segments 1658801 347470 79.1 17,402

dc_files 67249 6477 90.4 0

dc_sequences 748386 210954 71.8 748,386

dc_database_links 2552640 89 100.0 0

dc_global_oids 850330 3091 99.6 0

global database name 175908 174 99.9 0

dc_tablespaces 42863352 137296 99.7 0

dc_tablespace_quotas 14299 5277 63.1 576

dc_rollback_segments 149604805 2673 100.0 949

After check at v$rowcache, my point to SEQUENCE (DC_SEQUENCES). then I check ALL_SEQUENCES and then investigate how to solve...

SQL> select * from all_sequences order by last_number;

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

------------ ---------- ---------- -------- ------------ - - ------- ---------

APPUSER SEQ_XYZ 1 1.0000E+27 1 N N 0 34015364

This case, I solved it by "alter sequence ... cache ..."