Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

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.

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
> <mailto: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