Contention on 'row cache objects' Latch After Upgrade from 10.2 to 22.214.171.124
Last updated on APRIL 11, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
- AWR Report shows contention for 'row cache objects' as the top wait:
Top 5 Timed Foreground Events Event Waits Time(s) Avg wait (ms) DB time Wait Class latch: row cache objects 58,291,873 130,700 2 63.18 Concurrency DB CPU 60,563 29.27 db file sequential read 2,994,299 14,461 5 6.99 User I/O library cache lock 3,179 1,662 523 0.80 Concurrency gc cr grant 2-way 1,233,503 981 1 0.47 Cluster
- After the database upgrade to 188.8.131.52, there is dramatic increase in the waits for the 'row cache objects' latch. Looking at the 'Latch Miss Sources' section in the AWR Report, the top location calling 'row cache objects' is 'kqrpre: find obj':
Latch Name Where NoWait Misses Sleeps Waiter Sleeps ... row cache objects kqrpre: find obj 0 10,332,326 8,906,124 row cache objects kqreqd: reget 0 7,888,165 8,503,625 row cache objects kqreqd 0 7,677,074 8,489,832 row cache objects kqrso 0 4,127 3,622 row cache objects kqrpre: init complete 0 143 88
The 'row cache objects' latch is called most frequently from 'kqrpre: find obj'. This module tries to find details of an object being parsed in the row cache. During parse, the row cache is searched. The process searches through a linked list protected by the 'row cache objects' latch to find the object in the cache that it needs. When other processes are also parsing and looking through the row cache this may cause contention; especially if the parse activity is excessive or unnecessary.
- From the system state trace, a common stack can bee seen for the related processes:
Most of the time in the stack appears to be spent in the function KKOIQB. This function makes row cache calls in order to find index names so that indexes for a table can be sorted by alphabetical name order. In the case of a cost tie between different plans the optimizer needs a consistent method of choosing one index over another. The index names is sorted alphabetically to achieve this; in other words, it picks the first index alphabetically in the event of a choice between two plans using indexes with equal costs.
The database was upgraded from 10.2.0.4 to 184.108.40.206 .
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms