Object Statistics - Troubleshooting Oracle Performance - page 256

dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL)

■ As of version 11.2, setting the job_queue_processes initialization parameter to 0 disables the automatic
statistics job (and everything else scheduled through the Scheduler).

Caution

Restoring Object Statistics
Whenever object statistics are gathered through the dbms_stats package or, as of version 11.2, through the ALTER
INDEX statement, instead of simply overwriting current statistics with the new statistics, the current statistics are saved
in other data dictionary tables that keep a history of all changes occurring within a retention period. The purpose is to
be able to restore old statistics in case new statistics lead to inefficient execution plans.
Object statistics (as well as system statistics, because they're maintained by the same underlying functionality)
are kept in the history for an interval specified by a retention period. The default value is 31 days. You can display the
current value by calling the get_stats_history_retention function in the dbms_stats package, as shown here:

SELECT dbms_stats.get_stats_history_retention() AS retention FROM dual

To change the retention period, the dbms_stats package provides the alter_stats_history_retention
procedure. Here's an example where the call sets the retention period to 14 days:

dbms_stats.alter_stats_history_retention(retention => 14)

Note that with the alter_stats_history_retention procedure, the following values have a special meaning:
• NULL sets the retention period to the default value.

• 0 disables the history.

•
When the statistics_level initialization parameter is set to typical (the default value) or all , statistics older
than the retention period are automatically purged. Whenever manual purging is necessary, the dbms_stats package
provides the purge_stats procedure. The following call purges all statistics placed in the history more than 14 days ago:

-1 disables the purging of the history.

dbms_stats.purge_stats(before_timestamp => systimestamp - INTERVAL '14' DAY)

To execute the alter_stats_history_retention and purge_stats procedures, you need to have the analyze
any and analyze any dictionary system privileges.
If you're interested in knowing when object statistics for a given table were modified, the user_tab_stats_
history data dictionary view provides all the necessary information. Of course, there are dba , all and, in a 12.1
multitenant environment, cdb versions of that view as well. Here's an example. With the following query, it's possible
to display when the object statistics of the tab$ table in the sys schema were modified:

SQL> SELECT stats_update_time
2 FROM dba_tab_stats_history
3 WHERE owner = 'SYS' and table_name = 'TAB$';