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
