Document for exporting existing STATSISTICS before Gathering Statistics:

Metalink Reference:

Complete Checklist for Manual Upgrades to 10gR2
Note:316889.1

This Document is to export the existing statistics before gathering statistics. So that if we no need for the latest statistics we can delete the existing stats and import the old statistics.

This is Recommended for 4 users to run stats while Upgrading for 10g R1 to R2 . They are as follows:

Gather schema stats analyse:
======================

1. SYS
2. ODM
3. OLAPSYS
4. MDSYS

Step 1: Now take backup of existing stats for the following four users :
====================================================


1.sqlplus /as sysdba
2.spool sdict
3. grant analyze any to sys;
4.exec dbms_stats.create_stat_table('SYS','dictstattab')

Step 2: Now run the as the stats table package is created now we are going to export the existing stats for the four users by:
==================================================================================================

exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS')
exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS')
exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS')
exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS')

Step 3: To run the Gather statistics :
============================

1. sqlplus '/as sysdba'
2. spool gdict
3. grant analyze any to sys;

exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

spool off;


step 4 : OPTIONAL (ONLY WHEN WE NEED THE OLD STATISTICS
===================================================

If we want to have this existing statistics (ie) old statistics which we have before running the above gather stats , We need to do the below steps:

1. Delete the analysed schema by using :

exec dbms_stats.delete_schema_stats('SYS')

2. Import the above exported stats for that four users by

exec dbms_stats.import_schema_stats('SYS','dictstattab');