PREFERENCE_OVERRIDES_PARAMETER in Oracle

In Oracle Database, DBAs can enforce standard statistics gathering by setting PREFERENCE_OVERRIDES_PARAMETER to TRUE using DBMS_STATS.SET_GLOBAL_PREFS. This instructs the optimizer to ignore incorrect, hardcoded parameters passed by manual scripts—such as a fixed ESTIMATE_PERCENT—and strictly apply optimized global or table-level preferences like AUTO_SAMPLE_SIZE.”

What is PREFERENCE_OVERRIDES_PARAMETER

• We discussed optimizer statistics preferences in the previous blog post and we know that that got overwritten by the values given explicitly in the dbms_stats procedure
• Oracle Database 12 Release 2 includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER
• When this preference is set to TRUE, it allows preference settings to override DBMS_STATS parameter values

Suppose you want all the users to use AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT. So we can set PREFERENCE_OVERRIDES_PARAMETER to true at the global level. Then whatever users gave, will be ignored. it means that this best-practice setting will be used even if existing manual statistics-gathering procedures use a different parameter setting

Or

Suppose you want to fix the ESTIMATE_PERCENT for a particular table and it should not get changed by the parameter in dbms_stats, then it can be used

How to set at the table level

exec dbms_stats.set_table_prefs(
ownname=>'&owner',
tabname=>'&table_name',
pname=>'PREFERENCE_OVERRIDES_PARAMETER',
pvalue=>'TRUE') ;
end;
/

How to set at the global level

EXEC DBMS_STATS.SET_GLOBAL_PREFS ('PREFERENCE_OVERRIDES_PARAMETER', 'TRUE');

How to find the set value

Global Level
select dbms_stats.get_prefs('PREFERENCE_OVERRIDES_PARAMETER') from dual;
Table level
select dbms_stats.get_prefs('PREFERENCE_OVERRIDES_PARAMETER','SCOTT','EMP') from dual;

I hope you like this article.

How to set table level preference setting
How to set Schema level preference setting
How to delete Optimizer preference
Optimizer statistics preferences in Oracle
How do I drop the histogram on a column and prevent to generate in future