Re: Excessive "latch: row cache objects" waits with simple INSERT statement

Hello Thomas,
you need to find out which code (part) is responsible for all these row cache objects gets.

Profile (e.g. with perf on Linux or something similar depending on your OS platform) the process while running the INSERT and have a look at the C call stack to find the cause of these extensive latch gets.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> hat am 01.10.2020 10:23 geschrieben:
>
>
> Thomas Kellerer schrieb am 01.10.2020 um 10:03:
> > we have a strange performance problem in one of our test environments
> > running Oracle 12.1 (to be upgraded soon, but not soon enough to
> > ignore the problem).
> >
> > A simple INSERT into a global temp table causes excessive "latch: row
> > cache objects" wait events (contributing > 80% of the DB time in the
> > AWR report).
> >
> In case this is important:
>
> The "Latch Miss Sources" section for that wait event contains:
>
> +-------------------+-----------------------+---------------+--------+---------------+
> | Latch Name | Where | NoWait Misses | Sleeps | Waiter Sleeps |
> +-------------------+-----------------------+---------------+--------+---------------+
> ....
> | row cache objects | kqreqd: reget | 0 | 98,844 | 11,039 |
> | row cache objects | kqrpre: find obj | 0 | 60,075 | 126,005 |
> | row cache objects | kqreqd | 0 | 9,128 | 29,079 |
> | row cache objects | kqrso | 0 | 5,336 | 7,262 |
> | row cache objects | kqrcmt: while loop | 0 | 28 | 7 |
> | row cache objects | kqrssc: tell ksm | 0 | 17 | 3 |
> | row cache objects | kqrpup | 0 | 11 | 5 |
> | row cache objects | kqrpre: init complete | 0 | 4 | 23 |
> | row cache objects | kqrsrd | 0 | 2 | 23 |
> ....
> +-------------------+-----------------------+---------------+--------+---------------+
>
> And "Latch Sleep Breakdown" shows:
>
> +-------------------------------+--------------+-----------+---------+-----------+
> | Latch Name | Get Requests | Misses | Sleeps | Spin Gets |
> +-------------------------------+--------------+-----------+---------+-----------+
> | row cache objects | 9,282,696 | 1,580,897 | 173,709 | 1,423,975 |
> | shared pool | 8,185,650 | 539,190 | 54,845 | 486,003 |
> | transaction branch allocation | 2,868,903 | 75,766 | 344 | 75,428 |
> | Result Cache: RC Latch | 70,114 | 24,297 | 26,232 | 1,086 |
> | enqueue hash chains | 8,111,968 | 21,217 | 982 | 20,296 |
> | redo allocation | 592,058 | 19,462 | 204 | 19,260 |
> .....
>
>
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l