Gather Stats to rescue yourself after Migrating to 11.2.0.4

This  was a scenario which was  faced by our team at the customer site

Oracle Database migration was planned  with OS Up gradation.

Old Database was 10g and runnig on AIX 6.1

New Database is 11g running on AIX 7.1

After migrating to 11.2.0.4  there were performance degradation.

work around carried out :

optimizer_features_enabled to 10.2.0.1

Alter system set optimizer_features_enabled=10.2.0.1 scope=both;

After altering to this Some queries which were taken too long time were run in quick time.

Lets say if query take  10S in 10.2.0,4 

That query take 40S in 11.2.0.4 

after setting optimizer_features_enabled to 10.2.0.1 it takes only 15S.

This rescue me for 2 days the performance issue raised  again.

After that again statistics were gathered and created all indexes online.

exec dbms_stats.gather_system_stats();

exec dbms_stats.gather_dictionary_stats

exec dbms_stats.gather_fixed_objects_stats();

exec dbms_stats.gather stats();

Rebuild all the Indexes  

 select OWNER,count(OBJECT_TYPE) from dba_objects where  OBJECT_TYPE='INDEX' group by owner

select 'ALTER INDEX ' || owner||'.'||index_name || ' rebuild online nologging;' from dba_indexes

where owner ='&OWNER';

This will give the  Alter Statements for Rebuild online INDEX.

Now the performance looks good ..!! :)

You can check when statistics were gathered last.

SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS
LAST; --Tables.
SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS
LAST; -- Indexes.