MIME-Version: 1.0
References: <CAEjw_fhCEGxhKN=UKvSpj=FKJdnS_v5uoyr_dvd=AXgnapjLyw@mail.gmail.com>
 <CA+riqSUEzk7UFNrKZ0FZ=X1DkzpXKaEaM8Rw1T1HkzPFMyRJZQ@mail.gmail.com>
 <CAEjw_fiztzWCLgyZSjMT5+ySBhT1BDmaOjUKm7vhiT-XodM8mQ@mail.gmail.com>
 <55p-PDX85_zCh_GWlYkGZSUF9bSRVXfGiuixroaemoqVFYuFESjt3o5VTgG9z41X7fpdouFskiI4I_w0bPX7RMa1jhgRNzc0LsEwFIR56tM=@klockmail.com>
 <CAEjw_fgsuuvBriVn53VjUZQ3tCvmdYSsrWqMWTujwgw6KMgxCA@mail.gmail.com>
 <CAJu8R6jF+_piUOqE+x_Y7r2XVtBDZjK=f+DykVxZ5bXth6pkJw@mail.gmail.com>
 <CAEjw_fhEiY47b2j1rZ-zNr91s4S+S6uvo29NCWpEUaVCCXoRBA@mail.gmail.com>
 <CAKna9VZeZ1jhbSEnEhBGS9615oC2WdHJ+jRzHd_n+8COFMjmeg@mail.gmail.com>
 <CAJu8R6iM3+QOw0QYWs_LOsjunGyz=VH2ZCk_aNiZnhOeHdUh7w@mail.gmail.com>
 <CAEjw_fj7EK0V2RkCcQXDrZMZ-7hJkGgp1u8WG1bdEgrjnZOnvQ@mail.gmail.com>
 <CAJu8R6jB_10-+DdR1UBkAOVE9zvXtOD-60X7ihEsE8gFMM3DRA@mail.gmail.com>
 <CAEjw_fhK-ndLwG4Eq9W7ro9ZCtkamhR9G9-Rp99cUztCKPNv9Q@mail.gmail.com>
 <CAGtsp8=CqAeQVwdnutH-ga5SkvYdzzXOg0-kvXn5LVkCg3yUAA@mail.gmail.com> <CAEjw_fhafZQ3YaczjUFo12M0a4kZwV=ufpeujPwLkkirH0jutQ@mail.gmail.com>
In-Reply-To: <CAEjw_fhafZQ3YaczjUFo12M0a4kZwV=ufpeujPwLkkirH0jutQ@mail.gmail.com>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Wed, 22 Dec 2021 10:11:37 +0000
Message-ID: <CAGtsp8m1ZTiRDr+yCtNF-kazW2nAKxRT9dd6RQC1KT4vaS9ntQ@mail.gmail.com>
Subject: Re: Priority of profile baseline patch
To: Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000bd8d0505d3b95904"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--000000000000bd8d0505d3b95904
Content-Type: text/plain; charset="UTF-8"

You've got Table expansion, OR-expansion, and Join factorization going on
in the old plan, but the join factorization does not occur in the new plan.

The significant factor is probably the Table expansion - this suggests you
have some partial indexing in place, or some partitions of local indexes
that are currently unusable. If there was something about the pattern of
"missing" index data that FORCED oracle to use a 3-way OR-expansion then
everything else follows from there.

Have you posted the content of the profile yet ? If so I missed it.

Regards
Jonathan Lewis


On Mon, 20 Dec 2021 at 20:19, Pap <oracle.developer35@gmail.com> wrote:

> Thank You So much Jonathan.
>
> I think you are spot on wrt the OR expansion point. The plan which I am
> seeing now is not getting reproduced and thus causing a new
> baseline generation is having OR expansion. And thus the total number of
> lines in this new plan is ~113 as compared to ~87 in the earlier plan. I
> have attached the sample query with the old plan and the new one with their
> respective outlines.
>
> But yes the question would be why optimizer is now not able to produce the
> old plan(i.e mostly without OR expansion). No such object definition change
> or parameter setup changes have been done. Statistics are getting gathered
> on a daily basis on the underlying objects but that should not cause such
> issues. And this plan suddenly appeared after a particular date and is
> continuing now. And the note section in the new plan is showing the section
> below (i.e. it failed to reproduce the baseline). And used the sql profile,
> but the sql profile plan/old plan is not exactly the same as this one.
>
> We have not had any sql patch created here but yes the sql profile was not
> created using the traditional  method(which would have a bunch of
> opt_estimate hints) rather its created by forcing the exact outline hints
> of an awr plan i.e the old plan in the attached doc. So do you think that
> forced outline hints through sql profile can cause such an issue?
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>You&#39;ve got Table expansion, OR-e=
xpansion, and Join factorization going on in the old plan, but the join fac=
torization does not occur in the new plan.</div><div><br></div><div>The sig=
nificant factor is probably the Table expansion - this suggests you have so=
me partial indexing in place, or some partitions of local indexes that are =
currently unusable. If there was something about the pattern of &quot;missi=
ng&quot; index data that FORCED oracle to use a 3-way OR-expansion then eve=
rything else follows from there.</div><div><br></div><div>Have you posted t=
he content of the profile yet ? If so I missed it.</div><div><br></div><div=
>Regards</div><div>Jonathan Lewis</div><div><br></div><div><br></div><div><=
br></div><div>=C2=A0</div><div><br></div><div><br></div><div><br></div></di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On M=
on, 20 Dec 2021 at 20:19, Pap &lt;<a href=3D"mailto:oracle.developer35@gmai=
l.com">oracle.developer35@gmail.com</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"><div dir=3D"ltr">Thank You So much Jonat=
han.=C2=A0<div><br></div><div>I think you are spot on wrt the OR expansion =
point. The plan which I am seeing now is not getting reproduced and thus ca=
using a new baseline=C2=A0generation is having OR expansion. And thus the t=
otal number of lines in this new plan is ~113 as compared to ~87 in the ear=
lier plan. I have attached the sample query with the old plan and the new o=
ne with their respective outlines.=C2=A0</div><div><br></div><div>But yes t=
he question would be why optimizer is now not able to produce the old plan(=
i.e mostly without OR expansion). No such object definition=C2=A0change or =
parameter setup changes have been done. Statistics are getting gathered on =
a daily basis on the underlying objects but that should not cause such issu=
es. And this plan suddenly appeared=C2=A0after a particular=C2=A0date and i=
s continuing now. And the note section in the new plan is showing the secti=
on below (i.e. it failed to reproduce the baseline). And used the sql profi=
le, but the sql profile plan/old plan is not exactly the same as this one.<=
/div><div><br></div><div>We have not had any sql patch created here but yes=
 the sql profile was not created using the traditional=C2=A0 method(which w=
ould have a bunch of opt_estimate hints) rather its=C2=A0created by forcing=
 the exact outline hints of an awr plan i.e the old plan in the attached do=
c. So do you think that forced outline hints through sql profile can cause =
such an issue?</div></div></blockquote><br><div dir=3D"ltr"><div dir=3D"ltr=
"><p style=3D"margin-bottom:0.0001pt"><span style=3D"font-size:10pt;font-fa=
mily:Arial,&quot;sans-serif&quot;"></span></p></div></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div class=3D"gmail_quote"><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"><div class=3D"gmail_quote"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><div class=3D"gmail_quote"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex">
</blockquote></div>
</blockquote></div>
</blockquote></div>
</blockquote></div></div>

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