RE: Excessive "latch: row cache objects" waits with simple INSERT statement
Date: Thu, 1 Oct 2020 15:51:54 -0400
Message-ID: <00fc01d6982c$50dfde40$f29f9ac0$_at_rsiz.com>
This method of avoiding latch contention is often beat with a ball peen hammer into a shape useful for a particular concurrent workload that is not otherwise manageable. Maybe “often” is stretching it.
The more general method of simply having more than one temp tablespace is a question of whether it is worth it in the extra vacant space that is often not time-shareable because of the distinct objects. This is askew from groups. Logical workload arrangement is often preferable. And I didn’t get into RAC considerations, if any. The complications of managing Joe_user_n1…Joe_user_nm with force query local and m+1 temporary tablespaces with m each only actually used on a specific instance are not usually worth the trouble, let alone 2m+1 where you have two classes of storage media (such as fast SSD and slower SSD). But the world can be a complicated place. Setting it up so Oracle is shooting fish in a barrel is sometimes the answer. (And that’s all “legal” it’s just more complicated than it should have to be in the world of “automated.”)
Good luck. YMMV.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, October 01, 2020 6:39 AM
To: Oracle L
Subject: 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-lReceived on Thu Oct 01 2020 - 21:51:54 CEST