Fixed Objects Statistics and why they are important

Fixed objects are the x$ tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_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 suboptimal 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.

Fixed Object statistics must be manually gathered. They 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 asDBMS_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 SYSDBAprivilege or the DBA role to update fixed object statistics.

Because of the transient nature of the x$ tables it is import 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, v$session, v$access, etc.
Workload data - for example, v$sql, v$sql_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 v$buffer_pool or v$shared_pool_advice.

The following SQL*Plus script can be used to determine the status of these statistics. It does not check statistics for other internal schemas like for example SYSTEM as these are less critical. If needed, the WHERE-clause in the first query can to be modified accordingly.

alter session set nls_date_format='YYYY-Mon-DD';
col last_analyzed for a13
set termout off
set trimspool off
set feedback off
spool dictionary_statistics

prompt 'Statistics for SYS tables'
SELECT NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) dictionary_tables
FROM dba_tables
WHERE owner = 'SYS'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

prompt 'Statistics for Fixed Objects'
select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

参考文档：

Fixed Objects Statistics(GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)