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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 1 Oct 2020 15:51:54 -0400
Message-ID: <00fc01d6982c$50dfde40$f29f9ac0$_at_rsiz.com>



I don’t know whether it is worth the trouble or space, but in some cases that can be mitigated by creating a temporary tablespace for that singular user. This includes the possibility of creating a user (or multiple users and temporary tablespaces) with the correct access to execute the specific troublesome bits whilst not competing with the existing user that might need to execute additional processes using the original temp tablespace concurrently.  

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-l
Received on Thu Oct 01 2020 - 21:51:54 CEST

Original text of this message