Re: latch: cache buffers chains

My particular issue looks to be related with the oracle execution engine and not necessary with optimizer. I was trying different hints just to see if I can improve the execution.

Like Jonathan mentioned, apparently the issue is with buffered reads of GTTs in standby database using parallel execution. I tried to avoid buffered reads so I used set_table_stats(numblks) to a number very high and _small_table_threshold to a very low number.

This is solving half of my issues: looks like the first execution will be very fast, but subsequent runs will still do some buffered reads and will be slower. The more executions, the higher the contention and query is getting slower and slower each time.
The problem is that the query to be "tuned" is composed of 6 union all blocks, all using multiple GTTs, so first union will be fast then next ones will be slower and slower.

Is there any trick I can do so that oracle will not read this object(GTT) at all from the buffer cache?
(Looks like _small_table_threshold or _serial_direct_read or parallel execution is not doing the trick)

În sâm., 2 oct. 2021 la 23:41, Mladen Gogala <gogala.mladen_at_gmail.com> a scris:

>
> On 10/2/21 15:39, Laurentiu Oprea wrote:
> > Initially I was using some hints and now I remove them to let the
> > optimizer decide the execution plan.
>
> With all the plan features (statistics, adaptive plans, dynamic
> sampling), I sometimes see a royal mess. A client of mine has recently
> updated to the fancy full rack Exadata X8, with RDMA and persistent
> memory commit accelerator and immediately some plans went to full table
> scan across all partitions. I set optimizer_index_cost_adj to 20 and
> optimizer_index_caching to 85 which has fixed the vast majority of
> plans. However, 2 plans went with the wrong index and I had to create
> baselines to fix them. I have also turned the adaptive features off.
> Plans (and performance) are good as they are. I don't need them to
> adapt. I have not seen any feature that could automatically fix bad plan
> yet. There is but one feature that can fix bad plans and it's optional
> with Oracle 19c EE: a competent DBA.
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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