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

With two addresses for latch# = 411 that will be the child latches for dc_tablespaces and dc_users.
(It's in Oracle Core somewhere, but at the moment I can't remember how to connect (to prove) that your two child latch addresses correspond to those two dictionary cache entries.)

That's what I would expect for a very simple process of allocating and releasing a global temp table. I'm not sure you can do anything about that unless there's a known, fixed, bug in 12.1 that says something about the latches being held for too long during temp table space searches.

The rest of the high numbers for latch gets in the AWR report shouldn't be directly connected to the GTT, they look more like dictionary lookups for optimising statements (or maybe something to do with gathering stats - the code doesn't populate the gtt than try to gather stats on it, does it?"

Regards
Jonathan Lewis

the
On Thu, 1 Oct 2020 at 10:30, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> wrote:

> 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
>
>
>

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