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 [This will change with Oracle 12c where it is part of the Auto Stats Gathering job].
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 220.127.116.11 to 18.104.22.168 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.
I was in one of your seminar in Toronto, very good workshop. Thanks.
One question for gathering fixed object stats – is there any costumer experience bad impact of gathering fixed object stats, especially in EBS env? How do I check if the database was gather fixed object stats before? My concern is that I am not sure if the optimizer could act differently for day to day transaction, especially EBS env.
Other performance we faced during upgrade is statement ‘analyze table "SOURCE$" validate structure cascade;’ This statement took 30 minutes when we did first EBS upgrade and 9 hours on second EBS env. Did you even encounter the same for other customizer? Any suggestion on how to improve the performance of this analyze?
thanks for your comment and your questions.
To check if fixed object stats have been created you can check: select count(*) from sys.tab_stats$;
Take not of the result. Then:
Now compare another select count(*) from sys.tab_stats$;
result with the result from before.
And DBMS_STATS has also a DELETE_FIXED_OBJECTS_STATS procedure which you could use to take them away again.
One reason for the ANALYTE VALIDATE STRUCTURE CASCADE on SOURCE$ might be missing stats, but it could also have several other reasons (such as a bad plan for recursive statements etc.). I would have to see the execution plan – without that it won’t be possible to say something serious.
So far I had just good experiences with fixed object stats but my experience with EBS environment is a bit limited. You might open an SR with Oracle Support to doublecheck as they have EBS experts. And sometime EBS requires stats creation in a different way.