We did it for the same reasons I suggested earlier
General plan stability
Easy to manipulate and test alternate plans
Easier to get sign off on patching and upgrades
This has been in place for about 2 years now and we have seen a huge
reduction in performance issues and time required to address a performance
issue
Thanks,
Andy
On Mon, 4 Mar 2019 at 11:07, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi Andy
And what made you decide to activate SPM for all queries in production :-?
Thanks
On Mon, Mar 4, 2019 at 10:00 AM Andy Sayer <andysayer@xxxxxxxxx> wrote:
We use this on our production databases. Have seen no issues caused by it
other than with system queries on DB upgrades because the massive change in
definition of views invalidates the baselines (easily solved). Space usage
is hardly anything in the grand scheme of things.
Performance stability makes testing and signing off upgrades so much
easier.
Thanks,
Andy
On Mon, 4 Mar 2019 at 07:54, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
Mladen, I told the customer that all SQL execute more than once will
create a baseline and this will end up consume considerable space in SYSAUX
but he says he does not mind, then I said if this was a wonderful feature
it would be enabled by default, that makes him start to think.
I only have seen a few people enabled this in production with no much
benefit, many years have passed since this oracle-l post it seems to me no
one set this in production yet. :-)
Thank you
On Sun, Mar 3, 2019 at 5:14 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
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.
If you want your optimizer to adjust more quickly, I would suggest
turning on the adaptive features,
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@xxxxxxx> wrote:
On 08.10.2012 10:57, Chris Dunscombe wrote:Mladen Gogala
Hi,6 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
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.
in this area would be most welcome.
Am I being sensible, overly cautious or what? Anybody's experiences
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
--
Database Consultant
Tel: (347) 321-1217
