You could view 12.1 as a special case.
The adaptive optimisation features caused so many stability problems to so
many sites that official advice came out suggesting people adjust a couple
of hidden parameters to disable some of the adaptability features. If
you're going to have to start setting those hidden parameters anyway you
might as well just set OFE - provided you remember that it doesn't
guarantee that EVERY optimizer change will be reversed (and, in particular)
some of the optimizer bugs fixed in 12.1 may not be unfixed by setting the
OFE backwards.
Regards
Jonathan Lewis
On Mon, 11 Jan 2021 at 13:17, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you Shane.
Actually team says 12.1 is the buggier one and also all the .1 releases
having most of the issues , so trying to play safe by keeping OFE at 11.2.
So I wanted to check if it's really true. But i think , as per your point,
we should really stick with all defaults for that version, i.e. in our case
OFE- 12.1 only.
Thanks and regards
Pap
On Mon, Jan 11, 2021 at 4:59 PM Shane Borden <sborden76@xxxxxxxxx> wrote:
One last thing, it’s not like Oracle really tests this configuration
either. So if you really need OFE or some else set for a query, do it
with a profile or hint for that sql not systemwide.
Shane Borden
sborden76@xxxxxxxxx
Sent from my iPhone
On Jan 11, 2021, at 6:18 AM, Shane Borden <sborden76@xxxxxxxxx> wrote:you know you need to address a specific requirement such as Peoplesoft or
On an upgrade, I always reset the parameters to all defaults unless
EBS.
characteristics and eliminate technical debt that people likely can’t prove
Doing it this way gives you a chance to get back to default
the need to be there anyway.
prove the need. The worst reason ever is “that’s the way it’s always
Also, don’t let the App team dictate how to set it up unless they can
been”.
Oracle. We can't go to the latest versions(like 19C/18C) because of certain
Shane Borden
sborden76@xxxxxxxxx
Sent from my iPhone
On Jan 11, 2021, at 4:57 AM, Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello All, We are moving from version 11.2.0.4 to version12.1.0.2.0 of
application dependencies which are running on this database. Team is
suggesting to keep the optimizer_feature_enable as it was for existing
version i.e. 11.2.0.4, as because there are issues/bugs for version
12.1.0.2.0.
such a bad experience with OFE- 12.1.0.2.0 and we must keep the optimizer
So I wanted to check with experts here , if it's really so and have
feature at 11.2.0.4 even if the database version would be 12.1.0.2?
application code, but then we are thinking these will get the benefit of
For information, We do use a lot of Global temporary tables in our
the private session level stats on OFE- 12.1 and it won't happen such if we
keep the OFE at 11.2. Even if we don't change the code to gather stats on
the GTT immediately during migration , then that will still keep on using
dynamic sampling(in absence of session level stats) as it used to happen on
version 11.2. And gradually we will change our code to gather session level
stats in application code where estimation is going bad impacting execution
path.
Regards
Pap