"You don't necessarily need up to date statistics. You need statistics that are representative of your data."
Graham Wood.i.e. Old statistics are not necessarily bad
"Do you want the optimizer to give you the best performance, or consistent performance?"
Anjo Kolki.e. Changed stats can give changed plans.
consider also Baselines or SQL ProfilesSELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner = '&OWNER'
AND table_name = '&TABLE';
To see how long stats history is retained...
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM dual;
To see availability of historical stats to restore...
SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM dual;
To gather table stats...
exec dbms_stats.gather_table_stats('&OWNER', '&TABLE');
exec dbms_stats.gather_table_stats(ownname => '&OWNER', tabname => '&TABLE', estimate_percent => 100);
exec dbms_stats.gather_table_stats(ownname => '&OWNER', tabname => '&TABLE', estimate_percent => dbms_stats.auto_sample_size);
To gather index stats...
exec dbms_stats.gather_index_stats(ownname => '&OWNER', indname => '&INDEX');
To gather schema stats...
exec dbms_stats.gather_schema_stats(ownname => '&OWNER');
exec dbms_stats.gather_schema_stats(ownname => '&OWNER', options => 'GATHER STALE');
To only gather stale statisticsTo gather statistics for all objects in the database
exec dbms_stats.gather_database_stats(estimate_percent => dbms_stats.auto_sample_size);
To gather statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components
exec dbms_stats.gather_dictionary_stats;
To gather statistics for all fixed objects (dynamic performance tables)
exec dbms_stats.gather_fixed_objects_stats;
To gather statistics for the System Tables and Indexes
exec dbms_stats.gather_system_stats;
Automatic Stats gathering can happen in a couple of ways... via a scheduled job or via an "Autotask"...
To check whether the automatic stats gathering job is enabled...
SELECT owner, job_name, enabled
FROM DBA_SCHEDULER_JOBS
WHERE job_name = 'GATHER_STATS_JOB';
To Enable the automatic stats gathering job...
exec dbms_scheduler.enable('GATHER_STATS_JOB');
To Disable the automatic stats gathering job...
exec dbms_scheduler.disable('GATHER_STATS_JOB');
To check whether the autotask is enabled...
SELECT client_name, status
FROM dba_autotask_client
ORDER BY client_name;
To check when it runs...
SELECT *
FROM dba_autotask_window_clients;
To Disable the autotask...
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
Locking stats prevents new stats being gathered. This is useful if you have a table that has a wide variation in number of rows (perhaps it has a truncate/load/repeat lifecycle). By fixing the stats you prevent the automatic stats gathering job from gathering stats when the table has a low number of rows, which would likely lead to bad plans when the table has a high number of rows.
To lock...
EXEC dbms_stats.lock_table_stats(ownname => '&OWNER', tabname => '&TABLE');
To unlock...
EXEC dbms_stats.unlock_table_stats(ownname => '&OWNER', tabname => '&TABLE');
To Backup Stats...
EXEC dbms_stats.create_stat_table('&OWNER','&STATTAB');
Give you table a meaningful name to help you remember what it contains (perhaps a reference to the schema table name or to an incident number).EXEC dbms_stats.export_table_stats(ownname=>'&OWNER',tabname=>'&TABLE',stattab=>'&STATTAB',cascade=>TRUE);
To Import backed up Stats...
EXEC dbms_stats.import_table_stats(ownname=>'&OWNER',tabname=>'&TABLE',stattab =>'&STATTAB');
EXEC dbms_stats.restore_table_stats ('&OWNER','&TABLE',SYSDATE-1);
To restore stats for a specific table from 24 hours ago. The date can be a derivation of SYSDATE, a string in date format, or a string parsed by the TO_DATE function.EXEC dbms_stats.restore_database_stats(SYSDATE-1);
EXEC dbms_stats.restore_dictionary_stats(SYSDATE-1);
EXEC dbms_stats.restore_fixed_objects_stats(SYSDATE-1);
EXEC dbms_stats.restore_schema_stats('&OWNER', SYSDATE-1);
EXEC dbms_stats.restore_system_stats(SYSDATE-1);
The date can be a derivation of SYSDATE, a string in date format, or a string parsed by the TO_DATE function.You can see the retention period using...
SELECT dbms_stats.get_stats_history_retention FROM DUAL;
Returns the number of days stats are retained forSELECT dbms_stats.get_stats_history_availability FROM DUAL;
Returns the furthest point in time stats can be returned from (taking into account the retention and any purges that have occurred)You can change the retention period using...
EXEC dbms_stats.alter_stats_history_retention(0);
To turn off retention. Old statistics will not be saved. The automatic purge will delete all statistics history.EXEC dbms_stats.alter_stats_history_retention(1);
To disable automatic purge. Statistics history will be kept forever, or until manual purge (note this will eventually have SYSAUX storage implications).EXEC dbms_stats.alter_stats_history_retention(NULL);
Revert to Oracle defaultEXEC dbms_stats.alter_stats_history_retention(10);
Set stats retention to 10 daysEXEC dbms_stats.purge_stats(before_timestamp=>SYSDATE-1)
Purges all statistics older than 1 day oldEXEC dbms_stats.delete_table_stats(ownname=>'&OWNER',tabname=>'&TABLE');