*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@xxxxxxxxxx>
wrote:
/The statement is generated by Hibernate and the table is automaticallyprefixed with current user./
No, I just left out the owner in my initial post. The query recorded in
Does that mean the insert statement you presented us with initially
is nothing like the actual insert statement that's causing the
problem ?
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" globalEach user defines its own GTT.
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 ?
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 thisGTT ?
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
