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

*No, I just left out the owner in my initial post. The query recorded in V$SQL is: *

• insert into USER_ONE.lookup_table (lookup_value) values (:1 );*
• And yes the generated SELECT statements also prefix the owner. *
• But the statement is run as USER_ONE, so there shouldn't be a difference between *
• insert into USER_ONE.lookup_table (lookup_value) values (:1 );*
• and *
• insert into lookup_table (lookup_value) values (:1 );*

You might think that there shouldn't be - the insert statements, for example, vary with user and so are likely to be scattered across all library cache latches. But the table name is the same, so every table definition might be in the same dc_objects dictionary cache bucket so they're all covered by one specific row cache latch (conjecture, not necessarily correct); something similar may also apply to the library cache where "denormalized" versions of dictionary cache.objects are also stored.

*But in the end, I guess having different temp tablespaces for the users isn't really that much different than having different temp tablespaces for those two tables with regards to the problem at hand, or am I missing something? *

Hard to say. Moving the allocations and de-allocations of this table away from everything else affecting the default temp tablespace may well be a move in the right direction, but if a lot of the "everything else" is being done by those two users/schemas anyway then it might not make much difference and it might be that moving this table away from everything else done by the schema is the more effective strategy. (By "everything else" that mostly means hash joins or sort spilling to disc and allocating / deallocating temp space).

Have I said anything yet about creating a logon trigger (on one of your schemas) to enable SQL tracing with waits events enabled for just the SQL_ID of the critical statement - perhaps with a limited lifetime so you only capture a few occurrences of the statement.

Regards
Jonathan Lewis

On Fri, 2 Oct 2020 at 12:39, Thomas Kellerer <thomas.kellerer_at_mgm-tp.com> wrote:

> > /The statement is generated by Hibernate and the table is automatically
> prefixed with current user./
> >
> > Does that mean the insert statement you presented us with initially
> > is nothing like the actual insert statement that's causing the
> > problem ?
> No, I just left out the owner in my initial post. The query recorded in
> V$SQL is:
>
> insert into USER_ONE.lookup_table (lookup_value) values (:1 );
>
> And yes the generated SELECT statements also prefix the owner.
>
> But the statement is run as USER_ONE, so there shouldn't be a difference
> between
>
> insert into USER_ONE.lookup_table (lookup_value) values (:1 );
>
> and
>
> insert into lookup_table (lookup_value) values (:1 );
>
> (as no public synonym with that name exists)
>
>
> > Lots of schemas using their own declarations of "the same" global
> > temporary table is likely to show completely different
> > characteristics from lost of sessions using the same GTT declaration
> > from a single schema. Does the subsequent select statement also use
> > the schema name to qualify the GTT name ?
> Each user defines its own GTT.
> So its user_one.lookup_table, user_two.lookup_table and so on.
>
>
> > You can't MOVE something that doesn't exist. the CREATE statement
> > will have to modified to specify a tablespace and then the table will
> > need to be dropped and recreated. See the second bullett point in the
> > following. This has been possible since 11g
> > https://jonathanlewis.wordpress.com/2014/03/14/12c-temporary/
>
> Ah, I see. Thanks for pointing that out.
>
> But in the end, I guess having different temp tablespaces for the users
> isn't really that much different than having different temp tablespaces for
> those two tables with regards to the problem at hand, or am I missing
> something?
>
> > How many different schemas have created their private version of this
> GTT ?
>
> About 15, but the waits only happen on two of them (but those are the two
> most active ones as we run performance tests on those)
>
> Regards
> Thomas
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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