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

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