原文地址：https://dohdatabase.com/2020/09/14/how-to-gather-fixed-object-statistics-after-upgrade/
原文作者：Daniel Overby Hansen

我經(jīng)常會看到做為升級后工作之一的重新收集fixed objects統(tǒng)計信息被遺忘了，或者是用完全錯誤的方式進(jìn)行。這里給大家一個避免該問題的快捷方法。

精華：

為確保升級后，fixed objects的統(tǒng)計信息被收集，請升級后，在數(shù)據(jù)庫上運(yùn)行以下代碼：

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下運(yùn)行，如果升級的是單個PDB，則只在該指定PDB上運(yùn)行。

這會創(chuàng)建一個計劃任務(wù)，其會在7天后啟動并收集fixed objects的統(tǒng)計信息。一旦該任務(wù)運(yùn)行過了，則該任務(wù)會被自動刪除。

什么是fixed objects統(tǒng)計信息

有代表性的fixed objects統(tǒng)計信息是重要的，特別是在升級后，是需要重新收集的。很早以前（在這個星球上，還不算太遠(yuǎn)），Maria Colgan寫了一個有關(guān)于此的很棒的博文。如果你想了解更多，我建議你讀一下她的博文。

它和升級是何關(guān)系？

在你完成現(xiàn)實(shí)中數(shù)據(jù)庫的升級后，有大量的升級后任務(wù)需要你去運(yùn)行。在數(shù)據(jù)庫升級文檔中，用了一整章來說明，其中特別提及必須在fixed objects上收集統(tǒng)計信息。而且文檔清晰的寫明：

Oracle強(qiáng)烈建議在數(shù)據(jù)庫上運(yùn)行了典型的工作負(fù)載后，收集fixed objects的統(tǒng)計信息。

一般來說，DBA都可以在升級后正確的執(zhí)行它，所以，這不是個問題。但是

Oracle建議在系統(tǒng)處于運(yùn)行狀態(tài)時執(zhí)行它，并且，絕大多數(shù)的重要種類的fixed objects表中的數(shù)據(jù)已經(jīng)產(chǎn)生了。

因此，你可能在升級后沒能正確的操作。系統(tǒng)并不處于運(yùn)行狀態(tài)，實(shí)際上，是處于一種相反的狀態(tài)–數(shù)據(jù)庫預(yù)熱狀態(tài)。它已經(jīng)重啟了多次，內(nèi)存結(jié)構(gòu)中（譯者注：指x$打頭的表）也沒有典型的工作負(fù)載。這些變化會讓事情變得更糟。
你必須等到系統(tǒng)中有了典型的工作負(fù)載！

解決方案

當(dāng)然，最佳的方案是DBA確保在負(fù)載峰值期間，或者在這之后重新收集這些統(tǒng)計信息。然而，這需要某種自動化的智能處理或者DBA真正了解他（她）的數(shù)據(jù)庫。這對于管理著成百上千個數(shù)據(jù)庫的當(dāng)下，這是很困難的。手工操作還存在被遺忘的風(fēng)險–DBA畢竟是人。

創(chuàng)建一個計劃任務(wù)，在升級后的數(shù)天后啟動是一個好方案。這會使數(shù)據(jù)庫預(yù)熱并用代表性的數(shù)據(jù)填充內(nèi)存結(jié)構(gòu)。雖然這不是最好的方案，但總比忘記了它要好。

附原文：
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.