Thank you Mark. Actually this is a reporting query and can be hit by
multiple/same customers from multiple sessions with each having their
session specific data available to them, and no control over how
many customer can hit same report at same time, so it would be a little
difficult to have individual GTT's created for each reporting query
submission. Also it's an existing query running for years , and I need to
see the logic , if UNION can really be replaced with UNION ALL. And also i
doubt if its the insert part of the GTT which is causing latch cache buffer
chain. It seems to be the access of the index /table TAD in that repeating
part of the code , which is causing this issue as Jonathan pointed out.
Regards
Pap
On Fri, Jun 18, 2021 at 12:01 PM Mark W. Farnham <mwf@xxxxxxxx> wrote:
When I see something like this (many UNIONs), I believe it is important to
ask whether (or not) the pieces of the UNION contain mutually exclusive
rows.
Why? Because UNION is required to do deduplication whilst UNION ALL is
not. Even if only some of the pieces are known to be mutually exclusive
(and some are not), it is usually worthwhile to produce the required UNION
deduplication on only the pieces that require mutual de-duplication and
then use UNION ALL for the mutually exclusive pieces.
Good luck. Now if you just don’t know analytically from the predicates
that the pieces are mutually exclusive, don’t take chances. Using UNION ALL
when you should have used UNION potentially produces extraneous rows in
your insert. Oracle has to do the deduplication overhead for UNION even if
it is logically impossible for the pieces to contain duplicates. You may be
able to observe from the predicates that the pieces are disjoint. Apart
from the trivial case where each piece is from a different partition and
includes the partition key or each piece has a mutually exclusive predicate
for a particular column, it can get tricky to be certain deduplication is
never needed.
Anyway, check that first.
Regarding the GTT in 11 you don’t have private GTTs, so multiple inserter
could potentially cause a kerfuffle. Oracle keeps track of which rows
“belong” to which session, but many sessions using the same one is less
friendly to the engine than each having its own. I’m not entirely sure how
that would produce cache buffers chain latch contention, but IF it is easy
to create a unique (sessionid suffixed or something like that) GTT name and
parse each query separately having its own GTT, that also might make the
problem go away. IF it is easy, that’s worth a try.
Understand that it’s worth a try because the time overhead to create a
GTT for a 1-2 minute query is pretty small and you’re only reporting 5-6
concurrent sessions. If you have a few thousand sessions that would gum up
the works creating the object in the dictionary. Then (assuming separate
GTTs are the solution) you’d need to create the GTTs once in advance and
have some sort of check-out system for which one to use. (Presumably more
programming than using the sessionid as a suffix and creating on the fly
and dropping when you’re done.)
If a session’s event IS waiting for a latch, it’s not going to be doing
much until it gets that latch.
Even if using separate GTTs makes the problem go away, I suggest you still
look that that UNION versus UNION ALL issue. Unneeded de-duplication could
be a large percentage of your 1-2 minutes when it runs serially. If that
becomes 5 or 10 seconds, your chance of concurrent sessions drops
dramatically.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Pap
*Sent:* Friday, June 18, 2021 1:46 AM
*To:* Oracle L
*Subject:* How to fix cache buffer chain issue
Hello Listers, Its version 11.2.0.4 of oracle exadata. And we are facing
an issue in which a reporting query(part of plsql procedure) which normally
finishes within ~1-2minutes runs for ~1-2hrs at times. This happens when
the same query is submitted from 5-6 multiple sessions at the same time and
is accessing the same customer data. When we kill them and rerun them in
serial they run fine without any issue and finish in the same 1-2 minutes
duration.
Few things we observed is , when all the session submitted at same time
and the query runs long , the event its showing for the session is "latch:
cache buffers chains" but active session history is not showing up any
significant activity for that session and also the sql monitor is not
getting logged for that query. Which means it's not doing significant
activity while this issue occurs but kind of stuck. Why is it so? And also
due to that , I am not able to capture the current object on which it's
actually holding that latch.
The query is an INSERT query which inserts data into a global temporary
table. It has ~17 UNION clauses of which most look similar. So i am
wondering if by someway we can rewrite this query which will help us in
fixing this issue or making the situation better?
Attached is the sample INSERT query with UNION clauses(I have removed a
few of the UNIONS to make it look simple) and its plan which suffers from
"latch: cache buffers chains".
Regards
Pap
