Hi All,
DB version is 10.2.0.4
In AWR report top 5 wait events i could see ' row cache lock' wait event. Searched in google and got the below
SQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock';
P1TEXT P1 P2TEXT P2 P3TEXT P3
-------------------- ---------- -------------------- ---------- ---------------------------------------------------------------- ----------
cache id 16 mode 0 request 3
1 row selected.
SQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=16;
PARAMETER COUNT GETS GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- ---------- -------------
dc_histogram_defs 1704 47481213 11479508 5408
dc_histogram_data 218 8211149 1277649 0
dc_histogram_data 83 2668657 301607 0
3 rows selected.
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 7468 4954 33.6636315 7468
dc_free_extents 578 445 23.0103806 178
dc_tablespaces 82731475 12753 99.9845851 0
dc_tablespace_quotas 41635 2672 93.5823226 110
dc_awr_control 1774 856 51.7474634 30
dc_object_grants 16804751 315710 98.1213051 0
dc_histogram_data 10893968 1582368 85.4748242 0
dc_rollback_segments 4721434 934 99.9802179 606
dc_sequences 123169 46953 61.8792066 123169
dc_usernames 12046759 82141 99.3181486 0
dc_segments 43854408 3208602 92.6835131 32483
dc_objects 19096430 2728645 85.7112298 6698
dc_database_links 231247 1383 99.4019382 0
dc_histogram_defs 47588238 11511573 75.8100458 5408
dc_table_scns 77108 12028 84.4010998 128
dc_used_extents 71 45 36.6197183 71
dc_users 132473881 75854 99.9427404 0
outstanding_alerts 8387 7779 7.24931442 29
dc_files 2904 1072 63.0853994 0
dc_object_ids 277958942 3175244 98.8576572 831
dc_global_oids 1683020 46540 97.2347328 0
kqlsubheap_object 77217 3211 95.8415893 0
dc_profiles 150822 3056 97.9737704 0
global database name 22 7 68.1818182 0
24 rows selected.
Source: http://surachartopun.com/2009/11/investigate-row-cache-lock.html
How do i resolve this row cache lock on dc_histogram_defs and dc_histogram_data??
baskar.l