How to fix cache buffer chain issue
Date: Fri, 18 Jun 2021 11:16:21 +0530
Message-ID: <CAEjw_fimvZC9ZSXvY1FRp=7qM7STktsiBsapVocAh8OGftu7_w_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 18 2021 - 07:46:21 CEST
- text/plain attachment: list_question_cache_buffer_chain.txt