Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

Hi

I looked a new 19c feature a few days ago, is related what we have been discussing about capturing baselines and enabling SPM for all queries or not. In 19c SPM is automatic and only picks candidates and not all SQL.

https://blogs.oracle.com/optimizer/what-is-automatic-sql-plan-management-and-why-should-you-care

Looks interesting and more intelligent, before this we have to spot the candidates manually or capture baselines for ALL as some have done.

Thanks

On Tue, Mar 5, 2019 at 11:43 AM Jack Crocker <jackcrocker_at_gmail.com> wrote:

> We use it for prod. If you are worried multiple baselines you can just
> mark the ones you want as Fixed using dbms_spm and it will stop generating
> new ones.
>
> You can also export out the new baselines from your test environment and
> import into your prod before code deployments so you know exactly how your
> SQL will run.
>
> I was originally against the idea, and it was a bit bumpy to start but
> once the process is in place it's pretty smooth.
>
>
> On Tue, Mar 5, 2019, 3:01 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Multiple parses will only happen when the CBO does a hard parse anyway
>> (hopefully rare) and fails to get a baselined plan. We definitely noticed
>> this on upgrade with non-sys data dictionary queries so we dropped them all
>> on upgrade and let them do their own thing.
>>
>> If you have queries against views, and the views are modified to use
>> different tables / joins then you should expect the same issue. It is easy
>> enough to drop baselines that are on SQL that matches a like filter. If you
>> know it’s going to happen you can just drop them and accept your fate.
>>
>> Other than that, we did not notice anything negative.
>>
>> In 12.2 you can use dbms_spm.configure to prevent baselines being
>> captured in certain circumstances, one is a like filter against the
>> sql_text. You could use that to configure it to not capture plans that
>> reference data dictionary objects - we’re only just getting to 12.2 so it’s
>> not something I’ve properly looked at but it’s on the list!
>>
>> Talking of 12.2, we noticed that the way Oracle computes the signature of
>> a statement with comments changes. It no longer considers anything
>> commented by — (it’s always been the case for /*comments*/). So, if like
>> me, your customer will rely on them for upgrade to 12.2: then you will want
>> the work around. Pack, drop and unpack your baselines that are like '%—%'
>> after the upgrade. Hopefully, Oracle will release a patch to recompute the
>> signatures of all baselines on upgrade so they can match up to the new SQL
>> signatures.
>>
>> I would recommend doing baseline evolution tasks from time to time, I
>> seem to find it common to half tune a process - creating a new index
>> specifically for it and forgetting to get the baseline updated.
>>
>> Hope this helps,
>> Andy
>>
>>
>> On Tue, 5 Mar 2019 at 07:14, Ls Cheng <exriscer_at_gmail.com> wrote:
>>
>>> Hi Andy
>>>
>>> Thanks for the explanation.
>>>
>>> Have you noticed parsing slows down due to multiple parses with SPM?
>>>
>>> Thanks
>>>
>>>
>>> On Mon, Mar 4, 2019 at 1:22 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>>>
>>>> We did it for the same reasons I suggested earlier
>>>>
>>>> General plan stability
>>>> Easy to manipulate and test alternate plans
>>>> Easier to get sign off on patching and upgrades
>>>>
>>>> This has been in place for about 2 years now and we have seen a huge
>>>> reduction in performance issues and time required to address a performance
>>>> issue
>>>>
>>>> Thanks,
>>>> Andy
>>>>
>>>> On Mon, 4 Mar 2019 at 11:07, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>
>>>>> Hi Andy
>>>>>
>>>>> And what made you decide to activate SPM for all queries in production
>>>>> :-?
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Mar 4, 2019 at 10:00 AM Andy Sayer <andysayer_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> We use this on our production databases. Have seen no issues caused
>>>>>> by it other than with system queries on DB upgrades because the massive
>>>>>> change in definition of views invalidates the baselines (easily solved).
>>>>>> Space usage is hardly anything in the grand scheme of things.
>>>>>>
>>>>>> Performance stability makes testing and signing off upgrades so much
>>>>>> easier.
>>>>>>
>>>>>> Thanks,
>>>>>> Andy
>>>>>>
>>>>>> On Mon, 4 Mar 2019 at 07:54, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>>>
>>>>>>> Hi
>>>>>>>
>>>>>>> Mladen, I told the customer that all SQL execute more than once will
>>>>>>> create a baseline and this will end up consume considerable space in SYSAUX
>>>>>>> but he says he does not mind, then I said if this was a wonderful feature
>>>>>>> it would be enabled by default, that makes him start to think.
>>>>>>>
>>>>>>> I only have seen a few people enabled this in production with no
>>>>>>> much benefit, many years have passed since this oracle-l post it seems to
>>>>>>> me no one set this in production yet. :-)
>>>>>>>
>>>>>>>
>>>>>>> Thank you
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Sun, Mar 3, 2019 at 5:14 AM Mladen Gogala <
>>>>>>> gogala.mladen_at_gmail.com> wrote:
>>>>>>>
>>>>>>>> Well, baselines are essentially a bunch of hints which determine
>>>>>>>> the execution plan. Setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to
>>>>>>>> true would create a baseline for every single SQL statement executed by a
>>>>>>>> non-SYS user in the database, including trivialities like 'SELECT SYSDATE
>>>>>>>> FROM DUAL". There are several logical consequences to that:
>>>>>>>>
>>>>>>>> - Baseline tables would grow rapidly. The growth would also
>>>>>>>> include their indexes.
>>>>>>>> - DBA would not be able to select the baseline for the given
>>>>>>>> SQL because there would be too many to choose.
>>>>>>>> - Every time the optimizer decides that it needs to change the
>>>>>>>> plan for the given SQL, a new baseline will be created. In an inevitable
>>>>>>>> case that some of the SQL statements with the new baselines are not
>>>>>>>> performing as expected, the DBA would have to dig deep into the existing
>>>>>>>> baselines, purge the inadequate ones and create a new one manually.
>>>>>>>>
>>>>>>>> If you want your optimizer to adjust more quickly, I would suggest
>>>>>>>> turning on the adaptive features,
>>>>>>>>
>>>>>>>>
>>>>>>>> On 3/2/19 6:42 PM, Ls Cheng wrote:
>>>>>>>>
>>>>>>>> Hi
>>>>>>>>
>>>>>>>> Bring this old topic from 2012.
>>>>>>>>
>>>>>>>> Does anyone actually set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to
>>>>>>>> TRUE in production? I have a customer considering it in 12.1.0.2 Exadata
>>>>>>>> but I am not sure if this is good idea because if it is wouldn't this
>>>>>>>> feature on by default?
>>>>>>>>
>>>>>>>> My personal experience is use this feature for critical, regressed
>>>>>>>> queries only. I also had a couple of customers who set this parameter to
>>>>>>>> TRUE a few years ago and they had production outage due to parsing problems
>>>>>>>> (latch/mutex contention) and the problem didn't go away until they emptied
>>>>>>>> SPM repository, those were 11.2.0.3 databases.
>>>>>>>>
>>>>>>>> Anything changed?
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Oct 8, 2012 at 7:42 PM Andreas Piesk <a.piesk_at_gmx.net>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> On 08.10.2012 10:57, Chris Dunscombe wrote:
>>>>>>>>> > Hi,
>>>>>>>>> > We've got a large RAC database where we've been using SPBs for
>>>>>>>>> about 6 months. The SPBs were generated in the perf test environment and
>>>>>>>>> then migrated across. There are still plenty of SQL statements in live that
>>>>>>>>> aren't using SPBs. Now people are asking about setting
>>>>>>>>> >
>>>>>>>>> > OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
>>>>>>>>> >
>>>>>>>>> > in production, we've run with setting in perf test with full
>>>>>>>>> volume testing and not seen any obvious bad side effects. My natural
>>>>>>>>> reaction is to be cautious and say no.
>>>>>>>>> >
>>>>>>>>> > Am I being sensible, overly cautious or what? Anybody's
>>>>>>>>> experiences in this area would be most welcome.
>>>>>>>>> >
>>>>>>>>> > Version 11.2.0.3 on RHEL 5.6
>>>>>>>>> >
>>>>>>>>>
>>>>>>>>> i'm in exactly the same spot and decided against
>>>>>>>>> OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE.
>>>>>>>>>
>>>>>>>>> my problem with OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is the
>>>>>>>>> non-existence of any filters. i don't
>>>>>>>>> want baselines for all statements, i want baselines for the
>>>>>>>>> important stuff.
>>>>>>>>>
>>>>>>>>> initially i captured the important stuff in STS and loaded these
>>>>>>>>> STS as baselines. after that i
>>>>>>>>> regularly refresh the STS (to capture new statements) and load
>>>>>>>>> them as baselines.
>>>>>>>>>
>>>>>>>>> did you noticed occasionally high runtimes for statements covered
>>>>>>>>> by SPBs?
>>>>>>>>> i'm investigating an issue where a simple statement (select a from
>>>>>>>>> b where c=:1) normally returns in
>>>>>>>>> about 10ms but sometimes the reponse time is 1000ms and more. a
>>>>>>>>> testtool running a logical
>>>>>>>>> identical statement not covered by SPBs reports runtimes less than
>>>>>>>>> 100ms.
>>>>>>>>>
>>>>>>>>> regards,
>>>>>>>>> -ap
>>>>>>>>> --
>>>>>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>> Mladen Gogala
>>>>>>>> Database Consultant
>>>>>>>> Tel: (347) 321-1217
>>>>>>>>
>>>>>>>>

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