Fixed Object Statistics

If you have a database where querying against v$ Performance views is painful, consider gathering Fixed Object Stats.

Fixed objects are the 700+ X$ tables that feed a lot of these system and performance views.

Fixed Object Stats are _not_ gathered by the default stats gathering procedures. They are not gathered at all unless you run the proc to gather them. In this case Oracle relies heavily on Dynamic Sampling… but that isn’t really a substitute for really gathering stats.

More info on why and how is available at the blog post by one of the folks on the Optimizer team:
https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

You can see if you have any fixed stats with this:

SELECT * FROM DBA_TAB_STATISTICS WHERE OBJECT_TYPE='FIXED TABLE';

And you can gather fixed stats with this:

BEGIN
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;

I have some queries that run against one of my databases to check space allocation on its 35,000+ partitions. Before FOS were gathered it took 7-12 minutes to run. After gathering FOS it was in the 2-3 minute range.