原文地址：Fixed Objects Statistics and Why They are Important
原文作者：Maria Colgan
Fixed objects 是指"X$"表及其上的索引。Oracle中的"v$"性能視圖就是在X$表之上定義的。由于v$視圖可以像其它用戶表或視圖出現在SQL語句中，那么收集這些表的優化器統計信息就是重要的，以便幫助優化器生成好的執行計劃。但是，與其它數據庫表不同，當SQL語句中涉及的X$表的優化器統計信息缺失時，并不會自動對它們使用動態采樣。如果缺失了統計信息，優化器會使用預定義的默認值。這些默認值可能并不具代表性，而且可能潛在的導致產生一個欠優的執行計劃，這會導致你的系統出現嚴重的性能問題。正是因為這個原因，我們強烈建議你收集fixed objects的統計信息。
在Oracle Database 12c Release 1之前，fixed objects統計信息并不會被自動統計信息收集任務創建和維護。你可以通過使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS來收集fixed objects的統計信息。
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS過程會像DBMS_STATS.GATHER_TABLE_STATS一樣收集除數據塊數之外的統計信息。因為X$表只是內存結構，并且也不存儲在磁盤上，所以，其數據塊數總是被置為0。
你必須具有
ANALYZE ANY DICTIONARY 或者 SYSDBA 權限，或者是 DBA 角色才能更新fixed object統計信息。
由于X$表的瞬態性屬性，在系統中有了代表性負載后收集fixed objects的統計信息是非常重要的。但由于收集統計信息需要額外的資源，所以，在大型系統中這并不總是可行的。如果你不能在峰值負載期間做fixed objects統計信息的收集，那么也應該在系統已完成預熱，并且三種關鍵類型的fixed object表中已產生數據后收集它：
| 結構化數據 | 例如涵蓋了datafiles, controlfile的視圖等 |
|---|---|
| 基于會話的數據 | 例如v$session, v$access等 |
| 工作負載數據 | 例如 v$sql, v$sql_plan等 |
如果你做了一個重要的數據庫或應用升級，實現了一個新模塊或者對數據庫配置做了改變，建議你重新收集fixed object的統計信息。比如你增大了SGA的大小，那么包含了有關buffer cache和shared pool信息的X$表可能會有顯著變化，例如v$buffer_pool 或 v$shared_pool_advice所用到的X$表。
從Oracle Database 12c Release 1起，自動統計信息收集任務將會對缺失統計信息的fixed表收集統計信息。而要做到這一點，是需要在系統中其它表已經收集完成后，批處理窗口（譯者注：應是指自動統計信息收集的維護窗口）中還有一些可用時間才可以。即便有了這個新功能，當有了代表性的負載運行數據，特別是對系統做了重要的調整后，使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS來收集fixed 表的統計信息，仍然是一個好的實踐。
附原文：
Fixed Objects Statistics and Why They are Important
January 2, 2020 | 2 minute read
Maria Colgan
Distinguished Product Manager
Fixed objects are the "Xperformance views in Oracle are defined in top of X$ tables (for example VSQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a sub-optimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.
Prior to Oracle Database 12c Release 1 fixed object statistics are not created or maintained by the automatic statistics gathering job. You can collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.
BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/
The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. Blocks is always set to 0 since the X$ tables are in memory structures only and are not stored on disk. You must have the ANALYZE ANY DICTIONARY or SYSDBA privilege or the DBA role to update fixed object statistics.
Because of the transient nature of the X$ tables it is important that you gather fixed object statistics when there is a representative workload on the system. This may not always be feasible on large system due to additional resource need to gather the statistics. If you can’t do it during peak load you should do it after the system has warmed up and the three key types of fixed object tables have been populated:
| Structural Data | For example, views covering datafiles, controlfile contents, etc. |
|---|---|
| Session-based Data | For example, vaccess, etc. |
| Workload Data | For example, vsql_plan etc. |
It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module, or make changes to the database configuration. For example if you increase the SGA size then all of the X$ tables that contain information about the buffer cache and shared pool may change significantly, such as X$ tables used in vshared_pool_advice.
From Oracle Database 12c Release 1 the automatic statistics gathering job will gather statistics for fixed tables that have missing stats. For this to happen, there will need to be some time available inside the batch window after statistics for the other tables in the system have been gathered. Even with this new functionality, it is still good practice to gather fixed table stats with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS when there’s a representative workload running, especially after major changes have been made to the system.
