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.
--------------
1227
SQL> create table t2 (col1 number);
Table created.
SQL> select count(*) from tab$;
COUNT(*)
---------------
1228
---------------
1228
SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;
NUM_ROWS
------------------
1213
------------------
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
-------------------
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
--------------
1228
Now gather_stats_job run manually from DATABASE CONTROL !!!
SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;
NUM_ROWS
-----------------
1230
-----------------
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.
Enjoy :-)
No comments:
Post a Comment