Re: [EXTERNAL] Question on sql plan management

You can just move all the baselines to the stage table post migration. And then delete all the baselines in prod from dba_sql_plan_baseilines by keeping the required ones enabled, accepted, fixed as YES. In this way you won't be having new baselines captured or created automatically by oracle for the same sql_handle going forward.

On Fri, Sep 9, 2022 at 4:33 PM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> I tested the behaviour in a lower environment. Only by setting FIXED=YES
> is not stopping the new baseline creation for the sql_handle. It has to be
> Enabled=YES too. :( So basically both the ENABLE and FIXED have to be set
> as "YES" and then only new baseline capture will be stopped for that sql.
>
>
>
> On Fri, Sep 9, 2022 at 9:23 AM Lok P <loknath.73_at_gmail.com> wrote:
>
>> So you have marked all the captured baselines as disabled. Have you
>> marked those also fixed=yes and see if it still creating new ones?
>>
>> On Thu, 8 Sep 2022, 4:56 pm yudhi s, <learnerdatabase99_at_gmail.com> wrote:
>>
>>> Thank You so much Mohamed and Lok.
>>>
>>> Yes the requirement is exactly same as Lok pointed. As we have already
>>> captured the baselines related to all type of workload in current version.
>>> We don't want any more of those baseline to be created/captured now. But to
>>> just utilize/enable them just in case we see any sql regression on 19C.
>>>
>>> But seems there is no easy way out for this which is odd and seems we
>>> are trapped. I think in such case, we have no choice here but need to keep
>>> dropping the newly created baselines from now onward manually i.e. based on
>>> the created date condition with origin=auto_capture from
>>> dba_sql_plan_baselines. Once we move to 19C and based on the sql regression
>>> we will mark the specific baseline as Enabled+accepted. And after few days,
>>> once we are good with the application stability we will drop all the
>>> captured baselines except the ones those are accepted by us for the
>>> regressed sqls.
>>>
>>> On Thu, 8 Sep 2022, 3:02 pm Lok P, <loknath.73_at_gmail.com> wrote:
>>>
>>>> I think what Yudhi is asking , As he has captured baselines for all
>>>> types of workload for now on 11g, so how to stop the creation of those new
>>>> non accepted baselines now along with marking all those captured ones as
>>>> unusable for now. And he is going to only mark the ones as used/enabled
>>>> which would give suboptimal plan on 19c on case by case basis.
>>>>
>>>> The parameters which you suggesting i.e ACCEPT_PLANS , is for making
>>>> those non accepted baseline to accepted automatically vs manually. Am I
>>>> missing something?
>>>>
>>>> On Thu, 8 Sep 2022, 2:34 pm Mohamed Houri, <mohamed.houri_at_gmail.com>
>>>> wrote:
>>>>
>>>>> Hello
>>>>>
>>>>> *But surprisingly, i am seeing it's still keep creating new baselines.
>>>>> Why is it so?*
>>>>>
>>>>> If you reread the CBO interaction diagram in the presence of an SPM,
>>>>> you will realize that, whatever the situation is, in presence of an SPM,
>>>>> the *CBO plan* is
>>>>>
>>>>> 1) either equivalent to the plan already in the SPM baseline and not
>>>>> inserted again into the SPM baseline (completely on the left in the
>>>>> diagram)
>>>>> 2) or inserted into the SPM baseline when it is not equivalent to the
>>>>> SPM baseline (completely on the right in the diagram)
>>>>>
>>>>>
>>>>> https://hourim.wordpress.com/2015/10/01/oracle-optimizer-and-spm-plan-interaction/
>>>>>
>>>>> *And does that mean, we need to delete the baselines fully if we don't
>>>>> want to use them for a specific SQL?*
>>>>>
>>>>> No; you need to prevent Oracle from automatically evolving the CBO
>>>>> plans inserted in the SPM Baseline by changing the following parameter
>>>>> value from TRUE to FALSE
>>>>>
>>>>> SELECT
>>>>> parameter_name,
>>>>> parameter_value
>>>>> FROM
>>>>> dba_advisor_parameters
>>>>> WHERE
>>>>> task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
>>>>> AND parameter_name in ('ACCEPT_PLANS','ALTERNATE_PLAN_BASELINE')
>>>>> ;
>>>>>
>>>>> PARAMETER_NAME PARAMETER_VALUE
>>>>> ------------------------------ ----------------
>>>>> ALTERNATE_PLAN_BASELINE EXISTING
>>>>> ACCEPT_PLANS TRUE
>>>>>
>>>>>
>>>>> BEGIN
>>>>> DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
>>>>> task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
>>>>> parameter => 'ACCEPT_PLANS',
>>>>> value => 'FALSE');
>>>>> END;
>>>>> /
>>>>>
>>>>> PARAMETER_NAME PARAMETER_VALUE
>>>>> ------------------------------ ----------------
>>>>> ALTERNATE_PLAN_BASELINE EXISTING
>>>>> ACCEPT_PLANS FALSE
>>>>>
>>>>> Best regards
>>>>> Mohamed Houri
>>>>>
>>>>> Le jeu. 8 sept. 2022 à 10:02, yudhi s <learnerdatabase99_at_gmail.com> a
>>>>> écrit :
>>>>>
>>>>>> We did turn off the auto capture and also disabled the baselines
>>>>>> those were created through auto capture process. But surprisingly, i am
>>>>>> seeing it's still keep creating new baselines. Why is it so?
>>>>>>
>>>>>> Does it mean, it doesn't check the enabled flag but just see if any
>>>>>> baseline exists in past for that SQL in dba_sql_plan_baselines, if yes,
>>>>>> then keep creating or evaluating new one for those sqls? And does that
>>>>>> mean, we need to delete the baselines fully if we don't want to use them
>>>>>> for a specific SQL?
>>>>>>
>>>>>> On Thu, 8 Sep 2022, 11:15 am Lok P, <loknath.73_at_gmail.com> wrote:
>>>>>>
>>>>>>> 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_at_gmail.com>
>>>>>>> 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_at_gmail.com> 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_at_gmail.com>
>>>>>>>>> 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_at_gmail.com> 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_at_gmail.com> 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>
>>>>>>>>>
>>>>>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> 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>
>>>>>
>>>>>

--
http://www.freelists.org/webpage/oracle-l