oracle how to gather stats, backup them and use the backed up stats

Oracle stats are used by optimiser to determine the query plan. Query plan is how the query will be executed by oracle.

Plan determine if the query will use full table scan of the table / use indexes / use hash join / nested loops etc to get the final outcome from the query. All of this depends on what the stats are and stats will hold the number of the rows in table, cardinality, size of data and many more.

It is not always good idea to gather stats on tables all the time something they may change the query plan which might not be optimal for your scenario. So if you want to gather stats then always a good idea to export the stats of that table and then gather stats.

In this post I will discuss on how to export and import stats.

Steps:
1. Create a table which will store the exported stats
2. Grant permission to use this table
3. Export the stats (schema , table, index etc. )
4. Gather new stats on object
5. If required import stats back from exported table

Type of exports you can do in oracle 11g
EXPORT_COLUMN_STATS
EXPORT_DATABASE_STATS
EXPORT_DICTIONARY_STATS
EXPORT_FIXED_OBJECTS_STATS
EXPORT_INDEX_STATS
EXPORT_SCHEMA_STATS
EXPORT_SYSTEM_STATS
EXPORT_TABLE_STATS

Check Current statistics:

select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where TABLE_NAME='EMP';

Create stats table:

EXEC DBMS_STATS.CREATE_STAT_TABLE (ownname=>'HDHILLON', stattab=>'STATS_SCHEMA_EMP', tblspace=>'MICHAEL'); 

Grant Permissions:

GRANT SELECT,INSERT,UPDATE,DELETE on CSU.STATS_SCHEMA_EMP TO PUBLIC;

Export statistics:

exec dbms_stats.export_schema_stats (ownname =>'HDHILLON',stattab=>'STATS_SCHEMA_EMP'); 

Gather stats for HDHILLON.EMP table:

execute dbms_stats.gather_table_stats(ownname=>'HDHILLON', tabname=>'EMP', estimate_percent => 10, method_opt => 'for all columns size skewonly',  cascade=> true);

Check new statistics:

select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where TABLE_NAME='EMP';

Importing previously backed up stats:

exec dbms_stats.import_table_stats ('HDHILLON', 'EMP', stattab=>'STATS_SCHEMA_EMP'); 

Import procedures which can be used are:

IMPORT_COLUMN_STATS 

IMPORT_DATABASE_STATS 

IMPORT_DICTIONARY_STATS 

IMPORT_FIXED_OBJECTS_STATS 

IMPORT_INDEX_STATS 

IMPORT_SCHEMA_STATS 

IMPORT_SYSTEM_STATS 

IMPORT_TABLE_STATS 

Example of exporting table stats is:

exec DBMS_STATS.EXPORT_TABLE_STATS (ownname =>'HDHILLON', tabname => 'EMP', stattab=>'STATS_SCHEMA_EMP' ); 

------------------------

Export fixed object stats

-----------------------------

exec dbms_stats.create_stat_table('HDHILLON','HARVEY_STATS_TABLE','USERS');


select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       950

SQL>

SQL> select * from HARVEY_STATS_TABLE;

no rows selected

SQL>

 SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       117
2015-Feb-20                    833

SQL>

exec dbms_stats.export_fixed_objects_stats(stattab=>'HARVEY_STATS_TABLE',statown=>'HDHILLON');

SQL> select count(*) from HARVEY_STATS_TABLE;

  COUNT(*)

----------

     23578

SQL> 

------------------
Import fixed object stats
------------------
select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       117
2015-Feb-20                    833

SQL>

exec dbms_stats.delete_fixed_objects_stats();

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS

-------------------- -------------

NO STATS                       950

SQL> 

exec dbms_stats.import_fixed_objects_stats(stattab=>'HARVEY_STATS_TABLE',statown=>'HDHILLON');

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED        FIXED_OBJECTS
-------------------- -------------
NO STATS                       117
2015-Feb-20                    833

SQL>

Ever want to drop the stats table

SQL> exec  dbms_stats.drop_stat_table('HDHILLON','HARVEY_STATS_TABLE');

Syntax:

DBMS_STATS.DROP_STAT_TABLE (
   ownname VARCHAR2, 
   stattab VARCHAR2);