Gathering fixed object stats may reduce recompilation time post Oracle upgrade

Mike Dietrich:

In larger sized databases with many objects and components our recommendation is always to gather first fixed object stats prior to start the recompilation. Some time ago I’ve learned from to very different customer database projects that these stats will speed up the efficient job creation for recompilation. And last week I’ve got this feedback from an EBS 9.2.0.8 to 11.2.0.2 upgrade project:

• Approx 120,000 objects invalid post database upgrade
• Recompilation without fixed object stats: 14:44 hrs
• Recompilation with fixed object stats: 12:09 hrs
  Time it took to gather fixed object stats: 00:07 hrs
• Benefit: 7 minutes to gather fixed object stats decreased the recompilation time by 2:35hrs
  (or by 18%)
  
  
• How to gather fixed object stats in Oracle 11g:
  SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

Filed in Oracle, Tips on 21 Jun 11

---