gather stats on fixed database objects

Latching on large fixed objects is expensive, so without proper statistics, performance degradation is expected when sub-optimal plans happen.(as in some cases, there are instance wide effects, such as effectively serializing access to the shared pool) This is more prevalent in very large, or very busy systems, as the number of times the latch is requested, and/or the length of time the latch is held, will increase with load and volume in the X$ being queried through the views.

Fixed object statistics will need to be gathered under representative load and you may experience performance degradation while the statistics are gathering.

There are some cases where having no statistics on a fixed object may produce the best plans, but in general, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on. In general, in the case of these volatile fixed tables, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
SYS X$KGLDP

SQL> exec dbms_stats.gather_fixed_objects_stats();

PL/SQL procedure successfully completed.


SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

OWNER TABLE_NAME LAST_ANALYZED
------------------------------ ------------------------------ -------------------
SYS X$KGLDP 07/1/2010 11:09:54