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

Thanks for your answer.

I responded inline:

> How are you working out the connection between the row cache latches
> and this statement ? ASH or an ordinary SQL_TRACE ?
I used ASH:

    select ash.sql_id as sqlid, count(*) as num_waits     from v$active_session_history ash
    where event = 'latch: row cache objects'     group by ash.sql_id
    having count(*) > 100
    order by count(*) desc;

> If you have some detailed information about the wait you can check
> the p1, p2 parameters for the wait to determine exactly which latches
> are being acquired most frequently.

p1text is 'address' and p1 contains two different values (21532996456 and 21728500976 if it matters) p2text is always 'number' with p2 = 411
p3text is always 'tries' with p3 = 0  

> 1) If the code does one or two inserts, does something else, then
> commits, then starts the next couple of inserts the session will be
> re-allocating the temporary segment on each little batch of inserts.
> This would be consistent with the pattern of latching you've
> reported.

Yes, that's the process as far as I understand (I am not the developer of the code)

> 2) Is there a mismatch between the character set of the client and
> the characterset of the server
No.

> 3) Does the client issue a parse call on every execution

It uses a Java PreparedStatement, so I guess that's a "soft parse"

> 4) Is the client connected as the owner of the table GTT, or is there
> a public synonym for the table with the client connected to a schema
> other than the table owner

It's connected as the owner of the GTT  

> 5) Are you using a temporary tablespace group rather than a simple temporary tablespace

Simple temporary tablespace.

> 6) Do different client connections log in to different schemas, and if so are different tablespaces associated different schemas

Yes, there are multiple instances of the application (it's test environment). Every application instance uses its own DB user, but all use the same tablespace.

Regards
Thomas

> On Thu, 1 Oct 2020 at 09:04, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com <mailto:thomas.kellerer@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
>
>

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