Re: Excessive "latch: row cache objects" waits with simple INSERT statement
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 5 Oct 2020 09:55:34 +0200
Thomas Kellerer schrieb am 01.10.2020 um 10:03:
Hello,
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).
insert into lookup_table (lookup_value) values (:1 );
The table definition is pretty simple:
CREATE GLOBAL TEMPORARY TABLE LOOKUP_TABLE
( "LOOKUP_VALUE" VARCHAR2(255 CHAR),
PRIMARY KEY ("LOOKUP_VALUE") ENABLE
) ON COMMIT DELETE ROWS;
The temp table is used later in a different statement as a
replacement for a large IN list. But the query _using_ the temp table
never suffers from those wait events.
Another strange thing I noticed.
The AWR report for today (Begin Snap: 05-Oct-20 08:00:02, End Snap: 05-Oct-20
09:00:09) shows the following:
| Event | Waits | Total Wait Time (sec) | Wait
Avg(ms) | % DB time | Wait Class |
|-------------------------------|---------|-----------------------|--------------|-----------|---------------|
| latch: row cache objects | 93,638 | 1873.1 |
20.00 | 42.5 | Concurrency |
| latch: shared pool | 29,588 | 807.2 |
27.28 | 18.3 | Concurrency |
| DB CPU | | 686.8 |
| 15.6 | |
| SecureFile mutex | 10,050 | 414 |
41.20 | 9.4 | Concurrency |
| latch free | 20,775 | 227.2 |
10.94 | 5.2 | Other |
| enq: TX - row lock contention | 388 | 105.4 |
271.59 | 2.4 | Application |
| log file sync | 214,592 | 72 |
0.34 | 1.6 | Commit |
| library cache: mutex X | 5,356 | 37.9 |
7.08 | .9 | Concurrency |
| enq: SQ - contention | 995 | 30.8 |
30.99 | .7 | Configuration |
| enq: TX - index contention | 343 | 19.8 |
57.61 | .4 | Concurrency |
However, my query I use to check the wait event directly:
select count(*) as num_waits
from v$active_session_history ash
where event = 'latch: row cache objects'
and sample_time >= timestamp '2020-10-05 08:00:00'
and sample_time <= timestamp '2020-10-05 09:00:09';
only returns 2032.
So apparently my query looking a v$active_session_history counts something else
than the AWR report.
Can someone point me to the correct dynamic view that would also return 93638
as the number of waits
related to 'latch: row cache objects'?
Thomas
--
//www.freelists.org/webpage/oracle-l
Other related posts:
