Gather Stats – The Job That Keeps Stats Up-To-Date

What are optimizer statistics, which one is the default statistics collection job, how do I disable it and why would I disable it, what do I replace it with? All these questions will be answered in my Gather Stats series.

It all started one sunny day, when the AWR was reporting that gather_database_stats_job_proc is one of the top consumers in the database. The name itself is self explanatory, however I got curious to find out how is it scheduled, and what time is it running.
As I started researching, I noticed two more things: a job called BSLN_MAINTAIN_STATS_JOB, and another thing called an auto task “auto optimizer stats collection“. At this point I got really confused, which one of these things is in charge of gathering optimizer statistics for the database?

Before I go any further, I would like to clarify what are optimizer statistics?

The optimizer stats are data that describes the database, tables, indexes, such as number of rows in the table, number of distinct value in an index etc,
and are used by the optimizer to choose the best execution plan for the SQL statements. These stats are stored in the data dictionary, and you can access them through the data dictionary views, such as DBA_TABLES, DBA_INDEXES etc. There are different type of statistics that can be gathered in the database, and the following are the most important ones:

• database objects stats
• data dictionary stats
• fixed objects stats.

It is good practice to keep these statistics up to date.

Going back to my question, which job or program, or auto task is responsible in 11g and up for the optimizer statistics? Here are my findings:

• BSLN_MAINTAIN_STATS_JOB – has very little to do with the optimizer stats, except maybe the name. This job is new in 11g. It is a job in DBA_SCHEDULER_JOBS, and it keeps the default baseline statistics up to date. There is very little documentation on it.
• auto optimizer stats collection – is an Auto Task, new in 11g, and also available in 12c. It runs during the predefined maintenance windows. The interesting thing is that this auto task is invoking the gather_database_stats_job_proc program. To view information about the auto optimizer stats collection, query the DBA_AUTOTASK_CLIENT view.

Now it all makes sense.

Some of the documents I read from Oracle’s support site: Doc ID: 755838.1,Doc ID: 1233203.1; You will need a valid account with Oracle Support, in order to access these documents.

What’s in for next week: useful views, how to disable/enable the delivered stats job, and why would you do that.

Until next week, keep stats up to date!