Re: [EXTERNAL] Question on sql plan management

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