Re: Question on sql plan management

Thank you so much. My question was the same . Basically the auto avolve task seems to add and accept new plans automatically to the existing set of baselines. So I wanted to understand from experts, how reliable and matured is the auto evolve task so as to choose a newly accepted plan as baseline? or manually evolving a plan and accepting it for each sql in the 19C database, is the best choice at the current scenario?

And if the existing baseline evaluation can be a bottleneck for heavy hard parsing queries, is there any workaround for the same?

On Thu, Sep 1, 2022 at 12:54 AM Lok P <loknath.73_at_gmail.com> wrote:

> 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