Often, I see that re-gathering fixed objects statistics are one of those post-upgrade tasks that is forgotten – or completed in a wrong way. Here’s a quick way to avoid that.
TL;DR
To ensure that fixed objects statistics are gathered after upgrade, run this in your database after upgrade:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END;',
start_date => SYSDATE+7,
auto_drop => TRUE,
comments => 'Gather fixed objects stats after upgrade - one time'
);
DBMS_SCHEDULER.ENABLE (
name => '"SYS"."GATHER_FIXED_OBJECTS_STATS_ONE_TIME"'
);
END;
/
If you upgrade a CDB run this in all your PDBs and CDB$ROOT. If you upgrade a single PDB, just run this in that specific PDB.
It will create a scheduler job that fires in seven days and gathers fixed objects statistics. Once the job has been started, the job definition will be automatically deleted.
What Is Fixed Objects Statistics
It is important that fixed objects statistics are representative and especially after upgrade they must be re-gathered. A long time ago (in this galaxy, not one far, far away), Maria Colgan made a really good blog post about it. If you want to know more about it, I suggest that you read her blog post.
How Is That Related To Upgrade?
After you have completed the actual database upgrade, there is a significant number of post-upgrade tasks that you have to carry out. There is a whole chapter about it in the Database Upgrade documentation. One of the chapters specifically mention that you have to gather statistics on fixed objects. And the documentation is really clear:
… Oracle strongly recommends that you regather fixed object statistics after you have run representative workloads on Oracle Database.
Normally, this is not a problem because the DBA can just do it right after the upgrade. But …
… Oracle recommends that you do it after the system is in a runtime state, and the most important types of fixed object tables are populated.
So, you should not do this right after the upgrade. The system won’t be in a runtime state or in a warmed-up state. Actually, the database is in the opposite state. It has just been restarted multiple times and there are no representative workloads in the memory structures. Chances are that you could make things even worse.
You must wait until there is a representative workload on the system!
The Solution
The optimal solution is of course that the DBA ensures that these statistics are re-gathered during peak hours or right after. However, this will require some sort of intelligent automation or a DBA that really knows his or her database. These days – with hundreds or thousands of databases under management – that’s hard. And manual tasks have a risk of being forgotten – DBAs are humans after all.
A good solution could be to create a scheduler job that fires a number of days after the upgrade. That should allow for the database to warm up and the memory structures to fill with representative data. It’s not optimal – but for sure it is better than forgetting it.
Databases Are Fun 
Hi Daniel,
why does autoupgrade then do the fixed object stats right after the upgrade as part of the POSTFIXUPS (and consume a considerable amount of time for it). That sounds like it would be better to disable it there (is that possible?) and implement your job instead.
Thx
Robert
LikeLike
Hi Robert,
That’s a very good observation. And you are absolutely right. In a coming version of AutoUpgrade it will no longer re-gather fixed objects statistics right after upgrade.
Regards,
Daniel
LikeLike
Hi Daniel,
does “soon” mean next version, or it that more in the distance? Can I disable that right now in autoupgrade? I have varying amounts of time taken for the fixed object stats during my upgrade tests on the very same database (POSTFIXUPS vary from 53 to 131 minutes). If that time is no longer required to be part of the downtime it would be easier to predict the required downtime length, as all the other autoupgrade steps always take similar amounts of time.
Thx
Robert
LikeLike
Hi Robert,
It won’t be part of the next release of AutoUpgrade. The developers are just making the last finishing touches.
You can disable the re-gathering of fixed objects stats if you want. You can find more information in the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/how-to-override-default-fixups.html#GUID-4D4F8626-6C05-4D8F-BD90-BCEBA829773B
And in this blog post by Mike Dietrich:
https://mikedietrichde.com/2020/07/22/how-to-skip-a-fixup-in-autoupgrade/
The fixup that you want to disable is called “POST_FIXED_OBJECTS” in the “POSTCHECKS” stage.
It does sound like way too much that your POSTFIXUPS phase is taking up to 131 minutes. If there is enough CPU resources it should finish much faster. Do you have any idea exactly what it is doing that takes so much time?
Regards,
Daniel
LikeLike
Hi Daniel,
my database is multitenant and has 44 PDBs, so those stats will take some while and it would be ok if that time would be more or less constant.
Whats consuming so much different amounts of time are just the stats on CDB$ROOT:
[…]
2020-09-15 18:03:51.558 INFO FixUp [POST_FIXED_OBJECTS][PROVLOG_TS] elapsed 325 seconds – FixUpTrigger.executeFixUp
2020-09-15 18:04:39.552 INFO FixUp [POST_FIXED_OBJECTS][PKITBI_QA] elapsed 325 seconds – FixUpTrigger.executeFixUp
2020-09-15 18:06:22.542 INFO FixUp [POST_FIXED_OBJECTS][WOWI_DS] elapsed 312 seconds – FixUpTrigger.executeFixUp
2020-09-15 19:29:55.988 INFO FixUp [POST_FIXED_OBJECTS][CDB$ROOT] elapsed 5,369 seconds – FixUpTrigger.executeFixUp
[…]
The PDBs are typically in the range of 180 up to 360 seconds each.
The (physical) server is doing nothing else then the upgrade, so there is nobody stealing cpu. IO is not an issue as well. If you have an idea … would be nice. But I will check how to disable that stuff and do it the way you suggested here.
Thx.
Robert
LikeLike
Hello Daniel,
I am running exec dbms_stats.gather_fixed_objects_stats on a small database and very surprised that is taking 17 hours and still running?
Any ideas?
Regards,
Jorge
LikeLike
Hi Jorge,
That doesn’t sound right. Fixed objects stats should finish fairly quickly. It sounds like there is a lock of some kind preventing the gathering from completing.
Regards,
Daniel
LikeLike
Hello Daniel,
Update….it took 20 hours to complete, but I think you were correct. I am running Oracle on Windows and using Data Guard, so as a test, I failed over to my standby, rebooted my primary, failed back to my primary and reran dbms_stats.gather_fixed_objects_stats and completed in 30 minutes.
Another question, I have a query that takes more than 45 minutes to complete. SELECT * from (select * from (select a.*, rownum DefaultSortColumn from(v_report_summary) a));
and using /*+ ALL_ROWS */, takes less than 12 seconds, most of the info I found, said not to use /*+ ALL_ROWS */ in an OLTP.
Do you see any issue if I use /*+ ALL_ROWS */?
thanks
regards,
jorge
LikeLike
Hi Jorge,
I am glad that your issue got resolved. Although, I still think 30 minutes for gathering fixed objects stats sounds like too long.
Regarding tuning your query, you shouldn’t really ask me. My SQL tuning skills are not very advanced (politely speaking). I suggest that you post on asktom.oracle.com instead. Chris and Connor are super skilled and helpful.
Regards,
Daniel
LikeLike
Thanks Daniel
LikeLike