Monday, March 26, 2012

Gathering Fixed Objects Statistics

What are the fixed objects:

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.

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.
-This task should be done only a few times per year.

-It's recommended to Gather the fixed object stats during peak hours (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 been populated and the statistics well represent the DB activity.
-Performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representive statistics.

How to gather stats on fixed objects:

Firstly Check the last analyzed date:

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

------------------------------ ------------------------------      ---------
SYS                   X$KGLDP      20-MAR-12

Secondly Export the current fixed stats in a table: (in case you need to revert back)

exec dbms_stats.create_stat_table('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
exec dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');

Thirdly Gather fixed objects stats:

exec dbms_stats.gather_fixed_objects_stats;

In case of reverting to the old statistics:
In case you experianced a bad performance on fixed tables after gathering the new statistics:

exec dbms_stats.delete_fixed_objects_stats(); 
exec DBMS_STATS.import_fixed_objects_stats(stattab =>’STATS_TABLE_NAME’,STATOWN =>'OWNER');


Pedro Lucas Farinha said...

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 :)

Mahmmoud ADEL said...

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.

Pedro said...

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! :)