When to Gather Fixed Object Optimiser Statistics

Lets start with some advice from Metalink note 272479.1: Gathering Statistics For All fixed Objects In The Data Dictionary.

With Oracle Database 10G it is now recommended that you analyze the data dictionary.
The data dictionary has many fixed tables, such as X$ tables and collecting statistics
for these objects is suggested by Oracle. The GATHER_FIXED_OBJECTS_STATS
procedure gathers statistics for all fixed objects (dynamic performance tables) in the
data dictionary.

So we know we should do it – but when? Advice from the Oracle documentation this time.

Statistics on fixed objects, such as the dynamic performance tables, need to be
manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record
current database activity; statistics gathering should be done when database has
representative activity.

I have an example below of how important the timing of this “representative activity” can be. Here is a simple query – the likes of which are often run by monitoring tools such as Oracle Grid Control – executed on a database that has had statistics for all fixed objects gathered previously.

select count(*) from v$rman_backup_job_details;

  COUNT(*)
----------
        23

Elapsed: 00:01:43.39

Nearly 2 minutes for a 23 row result set. The execution plan is a bit long to post here but below is a snippet that set some gentle alarm bells ringing.

------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | 
------------------------------------------------------------------------------
... snip ...
|* 11 |            HASH JOIN OUTER          |                        |     1 | 
|  12 |             MERGE JOIN CARTESIAN    |                        |     1 |
|  13 |              MERGE JOIN CARTESIAN   |                        |     1 |
|  14 |               FIXED TABLE FULL      | X$KCCRSR               |     1 |
... snip ...
|* 36 |           HASH JOIN OUTER           |                        |     1 |
|  37 |            MERGE JOIN CARTESIAN     |                        |     1 |
|  38 |             MERGE JOIN CARTESIAN    |                        |     1 |
|  39 |              FIXED TABLE FULL       | X$KCCRSR               |     1 |
... snip ...
------------------------------------------------------------------------------

A quick glance at the optimiser statistics for the X$KCCRSR table tells us some interesting information.

select rowcnt, blkcnt, analyzetime, samplesize
from
 tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KCCRSR');

    ROWCNT     BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
         0          0 26-APR-09          0

Zero rows. And after gathering fixed object statistics…

exec dbms_stats.gather_fixed_objects_stats;

    ROWCNT     BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
       225          0 21-JUL-09        225

select count(*) from v$rman_backup_job_details;

  COUNT(*)
----------
        23

Elapsed: 00:00:00.19

The optimiser now knows there are 225 rows in X$KCCRSR and returns it’s result set in under a second. Now I’m not suggesting anyone regularly schedule a call to GATHER_FIXED_OBJECTS_STATS just consider the timing of this call a bit. Perhaps after your first couple of RMAN backups may do the trick 🙂

As an interesting aside I stumbled across the following method to view when statistics have been gathered.

select operation,start_time from DBA_OPTSTAT_OPERATIONS order by start_time;

OPERATION                             START_TIME
------------------------------------- -------------------------------------
gather_database_stats(auto)           17-JUL-09 10.00.01.560328 PM -03:00
gather_database_stats(auto)           18-JUL-09 06.00.03.189531 AM -03:00
gather_database_stats(auto)           20-JUL-09 10.00.01.363156 PM -03:00
gather_fixed_objects_stats            21-JUL-09 09.52.59.646815 AM -03:00

I’ve just noticed there are no system statistics in there – that’s an investigation for another day.