I would think twice about materialize especially on Exadata when you can often
query the data twice (or more) much quicker than the consequences of writing
and reading back from temp.
Sent from my iPhone
On 20 Jun 2021, at 18:41, Mladen Gogala <gogala.mladen@xxxxxxxxx> wrote:
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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdbwhisperer.wordpress.com%2F&data=04%7C01%7C%7C49c907d03cf14b8ff8df08d93412aa17%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637598077036369290%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=hgONyg%2FIkfVaGCwg3htYSEXTZlrYSBS3Bbz7TUO8j5k%3D&reserved=0
--
https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=04%7C01%7C%7C49c907d03cf14b8ff8df08d93412aa17%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637598077036369290%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=dAYarKUDC4QON%2F3eDiwvMaDyIEhRdBG9YYMabWLoV7c%3D&reserved=0
