What are optimizer statistics preferences?
- The statistics preferences of the optimizer determine the preset values of the parameters used by automatic statistics collection and the DBMS_STATS statistics gathering processes.
- We can configure optimizer information at the table, schema, database (all tables), and global levels.
- A global preference refers to tables with no preferences as well as any future tables made.
- This allows you to automatically keep optimizer statistics when some objects require settings that differ from the default. This gives you more influence over how Oracle Database collects statistics.
What are the Preferences available?
Preferences differ by version
For 19c
How to set the optimizer statistics preferences?
This is set using dbms_stats.SET_*_PREFS
Examples
EXEC DBMS_STATS.SET_GLOBAL_PREFS ('estimate_percent', '5');
EXEC DBMS_STATS.SET_DATABASE_PREFS
('incremental', 'false');
EXEC DBMS_STATS.SET_SCHEMA_PREFS ('estimate_percent', 'SCOTT','12');
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMP', 'STALE_PERCENT', '10');
I hope you like this article on Optimizer statistics preferences in Oracle
Related Articles
How to delete Optimizer preference
How to set table level preference
How to check optimizer statistics preferences at the table level
PREFERENCE_OVERRIDES_PARAMETER in Oracle
How to set Schema level preference