Did You Know #17 – DBMS_STATS

Many people are using DBMS_STATS to gather optimizer statistics. This package has many different options, and I wanted to tell you about one option for disabling automatic statistics gathering. When we need to disable the automatic statistics gathering we usually do one of the following:

• Disable the job itself (using DBMS_AUTO_TASK_ADMIN). This completely disables the job so no automatic statistics gathering will be performed.
• lock the statistics (using DBMS_STATS.LOCK_*_STATS), this disallows Oracle from gathering statistics on this partition, table or schema. Any DBMS_STATS against these objects will have to include the FORCE=>true flag to override the lock.

Another option (that I think most people are not aware of) is to use DBMS_STATS.SET_PARAM. With this procedure, we can set a parameter called AUTOSTATS_TARGET to one of the following values:

• ALL – gather statistics for all objects
• AUTO – Oracle decides on the objects to gather statistics for
• ORACLE – Oracle will only gather statistics on Oracle owned objects. Oracle will not gather statistics on any user objects

So if we wish to leave the job and allow Oracle to collect statistics on its own objects but not user objects, we can use: DBMS_STATS.SET_PARAM(‘AUTOSTATS_TARGET’,’ORACLE’)

Update

I just found out that the SET_PARAM procedure and GET_PARAM function are deprecated in 11.2, you should use SET_GLOBAL_PREFS procedure and GET_PREFS function instead:
exec DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ORACLE’)
select DBMS_STATS.GET_PREFS (‘AUTOSTATS_TARGET’) from dual;