Re: Question on sql plan management

In regards to hard parsing , in case of plan baseline CBO has to go through evaluating all possible paths and then will match with existing accepted baselines. Which means a parse intensive query is not going to be get any relaxation and it may worsen. But in this scenario, if you drop the baseline and create a SQL profile then that should be helpful , as optimizer is going to restrict itself with the set of hints or estimations forced through the SQL profile , so parsing burden should be minimised here as less plan evaluation. See below blog by "Mohamed houri".

https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-interaction/

In regards to evolving new plan baselines and accepting those, i have never noticed auto evolve task usage. But it seems a step towards automation and anybody having usage experience may comment on this.

But again in your case , after moving to 19c and then evolving each and every newly created SQL plan baselines manually to accept the newly created (and hopefully more efficient 19c OFE) plan seems more cumbersome. Others can comment.

On Thu, 1 Sep 2022, 1:13 am yudhi s, <learnerdatabase99_at_gmail.com> wrote:

> 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