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@xxxxxxxxx> 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@xxxxxxxxx>
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
