Oracle 23c New Features for DBA'sStatistics gathering until 18c was on daily/weekly basis based on the maintainance tasks defined. Say 10PM every day with 8 hour window on weekdays and 24 hrs window on week ends. And how many times we have seen statistics have become stale between two collections and DBA's has to manually intervene and collect stats.
From 19c onwards, High-frequency automatic optimizer statistics collection complements the standard statistics collection job. By default, when set to on, the collection occurs every 15 minutes, meaning that statistics have less time in which to be stale.
To Enable high frequency stats collection
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’);
For, Maximum Run duration of each run,
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600’);
To Specify the frequency of auto stats collection in seconds
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240')
Happy Learning
Suresh
Follow Me!!!