Re: Excessive "latch: row cache objects" waits with simple INSERT statement
- From: Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 2 Oct 2020 13:38:45 +0200
/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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
