Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

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