Re: latch: cache buffers chains
Date: Sat, 2 Oct 2021 16:41:22 -0400
Message-ID: <37843a74-6e16-5161-08cf-c40590b89e65_at_gmail.com>
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-lReceived on Sat Oct 02 2021 - 22:41:22 CEST