Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production
- From: Andreas Piesk <a.piesk@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 08 Oct 2012 19:40:40 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
