Optimizer_features_enable

For example, if you upgrade your database from release At a later time, you can try the enhancements introduced in releases up to and including release Oracle Database SQL Tuning Guide for more information about the optimizer and for information about the features listed in the following tables. All optimizer features listed in Table Previous Next JavaScript must be enabled to correctly display this content.

Note: When setting this parameter to a value representing Oracle Database 18c or later, you must specify three numeric values separated by periods, such as See Also: Oracle Database SQL Tuning Guide for more information about the optimizer and for information about the features listed in the following tables. Parameter type. Default value. Modifiable in a PDB. Adaptive cursor sharing. Join predicate pushdown. Use extended statistics to estimate selectivity.

Use native implementation for full outer joins. Partition pruning using join filtering.

optimizer_features_enable tips

Group by placement optimization. Null aware antijoins. Join Factorization. Cardinality Feedback. Subquery Unnesting. Subquery Coalescing. Table Expansion. Filtering Join Elimination. Dynamic statistics enhancements.

Adaptive Query Optimization. Online statistics gathering for bulk loads. Session level statistics for Global Temporary Tables. Multi-table left outer joins.

Lateral views. Batch table access by rowid. Null accepting semi joins. Scalar subquery unnesting. Conversion of joins that produce unnecessary duplicates to semi-joins. Parallel Union and Parallel Union All operations.

Enhance Auto DOP. Approximate count distinct. Support for Oracle Database In-Memory. Group-by and aggregation elimination. Approximate percentile and median processing.Roy today answered an interesting question on one of our internal mailing lists.

And this reminds me to pick up that piece of information as we see this quite often on customer sites, especially after upgrades or migrations. But in fact this is not true. Even though our documentation states it:. For example, if you upgrade your database from release At a later time, you can try the enhancements introduced in releases up to and including release In my experience the following is true: Setting this parameter reverts the optimizer settings in terms of parameters — but we still use the new optimizer code from that release you are on now.

And as far as I know nobody ever tests if switching back OFE will turn back to exactly the behaviour as it was known in the release OFE is now switched to. This can happen.

But if we act really really really precise than the performance should always be better with the optimizer running with the new settings — and not when the wheel is turned back. So in some cases this should be treated as a bug unless new behaviour leads to predictable worse results such as more buffer gets etc.

And I get so angry when Oracle Support people recommend to switch OFE to this or that setting to cure one or the otther misbehaviour. MOS Note And in addition regarding upgrades you may want to read this note here as well: MOS Note: Recommendation: Stay away from tweaking anything with OFE.

Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email. I agree. This site uses Akismet to reduce spam. Learn how your comment data is processed. Email Address - oracle. July 7, at OFE Oracle Scratchpad. Leave a Reply Cancel reply Your email address will not be published. Upcoming Events There are no upcoming events at this time.

Upgrade YouTube. Subscribe via Email Join 4, other subscribers. Follow me on Twitter My Tweets. By continuing to use this website, you agree to their use. To find out more, including how to control cookies, see here: Cookie Policy.This hint statement bought time to find the real cause for the poor SQL behavior.

Here, we back-off a 10g SQL statement back to its Oracle9i execution plan:.

optimizer_features_enable

The output from the report can be lengthy. Part of what the API tries is using different optimizer settings based on version. The extract below shows a path from You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.

OFE – Optimizer Features Enable

Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Oracle technology is changing and we strive to update our BC Oracle support information.

If you find an error or have a suggestion for improving our content, we would appreciate your feedback. All rights reserved by Burleson.

Search BC Oracle Sites.On rare occasions, after an upgrade from an earlier version to higher version, performance may degrade because new optimizer features are implemented but the environment is not setup correctly to allow them to perform to their full potential.

For example statistics may not be sufficiently accurate nor gathered to a sufficient level of detail for the features to operate in an optimal fashion.

Oracle Database Performance Tuning for Admins and Architects

Although such issues can largely be avoided by implementing some form of upgrade testing such as the use of Real Application Testing RAT features such as SQL Performance Analyzer and Database Replay and applying the relevent changes, if this has not happened then a suitable short term workaround may be necessary. In such cases, especially if insufficient testing has occurred, a system may be live on a new version and performance of certain queries can be severely reduced. This has serious potential for negatively affecting performance generally by eliminating the possibility of choosing better plans that are only available with features enabled within the higher revision levels.

In this Document Goal Solution References My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. Sign In.In some cases you may have information unknown to the optimizer, or need to tune the optimizer for a specific type of statement or workload.

In such cases, influencing the optimizer may provide better performance. Techniques for Influencing the Optimizer. Influencing the Optimizer with Initialization Parameters. Influencing the Optimizer with Hints.

The overlapping squares in the preceding diagram show that SQL plan management uses both initialization parameters and hints. SQL profiles also technically include hints. Parameters influence many types of optimizer behavior at the database instance and session level. The most important parameters are covered in " Influencing the Optimizer with Initialization Parameters ".

A hint is a commented instruction in a SQL statement. Hints control a wide range of behavior. See " Influencing the Optimizer with Hints ".

This package updates and manages optimizer statistics. The more accurate the statistics, the better the optimizer estimates. This chapter does not cover SQL profiles. SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.

This chapter does not cover SQL plan management. A stored outline is a legacy technique that serve a similar purpose to SQL plan baselines. In some cases, multiple techniques optimize the same behavior. For example, you can set optimizer goals using both initialization parameters and hints.

About Optimizer Initialization Parameters. Enabling Optimizer Features. Choosing an Optimizer Goal. Controlling Adaptive Optimization. Oracle Database includes several initialization parameters that can influence optimizer behavior. Converts literal values in SQL statements to bind variables.For example, if you upgrade your database from release At a later time, you can try the enhancements introduced in releases up to and including release Oracle Database SQL Tuning Guide for more information about the optimizer and for information about the features listed in the following tables.

See Also: Oracle Database SQL Tuning Guide for more information about the optimizer and for information about the features listed in the following tables. Parameter type. Default value. Modifiable in a PDB. Adaptive cursor sharing. Use extended statistics to estimate selectivity.

Use native implementation for full outer joins. Partition pruning using join filtering. Group by placement optimization. Join predicate pushdown. Filtering Join Elimination. Dynamic statistics enhancement. Adaptive Query Optimization. Online statistics gathering for bulk loads. Session level statistics for Global Temporary Tables. Multi-table left outer joins. Batch table access by rowid. Null accepting semi joins. Scalar subquery unnesting.

Conversion of joins that produce unnecessary duplicates to semi-joins. Parallel Union and Parallel Union All operations.

Why you shouldn’t set OPTIMIZER_FEATURES_ENABLE

Approximate count distinct. Support for Oracle Database In-Memory. Group-by and aggregation elimination.What do you think about it? Good or bad to use it? Every change new feature or bug fix you do to your software makes a new version of some part of the code. Those changes can be deployed individually as patches, grouped in patchset, or combined to build a new release. Which mean that you can compile an executable with exactly the set of features you want. But you can do more.

You can do even more: when you deploy a new release, you set a runtime version parameter that enables all the features you want to deploy into your new release. I know only one software that do that: the Oracle optimizer. It probably something hard to maintain for developers, but being able to choose at runtime the features you want to use is great and flexible. Well in Oracle this idea is not only for the optimizer, you can also choose the compatible version for the database: store it compatible with a previous version.

Being able to run the optimizer as of a previous version can be great, but it goes beyond that. For example:. Available in every edition. Want to know which values you can put there? You are starting a new project, building a new application?

optimizer_features_enable

Then install the latest database version and use the latest features. There are a lot of new features that will give more performance, more flexibility, more stability to your application, so use them. No OFE setting here except if you find a bug and need to workaround before getting the patch. But even there, you will not set OFE to previous version. You will disable only the feature or fix controls that cause the problem.

Then take the safe way. Set Optimizer Features to the previous version and everything will be fine. You build new reports on that application?

optimizer_features_enable

Then use new features for them. You can set it for the session. Why not having it set in a logon trigger that check the service name? But you should keep OFE to previous version only for a few months or year.

Donc cumulate the gaps on multiple releases. Take the occasion of an application release, when lot of non-regression testing will be done anyway, to bring OFE to latest version.


thoughts on “Optimizer_features_enable”

Leave a Reply

Your email address will not be published. Required fields are marked *