Why do some FIXED_TABLES not have statistics

Lets start with some advice from Metalink note 272479.1: Gathering Statistics For All fixed Objects In The Data Dictionary.

With Oracle Database 10G it is now recommended that you analyze the data dictionary.
The data dictionary has many fixed tables, such as X$ tables and collecting statistics
for these objects is suggested by Oracle. The GATHER_FIXED_OBJECTS_STATS
procedure gathers statistics for all fixed objects (dynamic performance tables) in the
data dictionary.

So we know we should do it – but when? 

Advice from the Oracle documentation this time. Statistics on fixed objects, such as the dynamic performance tables, need to be

manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record current database activity; statistics gathering should be done when database has representative activity.

Why do some fixed tables not have statistics even after running: DBMS_STATS.GATHER_FIXED_OBJECTS_STATS?

Some fixed tables are left without stats because the development team has flagged the code to avoid statistics gathering for some fixed tables. This is because they know they will perform better without stats or because it is not supported to gather the statistics upon them.

This is expected behavior which was confirmed in : Bug 12844116 fixed tables statistics.

If you encounter performance issues specifically because statistics are missing on certain of these fixed tables, please file a SR with Database Support, quoting this article as reference.

Some fixed tables(X$) have no CBO statistics against them and use defaults; it may betoo expensive to gather stats or the tables are too volatile such that any statistics are immediately outdated.

The sys.x$ktfbue table is one such object. These statistical inaccuracies can cause suboptimal plans in some cases.

An execution plan regression occurs if there are a large number of records in the X$KTFBUE table. Gathering of dictionary or fixed object stats does not improve performance as this table is intentionally omitted from those packages.

CAUSE

The Cost Based Optimizer uses a cardinality estimate of 100,000 rows for this table, and the 11.2.0.3 execution plan is doing a full table scan. If you have a large number of extents, this query can take more than 1 hour to complete. There are a number of unpublished bugs open on slow perfomance for queries on DBA_EXTENTS.
For example, following bugs:
Bug:13542477 ON 11.2.0.3 QUERY DBA_EXTENTS IS SO SLOW
Bug:14221159 JOIN CARDINALITY INCORRECT CALCULATED AFTER FIX 11814428

SOLUTION

Following are possible workarounds to improve the performance
Gathering stats on the X$KTFBUE table using the following command:

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘X$KTFBUE’);
OR
alter session set “_optimizer_cartesian_enabled” = false;
OR
alter session set “_smm_auto_cost_enabled” = false;
OR
Disable fix for unpublished Bug Bug 11814428: POOR CARDINALITY ESTIMATE FROM JOIN WITH A UNIONALL VIEW:
alter session set “_FIX_CONTROL” = “11814428:0″;

Additional note:

1. So what exactly X$KTFBUE table is used for? X$KTFBUE is a dynamically generated map of used extents in locally managed tablespace.

The followings are the definitions for the columns in x$ktfbue.

ADDR : N/A
INDX : N/A
INST_ID : N/A
KTFBUESEGTSN : ts# containing this segment
KTFBUESEGFNO : Relative number of the file containing the segment header
KTFBUESEGBNO : segment header block number
KTFBUEEXTNO : Extent number
KTFBUEFNO : Relative number of the file containing the extent
KTFBUEBNO : Starting block number of the extent
KTFBUEBLKS : Size of the extent in ORACLE blocks
KTFBUECTM : commit_jtime,Commit Time of the undo in the extent expressed as Julian date
KTFBUESTT :commit_wtime,Commit Time of the undo in the extent expressed as wall clock time
KTFBUESTA : Transaction Status of the undo in the extent;1, ‘ACTIVE’, 2, ‘EXPIRED’, 3, ‘UNEXPIRED’, 0 for non‐undo

2. Another interesting part is that the total number of rows in X$KTFBUE table was about 99 million rows while the total number of extents in the database was also around 99 million rows. So reducing the total number of extent in the database seem a good way to reduce this stats issue. Pay attention to the INITIAL and NEXT in the storage clause. Setting too small will have a large number of extents and create headache in querying fixed tables. 

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.

Prior to Oracle Database 12c 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 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 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, 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.

From Oracle Database 12c 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.

Some Tips : DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

Starting in Oracle 10g we see a new package gather_fixed_stats for analyzing the dictionary fixed structures (the X$ tables).  We now have three types of stats to analyze for the SQL optimizer:

• Table/object/schema stats: Via dbms_stats.gather_table_stats. Orgather_schema_stats.  Traditional metadata from data tables.
• System stats:  Via dbms_stats.gather_system_stats:  OS statistics (disk, CPU timings).
• Dictionary objects:  Used to make dictionary queries more efficient.  Thegather_fixed_objects_stats collects the same metadata as gather_table_stats, excepts for the number of blocks.  This is because the x$ structures and the v$ views only exists in the RAM of the SGA.

The docs note "Analyze fixed objects only once, unless the workload footprint changes. You must be connected a SYS (or a user with SYSDBA) to invokedbms_stats.gather_fixed_objects_stats.

Just like the workload statistics, Oracle recommends that you analyze the x$ tables only once, and during a typical database workload. 
exec dbms_stats.gather_schema_stats('sYS?,gather_fixed=>TRUE)

exec dbms_stats.gather_fixed_objects_stats(?ALL?); 

Oracle notes that the data dictionary now contains several classes of x$ structures and v$ views:  See here for all important v$ views.

• Structural fixed data - for example, v$datafile, v$datafile_header, &c

v$archive_dest
v$archive_dest_status
v$archive_gap
v$archive_processes
v$archived_log
v$bh
v$buffer_pool
v$buffer_pool_statistics
v$controlfile
v$controlfile_record_section
v$database
v$datafile
v$datafile_header
v$dataguard_status

• Session based fixed data - Such as v$session, v$access, &c.

v$session
v$session_event
v$session_longops
v$session_wait
v$session_wait_history
v$sessmetric
v$sesstat

• Workload fixed data - Such as  v$sql, v$sql_plan
• SQL data:

v$sql
v$sql_bind_capture
v$sql_bind_data
v$sql_cursor
v$sql_plan
v$sql_text_with_newlines
v$sql_workarea
v$sqlarea
v$sqltext
v$sqltext_with_newlines

We also see the new procedures dbms_stats.export_fixed_objects_stats anddbms_stats.import_fixed_ objects_stats for migrating production workload statistics into test and development instances.

Re-Analyzing fix object statistics

Oracle recommends a single analyze of data dictionary and x$ fixed structures for the cost-based optimizer, but it is not clear when it is necessary to re-analyze the v$ views and x$ structures.

If it ain't broke, don't fix it.  However, Oracle recommends that major parameter changes (db_cache_size, shared_pool_size. sga_target, &c ) may be followed-up with a re-analyze using dbms_stats.gather_fixed_stats.

gather_fixed_objects_stats usage tips

• You must have the SYSDBA or ANALYZE ANY DICTIONARY system privilege to execute this procedure.
   
• Also note Bug 3982803 - OERI[kcbshcb_1] with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
   
• Andrew Holdsworth of Oracle Corporation notes that dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters:

'the payback from good statistics management and execution plans will exceed any benefit of init.ora tuning by orders of magnitude?

Fixed Table Statistics

The data dictionary has many fixed tables, such as X$ tables. Oracle suggests that you also collect statistics for these objects, however, less frequently than the other normal objects.

There is a new parameter gather_fixed available in the procedure gather_database_stats which when set to TRUE, collects the statistics for data dictionary fixed tables. gather_fixed is set to FALSE by default, and causes statistics not to be gathered for fixed tables. It may not be necessary to collect statistics very often for data dictionary fixed tables.

Another procedure, gather_fixed_objects_stats, is primarily aimed at collecting statistics of fixed objects. This procedure takes the following arguments:

• STATTAB: The user statistics table identifier describing where to save the current statistics. Default value is NULL for dictionary collection.
• STATID: The optional identifier to associate with these statistics within STATTAB. Default value is also NULL
• STATOWN: The schema containing STATAB. Default value is NULL.
• NO_INVALIDATE: Do not invalidate the dependent cursors if it is set to TRUE. Default value is FALSE.

It is also possible to delete statistics on all fixed tables by using the new procedure delete_fixed_objects_stats. You can also perform export or import statistics on fixed tables by using the export_fixed_objects_stats and import_fixed_objects_stats procedures respectively.

The following example shows different formats:

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', -gather_fixed=>TRUE) ;

PL/SQL procedure successfully completed.

You can also use the gather_fixed_objects_stats procedure to collect statistics.

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (?ALL?);

In addition to what has been shown above, it is also possible to collect statistics for individual fixed tables. The procedures in the dbms_stats package that accept a table name as an argument are enhanced to accept a fixed table name as an argument. Since the fixed tables do not have I/O cost, as the rows reside in memory, CBO takes into account the CPU cost of reading rows.

Oracle suggests the following best practices for collecting statistics.

• Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.
• Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.
• For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically.

In the next section, we will examine the changes introduced for the dbms_stats package.

With Oracle Database 10g, there are some new arguments available for the dbms_stats package subprograms. Those parameters are as follows:

• Granularity
• Degree

Granularity

This parameter is used in subprograms such as gather_table_stats and gather_schema_stats. This parameter indicates the granularity of the statistics that you want to collect, particularly for partitioned tables. As an example, you can gather the global statistics on a partitioned table, or you can gather global and partition-level statistics. It has two options. They are: AUTO and GLOBAL AND PARTITION.

When the AUTO option is specified, the procedure determines the granularity based on the partitioning type. Oracle collects global, partition-level, and sub-partition level statistics if sub-partition method is LIST. For other partitioned tables, only the global and partition level statistics are generated.

When the GLOBAL AND PARTITION option is specified, Oracle gathers the global and partition level statistics. No sub-partition level statistics are gathered even it is composite partitioned object.

Degree

With this parameter, you are able to specify the degree of parallelism. In general, the ?degree? parameter allows you to parallelize the statistics gathering process. The degree parameter can take the value of auto_degree .

When you specify the auto_degree, Oracle will determine the degree of parallelism automatically. It will be either 1 (serial execution) or default_degree (the system default value based on number of CPUs and initialization parameters), according to the size of the object. Take care if Hyper Threading is used, as you will have less computational power than Oracle assumes.