How to do backup and Restore of Oracle statistics

Optimizer statistics backup and restore:

1)            Create stat table from source database.

EXEC DBMS_STATS.create_stat_table('SYS',' STATS_070509');

2)            Export stats to stats table created in step#1 

EXEC DBMS_STATS.export_schema_stats('SYS','STATS_070509',NULL,'DBASCHEMA');

This table can be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:

                EXEC DBMS_STATS.import_schema_stats('SYS','STATS_070509',NULL,'DBASCHEMA');

You can run into issue with the statistics for hidden columns.  When we create INDEXES on column expressions (hidden columns like decode (column1,…)) ,  Oracle create system generated name like sys_100036 for each column expression.

Example :

SQL> select index_name,column_expression from dba_ind_expressions  where table_owner like ‘MY_APP’ and table_name like 'AN_FILE'  and index_name IN ('AN_FILE_N1','AN_FILE_N4');

INDEX_NAME                                    COLUMN_EXPRESSION

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

AN_FILE_N1                                       UPPER("FILE_EXTERNAL_ID")

AN_FILE_N4                                       TRUNC("ADJ_FINALIZE_DTS")

SQL> select index_name,column_name from dba_ind_columns where table_owner like ‘MY_APP’and table_name like 'AN_FILE'  and indeX_name IN ('AN_FILE_N1','AN_FILE_N4');

INDEX_NAME                                    COLUMN_NAME

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

AN_FILE_N1                                       SYS_NC00037$

AN_FILE_N4                                       SYS_NC00038$

As the hidden column names are system-generated as shown above, oracle will give them different names in different databases based on whatever random number is available at the time of index creation on hidden column in that database.  When we import the exported statistics as explained in step#3 above  and  if oracle does not find same name for hidden column of the index in destination database  as it is mentioned in the exported statistics of index or in the source database ,then import_stats command silently skips the import of histogram information or statistics information on the hidden column of the index. This will cause difference in statistics and histogram information for that column or index.

Example :

SQL> select table_name,column_name,histogram from dba_tab_cols

  where owner like ‘MY_APP’   and (hidden_column='YES' or virtual_column= 'YES') order by table_name,column_name  ;

Production database:

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM

AN_TITLE_FEE                       SYS_NC00045$                     FREQUENCY

QA database:

TABLE_NAME                     COLUMN_NAME                    HISTOGRAM

AN_TITLE_FEE                       SYS_NC00051$                   FREQUENCY

From above-mentioned outputs , it seems that  production has hidden column name 'SYS_NC00045$  and Load test has a name called “SYS_NC00051$”  for same hidden column.

SQL> select index_name,column_name from dba_ind_columns where table_owner like ‘MY_APP’and table_name like 'AN_TITLE_FEE'  and column_name  like ‘'SYS_NC00045$ ‘

INDEX_NAME                                    COLUMN_NAME

AN_TITLE_FEE_N4                           SYS_NC00045$

AN_TITLE_FEE_N9                           SYS_NC00045$

SQL> select index_name,column_name from dba_ind_columns where table_owner like ‘MY_APP’and table_name like 'AN_TITLE_FEE'  and column_name  like ‘'SYS_NC00051$ ‘

INDEX_NAME                                    COLUMN_NAME

AN_TITLE_FEE_N9                           SYS_NC00051$

AN_TITLE_FEE_N4                           SYS_NC00051$

Now you can update the stats table to correct the hidden column name

SQL> update MY_APP.stats_070509 set c4='SYS_NC00051$' where c1 like 'AN_TITLE_FEE' and c4 like 'SYS_NC00045$';

SQL> commit;

Then import the statistics in QA environment  or any destination environment

EXEC DBMS_STATS.import_schema_stats('SYS','STATS_070509',NULL,'DBASCHEMA');