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. 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 dependant 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 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:

Firstly Check the last analyzed date:

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

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 back 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');

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

3 comments:

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