DBA Tips for Statistics gathering on Oracle Data Dictionary Objects!!

 Author: Fred Stephen

In Oracle releases prior to Oracle 10g it was explicitly recommended that statistics should not be gathered on data dictionary objects. However with the advent of Oracle database 10g Oracle it was explicitly recommended to gather statistics on data dictionary objects.

Oracle 10g Scheduler Job:

Every Oracle 10g database automatically creates Scheduler Job. The Scheduler Job runs every night and checks for object which have either no statistics at all or for which the statistics have become STALE. STALE statistics means that at least 10% of the statistics values have changed.

gather_stats_job:

This job is called gather_stats_job and belongs to the autotask job class. Basically gather_stats_job uses a program that will again call a procedure from DBMS_STATS package. The DBMS_STATS will do the statistics collection.

statistics_level Parameter:

Remember this feature will work for your 10g database if you at least set statistics_level parameter to TYPICAL. TYPICAL is the default value of this parameter in Oracle10g.The statistics_level parameter utilizes the table monitoring feature that is enabled by default in all Oracle 10g tables.

First of all we will determine if dbms_stats.gather_database_stats collect statistics for the data dictionary or not.

SQL> select count(*) from myTable$;

COUNT(*) ———- 3227

SQL> create table myTable2 (myColumn1 number);

Table created.

SQL> select count(*) from myTable$;

COUNT(*) ———- 3228

SQL> select NUM_ROWS from dba_tables where table_name='MYTABLE$';

NUM_ROWS ———- 3213

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS from dba_tables where table_name='MYTABLE$';

NUM_ROWS ———- 3228

We can see that dbms_stats.gather_database_stats collect statistics for the data dictionary. Now we will determine if the job also collect statistics for the data dictionary

SQL> create table myTable3 (myColumn1 number);

Table created.

SQL> create table myTable4 (myColumn1 number);

Table created.

SQL> select NUM_ROWS from dba_tables where table_name='MYTABLE$';

NUM_ROWS ———- 3228

Now we will run gather_stats_job run manually from database control

SQL> select NUM_ROWS from dba_tables where table_name='MYTABLE$';

NUM_ROWS ———- 3230

We can see that although we do not have any stale values but still statistics are gathered for the data dictionary objects. It is recommended to gather statistics manually when a significant number of changes are applied to the data dictionary and you can not wait for the next automatically scheduled job run. Such changes may occur when you drop significant numbers of partitions and create new partitions by dropping tables, indexes, creating new ones and so on. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of PL/SQL Performance Problems!!
   Oracle Asynchronous COMMIT: Facts and Concerns!
   Add Flexibility to your database – Use Database Abstraction Layer
   Automated Database management makes life easy!!