Oracle performance

Oracle COMPATIBLE VS OPTIMIZER_FEATURES_ENABLE

Bulent Soylu July 13, 2023

COMPATIBLE PARAMETER

Understanding the COMPATIBLE Initialization Parameter

In Oracle Database 19c, when the COMPATIBLE initialization parameter is not set in your parameter file, the COMPATIBLE parameter value defaults to 19.0.0 If you do not set the COMPATIBLE initialization parameter to 19.0.0, then you cannot use the new Oracle Database 19c features, because your upgraded database is not running in the required COMPATIBILITY setting for Oracle Database 19c features.

Notes:

  • Before upgrading to Oracle Database 19c, you must set the COMPATIBLE initialization parameter to at least 11.2.0, which is the minimum setting for Oracle Database 19c.
  • The compatible parameter must be at least 3 decimal numbers, separated by periods. For example:
    SQL> ALTER SYSTEM SET COMPATIBLE = ‘12.2.0.1’ SCOPE=SPFILE;
  • Setting COMPATIBLE ensures that new features do not write data formats or structures to disk that are not compatible with the earlier release, preventing a future downgrade. Features that require a higher value of COMPATIBLE to work correctly may be restricted or disabled to ensure downgrades are possible.The COMPATIBLE parameter specifies the Oracle version number that the database disk format must be compatible with. The database can be downgraded to the version specified in the COMPATIBLE parameter or any later version.

When to Set the COMPATIBLE Initialization Parameter in Oracle Database

Oracle recommends increasing the COMPATIBLE parameter only after you have completed testing the upgraded database.

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the new Oracle Database release. However, after you increase the COMPATIBLE parameter, you cannot subsequently downgrade the database.

How the COMPATIBLE Initialization Parameter Operates in Oracle Database

The COMPATIBLE initialization parameter enables or disables Oracle Database features based on release compatibility.

The COMPATIBLE initialization parameter operates in the following way:

  • The COMPATIBLE initialization parameter enables or disables the use of features, to help protect your existing application use of data.

    If you run an Oracle Database 12c database with the COMPATIBLE initialization parameter set to 11.2.0, then the database software generates database structures on disk that are compatible with Oracle Database Release 11g release 2 (11.2). If you try to use features that are part of a later release of Oracle Database, and make the database incompatible with the COMPATIBLE initialization parameter, then an error occurs. However, new features are enabled that do not create changes on disk that are incompatible with Oracle Database Release 11g release 2.
  • If you make changes to the database that make the database incompatible with the COMPATIBLE initialization parameter setting you want to use, then the database does not start, and initialization terminates in an error. If this happens, then you must set the COMPATIBLE initialization parameter to an appropriate value for the database.

Checking the Compatibility Level of Oracle Database

Use this SQL query to check that the compatibility level of your database corresponds to the value of the COMPATIBLE initialization parameter:

SQL> SELECT name, value FROM v$parameter  WHERE name = 'compatible';

Changing the Compatibility Level of Oracle Database You can only set it to a higher value. You can’t revert it to a lower value since Oracle 9i. The ALTER DATABASE RESET COMPATIBILITY command does not exist anymore. Hence, once changed, you are not able to revert to the previous value.

Changing COMPATIBLE requires a restart of the database. It is static parameter.

FOR SINGLE INSTANCE
SQL> alter system set COMPATIBLE='19.0.0' scope=spfile;
SQL> shutdown immediate
SQL> startup;

FOR RAC
alter system set COMPATIBLE='19.0.0' scope=spfile sid=’*’;
srvctl stop database -d DBNAME
srvctl start database -d DBNAME

You can’t adjust COMPATIBLE while the database is up and running. And you can’t have different COMPATIBLE settings on different instances in a RAC environment.

When you change it, it most likely will adjust the structure of the controlfiles, the redologs and the data file headers. And in the database, you may have now access to new features such as the online move of data files, etc.

COMPATIBLE is also used to determine how your database acts to the “outside”, i.e. how it interacts with an application. Having COMPATIBLE set to 12.2.0 in an Oracle 19c environment should allow the database to behave as it would be an 12.2 database.

And if you ask if you could run a database for months or years with a lower COMPATIBLE setting, then the answer is: Of course, you can. But you will miss a lot of cool features. That is the downside.

How Many Digits Should You Use?

The default is 3 number, for instance ‘19.0.0‘. And 3 numbers are always enough.

OPTIMIZER_FEATURES_ENABLE PARAMETER

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

For example, if you upgrade your database from release 12.2 to release 19.11, but you want to keep the release 12.2 optimizer behavior, you can do so by setting this parameter to 12.2.0.1. At a later time, you can try the enhancements introduced in releases up to and including release 19.11 by setting the parameter to 19.11.0.0.

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 18.1.0 or 19.1.0.

Changing OPTIMIZER_FEATURES_ENABLE does not require a restart of the database. It is dynamic parameter.

FOR SINGLE INSTANCE
SQL> alter system set OPTIMIZER_FEATURES_ENABLE ='19.11.0' scope=both;

FOR RAC
SQL> alter system set OPTIMIZER_FEATURES_ENABLE ='19.11.0' scope=both sid=’*’;

COMPATIBLE VS OPTIMIZER

A very common misunderstanding happens when COMPATIBLE gets mixed with the Oracle optimizer. Both are independent from each other. An adjustment in COMPATIBLE has no effect on the optimizer and its features. Those are derived from the value of OPTIMIZER_FEATURES_ENABLE


credit: https://serhatcelik.wordpress.com/2022/03/07/oracle-compatible-vs-optimizer_features_enable/

Related posts:

  1. How to Lock, Unlock, Transfer Statistics in oracle
  2. Partition Pruning and NLS Settings
  3. What is SQL PROFILE in oracle?
  4. APPEND_VALUES and SYS_DL_CURSOR Hints with HCC