Re: Question on sql plan management

I have not used it exactly in real production though, but I think you have answered it partly. If you see below oracle doc, it says even post the optimizer_capture_sql_plan_baselines set to FALSE, the new possible plans(i.e. the 19C OFE plans) evaluated by the CBO will be automatically added to the list of existing baselines but with ACCEPTED flag as 'NO' and that has to be evolved and accepted manually by you. Also there exists an auto evolve task (SYS_AUTO_SPM_EVOLVE_TASK) which runs on a nightly maintenance window can do this job automatically for you.

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-19c-5324207.pdf

On Thu, Sep 1, 2022 at 12:38 AM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Hello, we are migrating from 12.1 version Oracle database to 19C. And just
> to avoid any surprises we have turned on
> optimizer_capture_sql_plan_baselines to TRUE in current 12.1
> production. Each day we are seeing thousands of baselines getting created
> in dba_sql_plan_baselines with both the flag ACCEPTED and ENABLED as YES.
> And as I understand , each of the sql that were executed are now associated
> with one baselines or a specific execution path. We are planning to turn
> the optimizer_capture_sql_plan_baselines back to FALSE after all the
> possible workload times(like daily/weekly/monthly jobs sql) are captured so
> that no sql is left without usage of an 'accepted' baseline.
>
> Now as I understand this above baselines will work as a shield for the
> plan regression issue. As because , even with the 19C optimizer feature,
> the queries are going to follow the captured 12.1 baseline
> path which is in the accepted state. But my question was , as 19C has a
> lot of enhancement done in the optimizer and those may actually benefit
> many or some of the existing queries, so what is the
> suggested way to get those new or better plans added safely to the
> existing sql queries or accepted baselines? Or should we rely on the oracle
> given the auto evolve task(SYS_AUTO_SPM_EVOLVE_TASK) to do this for us? And
> will the captured baselines cause issues for heavy hard parse queries?
>
> Regards
> Yudhi
>

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