Hi
I looked a new 19c feature a few days ago, is related what we have been
discussing about capturing baselines and enabling SPM for all queries or
not. In 19c SPM is automatic and only picks candidates and not all SQL.
https://blogs.oracle.com/optimizer/what-is-automatic-sql-plan-management-and-why-should-you-care
Looks interesting and more intelligent, before this we have to spot the
candidates manually or capture baselines for ALL as some have done.
Thanks
On Tue, Mar 5, 2019 at 11:43 AM Jack Crocker <jackcrocker@xxxxxxxxx> wrote:
We use it for prod. If you are worried multiple baselines you can just
mark the ones you want as Fixed using dbms_spm and it will stop generating
new ones.
You can also export out the new baselines from your test environment and
import into your prod before code deployments so you know exactly how your
SQL will run.
I was originally against the idea, and it was a bit bumpy to start but
once the process is in place it's pretty smooth.
On Tue, Mar 5, 2019, 3:01 AM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Multiple parses will only happen when the CBO does a hard parse anyway
(hopefully rare) and fails to get a baselined plan. We definitely noticed
this on upgrade with non-sys data dictionary queries so we dropped them all
on upgrade and let them do their own thing.
If you have queries against views, and the views are modified to use
different tables / joins then you should expect the same issue. It is easy
enough to drop baselines that are on SQL that matches a like filter. If you
know it’s going to happen you can just drop them and accept your fate.
Other than that, we did not notice anything negative.
In 12.2 you can use dbms_spm.configure to prevent baselines being
captured in certain circumstances, one is a like filter against the
sql_text. You could use that to configure it to not capture plans that
reference data dictionary objects - we’re only just getting to 12.2 so it’s
not something I’ve properly looked at but it’s on the list!
Talking of 12.2, we noticed that the way Oracle computes the signature of
a statement with comments changes. It no longer considers anything
commented by — (it’s always been the case for /*comments*/). So, if like
me, your customer will rely on them for upgrade to 12.2: then you will want
the work around. Pack, drop and unpack your baselines that are like '%—%'
after the upgrade. Hopefully, Oracle will release a patch to recompute the
signatures of all baselines on upgrade so they can match up to the new SQL
signatures.
I would recommend doing baseline evolution tasks from time to time, I
seem to find it common to half tune a process - creating a new index
specifically for it and forgetting to get the baseline updated.
Hope this helps,
Andy
On Tue, 5 Mar 2019 at 07:14, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi Andy
Thanks for the explanation.
Have you noticed parsing slows down due to multiple parses with SPM?
Thanks
On Mon, Mar 4, 2019 at 1:22 PM Andy Sayer <andysayer@xxxxxxxxx> wrote:
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,about 6 months. The SPBs were generated in the perf test environment
We've got a large RAC database where we've been using SPBs for
and
then migrated across. There are still plenty of SQL statements in
live that
aren't using SPBs. Now people are asking about setting
volume testing and not seen any obvious bad side effects. My natural
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
in production, we've run with setting in perf test with full
reaction is to be cautious and say no.
experiences in this area would be most welcome.
Am I being sensible, overly cautious or what? Anybody's
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
