We did a full migration sometime last week.
The migration is for server,storage and database.
I used Oracle Data Guard Physical Standby switchover method for the migration.
After the migration, few of the SQL performance is degraded. The sql on the largest table (350GB) is slower compared to old system.
The SQL usually complete in less than 1 second. Now it takes up to 30 seconds.
The execution plan is same for old and new system. Statistics are up to date for the table as well.
The query is using index for the select.
I did a trace for the sql on both old and new system and I can see the slowness are on FETCH part.
The new server and storage are faster compared to old system.
The SGA size was 7GB on old system and 20GB on new system.
The difference between both the system are old system used VxFS and new system using ZFS.
Will re-org of the table will improve the performance?
I have already rebuild the index and the performance is still same.
We are clueless on what should we do after this.