Fixed Objects Statistics and why they are important

Fixed Objects Statistics and why they are important

By Maria Colgan-Oracle on Jan 09, 2012

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.

[ID 457926.1] How to gather statistics on SYS objects and fixed_objects 

GATHER_FIXED_OBJECTS_STATS Considerations 798257.1