Oracle Optimizer Statistics

"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 Kolk

i.e. Changed stats can give changed plans.

consider also Baselines or SQL Profiles

Check when Stats were Gathered

A stattype_locked of ALL suggests that the stats are locked (using dbms_stats.lock_table_stats)
A value of YES in stale_stats indicates that more than 10% of the rows have changed since the stats were last gathered.

Stats History

SELECT 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;

Manual Stats Gathering

Examples cover common scenarios. For full details see Oracle docs or Morgan's Library.

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 statistics

To 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

Automatic Stats gathering can happen in a couple of ways... via a scheduled job or via an "Autotask"...

Job

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');

AutoTask

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/Unlocking Stats

Also consider SQL Baselines or SQL Profiles.

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 => 'MYOWNER', tabname => 'MYTABLE');

To unlock...

EXEC dbms_stats.unlock_table_stats(ownname => 'MYOWNER', tabname => 'MYTABLE');

Statistics Export/Import

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');

Statistics Restore

By default, since 10g, Oracle keeps a backup of changed statistics for 31 days, which you can restore if necessary.

Restore

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.

Retention Period

You can see the retention period using...

SELECT dbms_stats.get_stats_history_retention FROM DUAL;

Returns the number of days stats are retained for

SELECT 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 default

EXEC dbms_stats.alter_stats_history_retention(10);

Set stats retention to 10 days

Purge

EXEC dbms_stats.purge_stats(before_timestamp=>SYSDATE-1)

Purges all statistics older than 1 day old

Delete Stats

EXEC dbms_stats.delete_table_stats(ownname=>'MYOWNER',tabname=>'MYTABLE');