Re: Excessive "latch: row cache objects" waits with simple INSERT statement
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 1 Oct 2020 10:23:00 +0200
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 |
.....
--
//www.freelists.org/webpage/oracle-l
Other related posts:
