Re: Excessive "latch: row cache objects" waits with simple INSERT statement
Date: Thu, 1 Oct 2020 10:23:00 +0200
Message-ID: <b85f99b9-2bb2-a259-0b31-ea065524f4fa_at_mgm-tp.com>
Thomas Kellerer schrieb am 01.10.2020 um 10:03:
In case this is important:
The "Latch Miss Sources" section for that wait event contains:
And "Latch Sleep Breakdown" shows:
> 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).
>
+-------------------+-----------------------+---------------+--------+---------------+
....
| 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 |
+-------------------+-----------------------+---------------+--------+---------------+
+-------------------------------+--------------+-----------+---------+-----------+
| 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-lReceived on Thu Oct 01 2020 - 10:23:00 CEST