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@xxxxxxxxxx>
wrote:
Thanks for your answer.
I responded inline:
How are you working out the connection between the row cache latchesI used ASH:
and this statement ? ASH or an ordinary SQL_TRACE ?
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
