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@xxxxxxx> wrote:
On 08.10.2012 10:57, Chris Dunscombe wrote:
Hi,months. The SPBs were generated in the perf test environment and then
We've got a large RAC database where we've been using SPBs for about 6
migrated across. There are still plenty of SQL statements in live that
aren't using SPBs. Now people are asking about setting
testing and not seen any obvious bad side effects. My natural reaction is
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
in production, we've run with setting in perf test with full volume
to be cautious and say no.
this area would be most welcome.
Am I being sensible, overly cautious or what? Anybody's experiences in
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
