Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

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