Thank You Jonathan.
Actually we were not having an environment which is close to production in
terms of data volume and data pattern and infrastructure. And currently we
have turned on the "capture baseline" flag to TRUE in production itself
which is on 12.1 version and we already have hundreds of thousands of sql
baselines getting captured in production on a daily basis.And i noticed the
"use baseline" flag is already set as default TRUE. And we are planning to
set the capture baseline flash now to FALSE as we have all types of
workloads covered till now. In this situation, how should we be able to
make use of 19C features or plans on top of the existing baselines post
migration?
On Sat, Sep 3, 2022 at 8:49 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
One fairly standard approach is to test on a full-size backup copy of
production with no SQL baselines to find the statements that show much
worse performance in 19c, then go back to the 11g versions and capture
plans for only those statements before the live upgrade.
Regards
Jonathan Lewis
On Sat, 3 Sept 2022 at 14:21, yudhi s <learnerdatabase99@xxxxxxxxx> wrote:
Thank you All.
I understand for some of the specific scenarios like fixing wrong results
related to bugs from some transformation etc, it may happen that a certain
plan is not reproducible exactly on 19c which was there in 11.2 /12.1
versions. In these cases in the19c version, it will come up with a new plan
which will eventually not match with any of the accepted old baselines and
thus the optimizer will forcibly choose a different path altogether and
create that as a new accepted baseline.
However, doesn't it sound odd that in cases where, of all those hundreds
of thousands of sqls using one of the captured baselines from 12.1 by auto
capture process which are now in an accepted state from the 12.1 version,
has to be manually evolved and accepted to use the 19C feature or new
plans? Considering enabling auto evolve jobs as riskier to go for. So I
wanted to understand what is the standard or lesser riskier suggested way
of doing this when moving to the 19c version?
