Database contains stale optimizer statistics – preupgrade check – workaround

Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.2
This problem can occur on any platform.
Symptoms
When running the pre-upgrade script utlu102i.sql , the following warning occurs :

WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 10g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:

…. SYS
…. OLAPSYS

This warning persists even after you run :
.
For 10G: SQL>exec dbms_stats.gather_dictionary_stats
For 9.2 : SQL>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);
Cause
This is an issue which was reported internally.You can use the following workaround.

Solution

* Gather statistics in the original release prior to running the DBUA or running the utlu102i.sql script
* You can do the same by using the following command :

SQL> exec dbms_stats.gather_schema_stats(”,options=>’GATHER STALE’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>’FOR ALL COLUMNS SIZE AUTO’,cascade=>TRUE);

Replace the ” with the name of the schema where the stale statistics are reported.

For Eg :
For ‘SYS’ schema :

SQL> exec dbms_stats.gather_schema_stats(‘SYS’,options=>’GATHER STALE’,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>’FOR ALL COLUMNS SIZE AUTO’, cascade=>TRUE);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: