Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

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