Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

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