Fixed objects are the x$ tables and their indexes.
Why we must gather statistics on fixed objects:
If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
Does Oracle gather statistics on fixed objects:
Statistics on fixed objects are not being gathered automatically nor within gathering database stats procedure. Starting with 12.1 fixed object statistics will be gathered for the tables that don’t have statistics, but still, this will not produce accurate statistics and hence Oracle recommends the DBA do this job himself as he/she knows better the peak time of his/her DB.
When we should gather statistics on fixed objects:
-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
–Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
–Features that are heavily dependent on fixed objects by design are performing slow, like RMAN, EM and Grid Control.
-This task should be done only a few times per year.
Notes to consider before starting gathering fixed object statistics:
-It’s recommended to Gather the fixed object statistics during peak hours (while the system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables have been populated and the statistics are well representing the DB workload.
–Performance degradation may be experienced during gathering fixed object statistics.
-Having no statistics is better than having a non-representative statistics.
How to gather stats on fixed objects:
First Check the last analyzed date:
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name=’X$KGLDP’;
OWNER TABLE_NAME LAST_ANAL
——— ———— ———
SYS X$KGLDP 20-MAR-12
Second Export the current fixed stats in a table: (in case you need to revert)
— Create a Stats table to hold the statistics:
SQL> exec dbms_stats.create_stat_table(‘SYS’,’STATS_TABLE_BKP’);
— Export the current statistics into the Stats table
SQL> exec dbms_stats.export_fixed_objects_stats(stattab=>’STATS_TABLE_BKP‘,statown=>’SYS’);
Last Gather fixed objects stats:
SQL> exec dbms_stats.gather_fixed_objects_stats;
Important Note:
Above command will take a while to complete, once it finishes, it’s important to flush the shared pool to get an immediate reflection of the new gathered statistics and let the magic happen:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
Remember, it’s always recommended to run FLUSH SHARED_POOL command during the least workload times on the DB; as it will force the hard parse of most of the queries on the DB, the thing will slow down the applications for some time; till most of the queries get hard parsed again.
In case of reverting to the old statistics:
In case you experienced bad performance on fixed tables after gathering the new statistics you can revert to the old statistics you already backed up:
SQL> exec dbms_stats.delete_fixed_objects_stats();
SQL> exec DBMS_STATS.import_fixed_objects_stats(stattab =>’STATS_TABLE_BKP’, STATOWN =>’SYS’);
Update on uncovered fixed objects by gather_fixed_objects_stats procedure: [05-01-2020]
Some fixed objects are not covered by gather_fixed_objects_stats procedure like X$KTFBUE which used by views that show segments size; like DBA_EXTENTS and DBA_FREE_SPACE, in case you observe slowness when querying those views then you have to gather the statistics for the underlying fixed table using the following procedure:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (‘SYS’,’X$KTFBUE’);
It’s recommended to flush the shared pool:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
In case you still facing slowness after gathering the fixed objects’ statistics, it’s recommended to purge the RECYCLEBIN as a workaround:
SQL> PURGE DBA_RECYCLEBIN;
For more reading on Statistics topic:
http://dba-tips.blogspot.com/2012/11/all-about-statistics-in-oracle.html
References:
Oracle white paper: Best Practices for Gathering Optimizer Statistics
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf
https://blogs.oracle.com/optimizer/fixed-objects-statistics-and-why-they-are-important
https://weidongzhou.wordpress.com/2015/03/15/fixed-table-stats-issue-after-gathering-fixed-object-stats/
The Pursuit of Ultimate Performance On Non Production Oracle Databases
Hi there,
I just read your post regarding the fixed objects statistics gathering.
I'd like to ask for your opinion for this:
I have a production database running on a 2-node RAC. Both instances are running really slow when joining for example, V$SQL and V$SESSION by SQL_ID.
Do you think gathering the statistics on peak hours will improve the performance?
Also, if I get bad results, will it be a good idea running dbms_stats.delete_fixed_object_stats? Statistics for fixed objects have never been gathered and the database has been running for 2+ years.
Thanks in advance 🙂
Gathering fixed objects stats may help, I suggest you to do this practice on a test DB first before going production, try first to export the fixed stats on a table, test the performance on the fixed objects, at the end try to import old stats back.
Could you please tell me how many object you have in DBA_RECYCLEBIN?
select count(*) from dba_recyclebin;
Having too many objects in the dba_recyclebin may cause the same scenario you have, specially if your applications are heavily depend on temporary tables. if so I suggest you to purge it unless you need the recycled objects.
Hello again,
I know a lot of time has passed but I purged the recycle_bin and the queries on fixed objects have run a lot faster.
I also tried to gather new statistics on peak hours, as you say on your post. However, I didn't notice any difference, so I imported the old stats back.
Thanks! 🙂
I came across your article to gather fixed objects stats in order to tune the queries against DBA_EXTENTS without any luck, querying DBA_EXTENTS to check the size of any object no matter it's big or small still takes very long time, gathaering fixed object stats didn't make any improvement! Any idea?
Thanks for your comment;
DBA_EXTENTS is querying X$KTFBUE which is not covered by gather_fixed_objects_stats procedure; you have to gather the statistics separately on this table using the following:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SYS','X$KTFBUE');
It's recommended to flush the shared pool to get the new statistics take effect immediately:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
I've updated the article with the same.