Disable awr snapshot before upgrade

[satish]

Dear erman,

We are in process of upgrading 11.1.0.7 to 11.2.0.4.
gather dictionary statistics running for 5 hours in our database?
Can we run this 1 day before the downtime window?

Thanks,
Satish

[satish]

Dear erman,

Actual scenario.

We are in process of upgrading 11.1.0.7 to 11.2.0.4.
As a pre upgrade step,gather dictionary statistics running for 5 hours in our database?
Can we run this 1 day before the downtime window?

Thanks,
Satish

[ErmanArslansOracleBlog]

Maybe as last resort, but we don't want to do anything to affect production performance and we don't want to do this by going beyond the limits, beyond the documentation I mean.. Even for 1 day.. Even 1 day before the upgrade. That's why we got to try to fix it first..

For instance; did you try to run it in parallel?

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ( options => 'GATHER STALE', DEGREE => 16);

[satish]

Thanks erman.

This is ebs database.During downtime,can we run this command?We have 4 core cpu dual processor.

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ( options => 'GATHER STALE', DEGREE => 16);

Can we use Gather Stale or AUTO.Since we are moving to prod next week,we just want to make sure.

Thanks you,
Satish

[ErmanArslansOracleBlog]

Decrease the degree to 8 or 4 (test it on your TEST Environment)
You are doing it before the upgrade; in the old version Oracle DB right?, Just use GATHER_STALE and parallel degree and see if it helps.. Again check it on your TEST env first.

[satish]

Yes erman,we are running in 11.1.0.7 database.By default,how many parallel does the script use?Any idea how to check.

[ErmanArslansOracleBlog]

It is the table's default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement.

See ->Database PL/SQL Packages and Types Reference -> GATHER_DICTIONARY_STATS Procedure Parameters