If you want to selectively use the baseline but not all those captured then
I think after turning OFF the auto capture process you can disable all of
those 300k baselines those are created as part of your 11g auto capture
process leaving the ones those are really in use in 11g. Then when you move
to 19c just use or enable the ones which are regressing or showing sub
optimal performance only on 19c.
On Mon, 5 Sep 2022, 6:24 pm yudhi s, <learnerdatabase99@xxxxxxxxx> wrote:
Thank you Mohamed.
I checked current production environment(still on 12.1) , so far we have
the capture process turned on for ~7days and it has ~373K baselines created
for ~299K distinct sql_handles or sqls. I didn't see the spike in
concurrency waits in overall database level though. Even if i see the
number of baselines created for few of the sql_handles is 20+ , but all of
those having only one baselines with accepted=YES and enabled=YES other
were created with accepted=NO. Out of all the ~377K baselines , I see only
two sqls/sql_handles having two baselines each with both enabled='YES' and
accepted='YES'.
So as per below algo, in most of our cases optimizer will only go through
"one" evaluation of the accepted+enabled plan and thus may not cause a
parsing storm. And if we ensure to keep one 'accepted=yes' baseline for any
sql that should be okay to not cause concurrency issue during parsing. Also
as Pap mentioned , i hope during parsing issue for any SQL, dropping the
baselines and creating as sql profile out of those plan may be the way to
go. Correct me if my understanding is wrong.
https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-interaction/
Also in below doc page-5 , its mentioned in the auto capture process
"Regardless of which method you use to initially create a SQL plan
baseline, any subsequent new plan found for that SQL statement will be added
to the plan baseline as an unaccepted plan. This behavior is not dependent
on the initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_ BASELINES and
will occur even if this parameter is set to FALSE (the default)."
https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-19c-5324207.pdf
On Mon, 5 Sep 2022, 12:55 pm Mohamed Houri, <mohamed.houri@xxxxxxxxx>
wrote:
Hello,
Your 12c SPM execution plans will be used only if they are reproducible
in 19c. Otherwise, Oracle will use the 19c plan it produces at hard parse
time.
So, in 19c, you will be in one of the two following situations:
1. CBO comes up with a new 19c plan present in the SPM baseline
a) You will use the 19c CBO (or the 12c SPM plan)
2. CBO comes up with a new 19c plan not present in the SPM baseline
b) Your 12c SPM baseline plans are reproducible: you will use the
SPM plan
c) Your 12c SPM baseline plans are not reproducible and you will
use the 19c plan
*How should we be able to make use of 19C features or plans on top of the
existing baselines post migration? *
If you are in situation n°1 it means that your queries are processed by
Oracle in the same way in both releases 12c and 19c
However, if you are in situation n°2 b), then it is possible that the new
19c plan is better (or worse). And this is where you need to strategize.
Opt for stability and keep using the SPM 12c plans (you will have to
prevent Oracle from evolving the 19c plan by accepting it as a new valid
plan in the baseline), or let Oracle automatically evolves the 19c plan
But I think that you have opted for a bad strategy which consists in
capturing execution plans of all queries executed twice. This will fill the
SYSAUX tablespace and introduce considerable parsing effects, especially
for queries that change plans too often (due to ACS for example). If the
new 19c plan is not present in the SPM baseline and this SPM baseline
contains more than 2 ACCEPTED AND ENABLED plans, then all those
execution plans will enter in competition to determine the best one. And
you can imagine that this is not free in terms of CPU and Cursor Pin S wait
on X and library cache lock wait events.
Best regards
Mohamed Houri
Le sam. 3 sept. 2022 à 20:10, yudhi s <learnerdatabase99@xxxxxxxxx> a
écrit :
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?
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
