Applies to:Oracle Server - Enterprise Edition - Version 220.127.116.11 and later
Information in this document applies to any platform.
- AWR Report shows contention for 'row cache objects' as the top wait:
- After the database upgrade to 18.104.22.168, 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 22.214.171.124 .
The cause is due to architectural changes between 10.2 and 11.2 in how Oracle does optimization. There are lot more query transformation in 126.96.36.199 than in 10.2.0.4 for queries with views or sub-queries. This results in more passes through optimization, which means more row cache latch calls.
(Furthermore, in this particular case the parameter optimizer_mode = first_rows_100 was being used increasing the number of optimization passes still further, since In 11.2, with first_rows optimization, the optimizer goes through more permutations at hard parse time to get the most optimal execution plan possible. In addition, this particular environment is doing 350 hard parses per second because the application did not use bind variables.)
There are actually several solutions/workarounds. The list is in order of preference from Support's perspective.
Here are some example metrics on the batch job in question before and after some of the changes: