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

--
http://www.freelists.org/webpage/oracle-l