By Mladen Gogala

Collecting statistics is a tough nut to crack, for every DBA. Newer versions of Oracle RDBMS come with an automated job which, let's be honest, does a really poor job, if left to its own accords. This automated job runs far too often, daily in version 10g or weekly in version 11g and it doesn't collect histograms.

Histograms provide the distribution of the column data and are crucial for the optimizer to decide whether to use an index or not. In other words, histograms are essential for the selection of adequately performing execution plans. The usual advice dispensed by many Oracle experts is to turn this job off and write your own, doing a better job. Unfortunately, that will also force you to either rewrite all of the intelligence built into DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC or give it up. Job that gathers database statistics is based on an undocumented procedure  GATHER_DATABASE_STATS_JOB_PROC in the DBMS_STATS package.

This procedure reads and observes all parameters, set by SET_PARAM procedure, which means that we can obtain fairly usable statistics without much effort. Many authors discourage gathering statistics with option like ?FOR ALL INDEXED COLUMNS SIZE 251? but statistics produced by DBMS_STATS using ?indexed columns? option is quite adequate for the vast majority of Oracle databases I've worked with during the last 5 years of my career. This is more then 100 databases in various companies, ranging from 50 employees to more then 3000 employees.

Recipe

The recipe is really simple. First, we set parameters for the collection routine:

That will make GATHER_STATS_JOB to run on the first day of every month, at 22:30 PM. It will sample 8 percent of every table it collects statistics for and will create histograms with 251 endpoints on all indexed columns. Statistics thus produces is actually quite good statistics and provides the optimizer with sufficient information to accurately assess the optimal execution plans for the vast majority of SQL statements. Sample of 8% is rather large, but the underlying database is quite a small one. Larger the database,  smaller the sample.

Statistics operations can be monitored by looking into DBA_OPTSTAT_OPERATIONS table. This table shows operation, start and end times of all DBMS_STATS operations on database and schema level.

Proof

As usual, Oracle Corp. doesn't make our job easier by documenting things, but we can experiment. What we can do to experiment is to set parameters by using DBMS_STATS.SET_PARAM to see whether the undocumented database procedure for statistics collection observes them. The experiment itself is extremely simple, it involves dropping statistics on the EMP table, setting parameters and then re-running statistics collection like this:

Varying different options by using different arguments for DBMS_STATS.SET_PARAM will produce different histograms. The first option is  with METHOD_OPT set to 'FOR TABLE'. It doesn't produce any histograms whatsoever:

For the next step, I created indexes on columns DEPTNO and JOB, in addition to the existing primary key. The METHOD_OPT will now be set to 'FOR ALL INDEXED COLUMNS SIZE 4'. The histogram produced looks like this:

Finally, when we set the options to 'ALL INDEXED COLUMNS SIZE 251',  we will see this:

That is pretty much what is expected. Columns JOB and DEPTNO have frequency histograms as the number of different values is much smaller then the requested histogram size. EMPNO column is the primary key column and histograms are pointless for the primary key columns as the distribution of values in the primary key columns is always the same.  The query used to produce these HTML tables looks is a simple join of DBA_TAB_COLUMNS and DBA_HISTOGRAMS. It looks like this:

Oracle11g

Until now, this article was entirely devoted to Oracle10g. Oracle11g, however, is a slightly different animal. First,  job named ?GATHER_STATS_JOB? doesn't exist in Oracle11g. Here are jobs delivered with an Oracle11g instance.

Judging by the name alone, there are two jobs that can be candidates to replace GATHER_STATS_JOB:

1.     ORACLE_OCM.MGMT_STATS_CONFIG_JOB

2.     SYS.BSLN_MAINTAIN_STATS_JOB

ORACLE_OCM user belongs to the Oracle Configuration Manager, Oracle version of similar Microsoft utility. Same as with Microsoft, people are reluctant to use the OCM because of the ?big brother is watching you? feeling. For now (March 2015), it is fair to assume that the vast majority of Oracle11 databases are installed for testing purposes and without OCM.  Nevertheless, we will have to check it out. How can we check what does a job, for which we don't have a source, do? We can see whether there are any objects dependent on it and we can trace the procedure execution with 10046 trace.

After doing all that, the conclusion is that neither of  these two jobs is a plug-in replacement for GATHER_STATS_JOB. One job requires diagnostics package license, the other one belongs to the configuration manager. There is no GATHER_STATS_JOB in version 11! What can we do now to get our intelligent statistics collection back? We can create our beloved GATHER_STATS_JOB: