RE: Excessive "latch: row cache objects" waits with simple INSERT statement
Date: Mon, 5 Oct 2020 08:11:47 +0000
Message-ID: <DB7PR04MB44434EEB1A1B55ECB639843CA10C0_at_DB7PR04MB4443.eurprd04.prod.outlook.com>
ASH is sampled data of active sessions so you’re going to tend to only capture a fraction of the waits. AWR report number should map to DBA_HIST_SYSTEM_EVENT.
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
From: Thomas Kellerer<mailto:thomas.kellerer_at_mgm-tp.com>
Sent: 05 October 2020 09:00
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Re: Excessive "latch: row cache objects" waits with simple INSERT statement
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
-- https://eur04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=02%7C01%7C%7C27e2fb9ea7714931724108d86904a86e%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637374816013893735&sdata=qTxmumtjHwkfAkzHFIb22jolBsBxS8V4kslVtak%2Bz%2Bo%3D&reserved=0 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 05 2020 - 10:11:47 CEST