Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production
Date: Mon, 08 Oct 2012 19:40:40 +0200
Message-ID: <50731018.4000108_at_gmx.net>
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-lReceived on Mon Oct 08 2012 - 19:40:40 CEST