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’)

One thought on “Did You Know #17 – DBMS_STATS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s