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