Re: [EXTERNAL] Question on sql plan management

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