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.
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 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.