Re: Excessive "latch: row cache objects" waits with simple INSERT statement

>
> Any ideas on what I could investigate further to find the root cause?

If you can isolate this, do stack sampling - either through /proc/PID/stack directly or via process snapper
Usually stack traces give a nice clue of what's going on. With psn it is possible to sample more than one process so can find something with a brute force

On Thu, Oct 1, 2020 at 11:04 AM Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> wrote:

> 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.
>
> The dictionary stats from the AWR report show this:
>
>
> +----------------------+--------------+----------+-----------+----------+----------+-------------+
> | Cache | Get Requests | Pct Miss | Scan Reqs | Pct Miss |
> Mod Reqs | Final Usage |
>
> +----------------------+--------------+----------+-----------+----------+----------+-------------+
> | dc_awr_control | 78 | 0.00 | 0 | |
> 2 | 1 |
> | dc_files | 64 | 0.00 | 0 | |
> 0 | 4 |
> | dc_global_oids | 9,085 | 0.00 | 0 | |
> 0 | 439 |
> | dc_histogram_data | 492,981 | 0.05 | 0 | |
> 0 | 6,631 |
> | dc_histogram_defs | 740,310 | 0.05 | 0 | |
> 353 | 10,124 |
> | dc_objects | 541,488 | 0.06 | 0 | |
> 240 | 4,697 |
> | dc_profiles | 769 | 0.00 | 0 | |
> 0 | 2 |
> | dc_props | 3,829 | 0.00 | 0 | |
> 0 | 6 |
> | dc_rollback_segments | 16,087 | 0.00 | 0 | |
> 0 | 303 |
> | dc_segments | 248,107 | 0.03 | 0 | |
> 28 | 4,664 |
> | dc_sequences | 2,249 | 0.09 | 0 | |
> 2,249 | 15 |
> | dc_tablespaces | 548,284 | 0.00 | 0 | |
> 0 | 13 |
> | dc_users | 817,470 | 0.00 | 1,780 | 0.00 |
> 0 | 415 |
> | outstanding_alerts | 8 | 0.00 | 0 | |
> 0 | 3 |
> | sch_lj_oids | 164 | 3.66 | 0 | |
> 0 | 36 |
>
> +----------------------+--------------+----------+-----------+----------+----------+-------------+
>
> I have found references in MOS that "latch: row cache objects" might be
> related to the DB user having system privileges. The user did have the
> SELECT_CATALOG_ROLE role granted, so I revoked that but to no avail.
>
> MOS also contains documents that state that this could happen with VPD
> enabled, which we don't have. But I wonder if a global temp table uses that
> somehow to isolate the content of the table between sessions.
>
> The server has 32 CPUs and the AWR report shows a load of 5 seconds DB
> Time per second (so it's not CPU bound), 37 transactions/s and 360
> executes/s
>
> I don't have any ideas on how to investigate this further.
>
> Any ideas on what I could investigate further to find the root cause?
>
>
> Thanks
> Thomas
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards
Timur Akhmadeev

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