Re: How to fix cache buffer chain issue

Pap, as a matter of principle, your SQL is too big. I have had a myriad of problems with gigantic SQL statements like yours. You are inserting into GTT using a statement with all kinds of hash group by, sorting and full table scans. Not even Exadata can help you with that. I would try slicing and dicing the SQL using CTE and /*+ MATERIALIZE */ hint.

Regards

On 6/18/21 1:46 AM, Pap wrote:
> 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

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l