Contention on 'row cache objects' Latch After Upgrade from 10.2 to 220.127.116.11
(Doc ID 1485410.1)
Last updated on FEBRUARY 03, 2019
Oracle Database - Enterprise Edition - Version 18.104.22.168 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 22.214.171.124, 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':
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 126.96.36.199 .
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!