原文地址：https://dohdatabase.com/2020/09/14/how-to-gather-fixed-object-statistics-after-upgrade/
原文作者：Daniel Overby Hansen
我经常会看到做为升级后工作之一的重新收集fixed objects统计信息被遗忘了，或者是用完全错误的方式进行。这里给大家一个避免该问题的快捷方法。
精华：
为确保升级后，fixed objects的统计信息被收集，请升级后，在数据库上运行以下代码：
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;
/
如果升级的是CDB，要在全部PDB和CDB$ROOT下运行，如果升级的是单个PDB，则只在该指定PDB上运行。
这会创建一个计划任务，其会在7天后启动并收集fixed objects的统计信息。一旦该任务运行过了，则该任务会被自动删除。
什么是fixed objects统计信息
有代表性的fixed objects统计信息是重要的，特别是在升级后，是需要重新收集的。很早以前（在这个星球上，还不算太远），Maria Colgan写了一个有关于此的很棒的博文。如果你想了解更多，我建议你读一下她的博文。
它和升级是何关系？
在你完成现实中数据库的升级后，有大量的升级后任务需要你去运行。在数据库升级文档中，用了一整章来说明，其中特别提及必须在fixed objects上收集统计信息。而且文档清晰的写明：
Oracle强烈建议在数据库上运行了典型的工作负载后，收集fixed objects的统计信息。
一般来说，DBA都可以在升级后正确的执行它，所以，这不是个问题。但是
Oracle建议在系统处于运行状态时执行它，并且，绝大多数的重要种类的fixed objects表中的数据已经产生了。
因此，你可能在升级后没能正确的操作。系统并不处于运行状态，实际上，是处于一种相反的状态–数据库预热状态。它已经重启了多次，内存结构中（译者注：指x$打头的表）也没有典型的工作负载。这些变化会让事情变得更糟。
你必须等到系统中有了典型的工作负载！
解决方案
当然，最佳的方案是DBA确保在负载峰值期间，或者在这之后重新收集这些统计信息。然而，这需要某种自动化的智能处理或者DBA真正了解他（她）的数据库。这对于管理着成百上千个数据库的当下，这是很困难的。手工操作还存在被遗忘的风险–DBA毕竟是人。
创建一个计划任务，在升级后的数天后启动是一个好方案。这会使数据库预热并用代表性的数据填充内存结构。虽然这不是最好的方案，但总比忘记了它要好。
附原文：
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.
