Re: How to fix cache buffer chain issue

Hi Andrew, the below query will return <100 rows most of the time. Do you mean to say even the current plan can be tweaked(through some hints) to make it perform more efficiently?

SELECT count(*)
FROM TNI TNI, TMTD TMTD
WHERE TNI.NE <http://tni.ne/> = 'XX'
AND TNI.NID = TMTD.DID Regards
Pap

On Mon, Jun 21, 2021 at 1:06 AM Andy Sayer <andysayer_at_gmail.com> wrote:

> Jonathan wrote:
> "
> Looking at the predicate information for operation 15 you can see that it
> is an ACCESS predicate (that, since it came from memory) has "lost" the
> subquery bit.
> This means the subquery runs one to generate a value that is effectively
> then used as a constant to drive the index range scan at operation 15. The
> index tad_ix2 is (presumably) on the column (date_cr), so this part of the
> query selects all data for the most recent date by index range scan. (And
> possibly will be doing this 17 times in the course of the query).
> "
>
> According to the plan in the original attachment, the join to TAD is done
> via a nested loop. This would mean that this read of all rows in TAD where
> DT_CR=MAX(DT_CR) happens 18 * (rows in TNI with NE='XX'). This plan had
> notes but didn't indicate it was an adaptive plan, I reckon this difference
> would easily explain the difference in run times.
> Having to read rows which have already been recently read is usually going
> to be logical reads so the most likely waits *sampled* are going to be do
> with that, but most of the work will come under CPU.
>
> Pap, what does
>
> SELECT count(*)
> FROM TNI TNI, TMTD TMTD
> WHERE TNI.NE = 'XX'
> AND TNI.NID = TMTD.DID
>
> Return? Somewhere in the region of 60?
>
> (Obviously, Jonathan's advise is going to be sound, but this will probably
> explain your observation).
> (Aside, AWR gets predicates in 21c so getting similar answers out of
> historic plans is going to be much easier)
>
> Thanks,
> Andrew
>
> On Sun, 20 Jun 2021 at 20:27, Dominic Brooks <dombrooks_at_hotmail.com>
> wrote:
>
>> 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_at_gmail.com>
>> 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&amp;data=04%7C01%7C%7C49c907d03cf14b8ff8df08d93412aa17%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637598077036369290%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=hgONyg%2FIkfVaGCwg3htYSEXTZlrYSBS3Bbz7TUO8j5k%3D&amp;reserved=0
>> >
>> > --
>> >
>> https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&amp;data=04%7C01%7C%7C49c907d03cf14b8ff8df08d93412aa17%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637598077036369290%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=dAYarKUDC4QON%2F3eDiwvMaDyIEhRdBG9YYMabWLoV7c%3D&amp;reserved=0
>> >
>> >
>>
>

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