1) DBMS_STATS.CREATE_STAT_TABLE
This will create a statistics table in the schema specified.
· ownname – Schema where the table can be created.
· stattab - statistics table name.
· tblspace - tablespace to be used.
exec dbms_stats.create_stat_table (
ownname=>'OWNER',
stattab=>'STAT_TAB',
tblspace=>'USER');
2) DBMS_STATS.EXPORT_TABLE_STATS
This method exports the statistics of the table specified.
ownname - schema name
tabname - table name
stattab - stat table name
cascade - TRUE = indexes too
statown - stat table schema
exec dbms_stats.export_table_stats (
ownname =>'OWNER',
tabname =>'TABLE_NAME',
stattab =>'STAT_TAB',
cascade =>true);
3) DBMS_STATS.EXPORT_SCHEMA_STATS
This method exports the statistics of the SCHEMA specified.
ownname - schema name
stattab - stat table name
statown - stat table schema
exec dbms_stats.export_schema_stats(
ownname=>’OWNER’,
stattab=>'STAT_TAB');
4) DBMS_STATS.EXPORT_DATABASE_STATS
This method exports the statistics of the entire database.
stattab - stat table name
statid - optional identifier
statown - stat table schema
exec dbms_stats.export_database_stats(
stattab=>'STAT_TAB');
Ø dbms_stats.export_dictionary_stats – Retrieves statistics for all dictionary schemas (SYS and SYSTEM)
Ø dbms_stats.export_fixed_object_stats- Retrieves statistics for fixed tables
Ø dbms_stats.export_system_stats - Retrieves and stores system statistics
The STAT_TAB created will hold the database/schema/table statistics in the form of an oracle table. If we wish to keep it safe outside of the database, use the traditional export(exp utility) to export the contents of the same.
exp user/****** tables=STAT_TAB file=stat_tab.dmp
Now, we will see the methods and options available on how to import the statistics.
Before importing any statistics from a table or restoring them back from database, it is recommended to delete the existing statistics using DBMS_STATS.DELETE_TABLE_STATS package.
II. Deleting Statistics
This method deletes particular statistics from the user statistics table identified by stattab and stores them in the dictionary.
ownname - schema name
tabname - table name
exec dbms_stats.delete_table_stats(
ownname =>'OWNER',
tabname =>'TABLE_NAME');
Following methods are available for deleting statistics on schema/dictionary/database level.
exec dbms_stats.delete_schema_stats(OWNER);
exec dbms_stats.delete_dictionary_stats;
exec dbms_stats.delete_fixed_object_stats;
exec dbms_stats.delete_database_stats;
III. Import Statistics
1) DBMS_STATS.IMPORT_TABLE_STATS
This method imports particular table statistics from the user statistics table identified by stattab and stores them in the dictionary.
ownname - schema name
tabname - table name
partname - partition name
stattab - stat table name
statid - optional identifier
cascade - TRUE = indexes too
statown - stat table schema
exec dbms_stats.import_table_stats(
ownname =>'OWNER',
tabname =>'TABLE_NAME',
stattab =>'STAT_TAB',
cascade =>true);
2) DBMS_STATS.IMPORT_SCHEMA_STATS
This method imports the statistics of the SCHEMA specified.
ownname - schema name
stattab - stat table name
statown - stat table schema
exec dbms_stats.import_schema_stats(
ownname=>’OWNER’,
stattab=>’STAT_TAB’);
3) DBMS_STATS.IMPORT_DATABASE_STATS
This method imports the statistics of the entire database.
stattab - stat table name
statid - optional identifier
statown - stat table schema
exec dbms_stats.import_database_stats(
stattab=>'STAT_TAB');
Ø dbms_stats.import_dictionary_stats – Retrieves statistics for all dictionary schemas (SYS and SYSTEM)
Ø dbms_stats.import_fixed_object_stats- Retrieves statistics for fixed tables
Ø dbms_stats.import_system_stats - Retrieves and stores system statistics
Now, we will see the options available on how to restore statistics from the database, within the stats retention period.
V. Restoring Database Statistics
The old statistics are retained at the database level and if there’s any problem with performance of queries, the same can be restored. By default, the statistics are retained at the database level for 31 days.
To see the retention period, the below SQL will return the current retention period(in days) for the stats.
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
The below SQL will return the oldest possible stats date availability and can be restored.
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------
08-SEP-08 12.47.26.580621000 AM -04:00
To find the history of statistics availability for a table,
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history
where table_name='
