Friday, January 7, 2011

How to gather statistics on data dictionary objects in Oracle 10g

How to gather statistics on data dictionary objects in Oracle 10g.


Before Oracle database 10g Oracle explicitly recommeded 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 you 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 which pops uo in my seminars frequently is “Does this job also collect statistics on the data dictionary objects as well?” The answer is not 42 but “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


– 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 gahter statistics for the data dictionary manually?

Oracle recommends to collect them when a significat nnumber of changes were applied to the data dictionary, like dropping significant numbers of partions and creating new ones dropping tables, indexes, creating new ones and so on.

But this only if it is a signifcant number of changes and you cannot wait for the next automatically scheduled job run.

No comments:

Post a Comment