MIME-Version: 1.0
References: <1349686622.71482.YahooMailNeo@web29705.mail.ird.yahoo.com> <50731018.4000108@gmx.net>
In-Reply-To: <50731018.4000108@gmx.net>
From: Ls Cheng <exriscer@gmail.com>
Date: Sun, 3 Mar 2019 00:42:42 +0100
Message-ID: <CAJ2-Qb_QPTraxWk5XmKeSrvx4Su7OEDBZG7XHGa9qYgpMUsPQQ@mail.gmail.com>
Subject: Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production
To: a.piesk@gmx.net
Cc: "oracle-l@freelists.org" <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000e6c0ac0583251477"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: exriscer@gmail.com
--000000000000e6c0ac0583251477
Content-Type: text/plain; charset="UTF-8"

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@gmx.net> wrote:

> 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-l
>
>
>

--000000000000e6c0ac0583251477
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi<div><br></div><div>Bring this old topi=
c from 2012.</div><div><br></div><div>Does anyone actually set=C2=A0OPTIMIZ=
ER_CAPTURE_SQL_PLAN_BASELINES to TRUE in production? I have a customer cons=
idering it in 12.1.0.2 Exadata but I am not sure if this is good idea becau=
se if it is wouldn&#39;t this feature on by default?</div><div><br></div><d=
iv>My personal experience is use this feature for critical, regressed queri=
es 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&#39;t go away until they emptied SP=
M repository, those were 11.2.0.3 databases.</div><div><br></div><div>Anyth=
ing changed?</div><div><br></div><div>Thanks</div><div><br></div><div><br><=
/div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"g=
mail_attr">On Mon, Oct 8, 2012 at 7:42 PM Andreas Piesk &lt;<a href=3D"mail=
to:a.piesk@gmx.net">a.piesk@gmx.net</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex">On 08.10.2012 10:57, Chris Dunscombe wro=
te:<br>
&gt; Hi,<br>
&gt; We&#39;ve got a large RAC database where we&#39;ve been using SPBs for=
 about 6 months. The SPBs were generated in the perf test environment and t=
hen migrated across. There are still plenty of SQL statements in live that =
aren&#39;t using SPBs. Now people are asking about setting <br>
&gt; <br>
&gt; OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=3DTRUE<br>
&gt; <br>
&gt; in production, we&#39;ve run with setting in perf test with full volum=
e testing and not seen any obvious bad side effects. My natural reaction is=
 to be cautious and say no. <br>
&gt; <br>
&gt; Am I being sensible, overly cautious or what? Anybody&#39;s experience=
s in this area would be most welcome.<br>
&gt; <br>
&gt; Version 11.2.0.3 on RHEL 5.6<br>
&gt; <br>
<br>
i&#39;m in exactly the same spot and decided against OPTIMIZER_CAPTURE_SQL_=
PLAN_BASELINES=3DTRUE.<br>
<br>
my problem with OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is the non-existence o=
f any filters. i don&#39;t<br>
want baselines for all statements, i want baselines for the important stuff=
.<br>
<br>
initially i captured the important stuff in STS and loaded these STS as bas=
elines. after that i<br>
regularly refresh the STS (to capture new statements) and load them as base=
lines.<br>
<br>
did you noticed occasionally high runtimes for statements covered by SPBs?<=
br>
i&#39;m investigating an issue where a simple statement (select a from b wh=
ere c=3D:1) normally returns in<br>
about 10ms but sometimes the reponse time is 1000ms and more. a testtool ru=
nning a logical<br>
identical statement not covered by SPBs reports runtimes less than 100ms.<b=
r>
<br>
regards,<br>
-ap<br>
--<br>
<a href=3D"http://www.freelists.org/webpage/oracle-l" rel=3D"noreferrer" ta=
rget=3D"_blank">http://www.freelists.org/webpage/oracle-l</a><br>
<br>
<br>
</blockquote></div>

--000000000000e6c0ac0583251477--
--
http://www.freelists.org/webpage/oracle-l