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.
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.
