To make upgrades easier, I regularly see people considering disabling query optimizer features by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to a non-default value. My general opinion about this “habit” is summarized in TOP with the following two sentences:
Changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter is only a short-term workaround. Sooner or later the application should be adapted (optimized) for the new database version.
The issue is that not all new features are disabled by the OPTIMIZER_FEATURES_ENABLE initialization parameter. For example, if you set it to 10.2.0.5 in version 12.1.0.2, you will not get exactly the 10.2.0.5 query optimizer. A particular case to be considered in 12.1 is related to the new types of histograms (top-frequency and hybrid). In fact, even though you set the OPTIMIZER_FEATURES_ENABLE initialization parameter to 10.2.0.5, the DBMS_STATS package can produce top-frequency and hybrid histograms. As a result, the estimations of the query optimizer can change. An example is provided by this script.
Note that also setting the COMPATIBLE initialization parameter to, for example, 11.0.0 with the 12.1.0.2 binaries does not prevent the DBMS_STATS package to gather top-frequency and hybrid histograms.
So, if you are really planning to upgrade to 12.1 and changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter, in my opinion you should make sure that the DBMS_STATS package does not create top-frequency and hybrid histograms. Unfortunately, with the METHOD_OPT parameter, it is not possible to specify which type of histograms the DBMS_STATS package can create. The only input parameter of the GATHER_*_STATS procedures that affects this decision is ESTIMATE_PERCENT. When it is set to a numeric value greater than 0, the DBMS_STATS package does not create top-frequency and hybrid histograms. In other words, the DBMS_STATS package considers the creation of the new types of histograms only if the ESTIMATE_PERCENT parameter is set to the DBMS_STATS.AUTO_SAMPLE_SIZE constant (0).
Another possibility to prevent that the DBMS_STATS package creates top-frequency and hybrid histograms is to set the undocumented preferences ENABLE_TOP_FREQ_HISTOGRAMS and ENABLE_HYBRID_HISTOGRAMS. For example, to set them globally, you can use the following PL/SQL block:
BEGIN dbms_stats.set_global_prefs(pname => 'ENABLE_HYBRID_HISTOGRAMS', pvalue => 0); dbms_stats.set_global_prefs(pname => 'ENABLE_TOP_FREQ_HISTOGRAMS', pvalue => 0); END; |
WARNING: Even though according to my tests the preferences do what I expect (i.e. disable top-frequency and hybrid histograms), I never used them in a real case and I did not ask Oracle about their usage. Hence, if you want to use them, I would not only test them carefully, but also ask Oracle Support whether their utilization can lead to known problems.
Before setting them, I also adivse you to get their (default) value with a query like the following one:
SELECT dbms_stats.get_prefs('ENABLE_HYBRID_HISTOGRAMS') AS hybrid,
dbms_stats.get_prefs('ENABLE_TOP_FREQ_HISTOGRAMS') AS top_freq
FROM dual |
Too bad, we can’t set it at the table level :(
exec DBMS_STATS.SET_TABLE_PREFS(ownname=>user, tabname=>’MY_TABLE’, pname=>’ENABLE_HYBRID_HISTOGRAMS’,pvalue=>3);
*
ERROR at line 1:
ORA-20001: Invalid input values for pname
[…] version to be current) is to set the optimizer_features_enable parameter to the older version and hope that this stops most of the new features from appearing, letting other customers identify the problems and raise the SRs […]