Monday May 30, 2011

Will gathering fixed object stats reduce recompilation time post upgrade?

Interesting question, isn't it?
Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered?

First of all fixed object stats on X$-tables won't be gathered by default. X$ structures are undocumented. V$ views are build on top of them and should only be used even though it might be sometimes useful to access X$ tables such as X$BH (buffer headers - contains information describing the current contents of a piece of the buffer cache) sometimes directly. 

Anyway, back to the upgrade topic. Post upgrade you'll have a good bunch of invalid objects in your database. You would start now the recompilation with either utlrp.sql or, to decrease CPU load with utlrprp <number> to recompile with a distinct <number> of parallel threads. Since Oracle 10g we are using the package UTL_RECOMP for recompilation taking the object dependencies into consideration. Therefore jobs will be created to run the recompilation tasks in parallel and leverage from the CPU power of multiple cores. In both cases you'll get 4 queries to monitor progress and jobs as script output displayed.

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 (yes, not minutes ... hours!)
  • 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;

It's always worth a try - you won't see a dramatic effect on a small ORCL type database with some thousand tables, indexes and views. But in a Siebel, Peoplesoft, EBS or any other huge application nvironment the effect might be remarkable. Still I agree in this EBS case: 12 hours recompilation time is a lot. The only way to decrease this time will be the (temporary) addition of CPU power to the system to satisfy more parallel recompilation threads.

About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
18
19
20
21
22
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers