How to gather statistics on data dictionary objects in Oracle

How to gather statistics on data dictionary objects in Oracle

Before Oracle database 10g ,Oracle   explicitly recommended   not  to   gather   statistics   on  data dictionary   objects . As   of   Oracle database 10g   Oracle   explicitly does   recommend to   gather statistics on   data    dictionary   objects. As   we   might   know, there   is an   automatically   created   SCHEDULER JOB    in every   10g database   which runs   every night and   checks    for object    which   have either no statistics   at   all   or   for   which   the statistics   have   become   STALE   (which  means stat    at   least 10%   of   the  values have changed).   This   job   is   call GATHER_STATS_JOB and    belongs   to   the autotask   job   class.   

It   uses   a   program   which   again   call   a   procedure   from   built    in package DBMS_STATS   which does   the   statistics   collection. This   feature   only   works   if   the   initialization   parameter   STATISTICS_LEVEL   is   set to TYPICAL at   least   (which is the DEFAULT in 10g) and  it utilizes the TABLE MONITORING feature . TABLE  MONITORING is   enabled for  all tables in 10g by DEFAULT.

One question may come in our mind that  “Does  this job also collect   statistics on the data dictionary objects as well?” The answer is  “YES, it does!” and here is the proof  for this . First let us check if dbms_stats.gather_database_stats collect statistics for the data dictionary:

SQL> select count(*) from tab$;

COUNT(*) 
--------------
1227
SQL> create table t2 (col1 number);
Table created.

SQL> select count(*) from tab$;

COUNT(*)      
---------------
 1228

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
------------------
1213

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;   

 NUM_ROWS
-------------------
1228             

IT DOES! – and now let’s see if the job does also: 

SQL> create table t3 (col1 number);

Table created.

SQL> create table t4 (col1 number);

Table created.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
--------------
1228

Now  gather_stats_job run manually from DATABASE CONTROL !!! 

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
-----------------
1230

and IT ALSO DOES! 

Even  though  there were  not  even 0.1%  of  the values changed it did!  So when should we gather statistics for the data dictionary manually? Oracle recommends to collect them when a significant  number of changes  were applied  to the data  dictionary,   like  dropping  significant  numbers  of  part ions  and creating new ones dropping tables, indexes, creating new ones and so on. But this only if it  is a significant number of changes and we cannot wait for the next automatically scheduled job run