Re: [EXTERNAL] Question on sql plan management

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