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