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: @OracleSK
Thomas Kellerer <thomas.kellerer@xxxxxxxxxx> 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 environmentsIn case this is important:
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).
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
