Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

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

--
http://www.freelists.org/webpage/oracle-l