10 Facts about Automatic Statistics Gathering

I regularly receive inquiries about automatic statistics gathering which is controlled by a default job (gather_stats_job) installed with any new 10G/11G database.

 

Here are some facts I’ve compiled so far:

 

  • Job runs daily in 10g, weekly in 11g

 

  • Job runs in the default maintenance window (opened from 10pm to 6am)

 

  • Gather statistics on objects with missing or stale statistics

 

  • Can be resource intensive depending on daily activity

 

  • Objects that are in most need are processed first

 

  • The default gathering parameters can be modified using DBMS_STATS.SET_PARAM

 

  • Run DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’) to disable the job

 

  • For highly volatile tables (truncated once or many times during the day), it’s preferrable to set the statistics to NULL and let the dynamic sampling kick in, use DBMS_STATS.DELETE_STATS and LOCK_TABLE_STATS

 

  • Statistics are not gathered on external tables, they must be gathered individually using DBMS_STATS.GATHER_TABLE_STATS

 

  • Statistics are not gathered on fixed objects , use GATHER_FIXED_OBJECTS_STATS during a representative workload

Optimizer Cost Model vs Implicit Sorting and Datatype Conversion

I recently stumbled on an issue for the first time, while migrating an application from 9i to 10g.

When migrating an application to 10g, one thing that needs to be considered is the optimizer cost model change.

In Oracle 9i, the cost model parameter (_optimizer_cost_model) is set to IO by default, while in 10G it is set to CHOOSE, seems to default to CPU in all cases however.

It doesn’t seem like much, but the implications are serious.

The IO cost model provides implicit data conversion and sorting.

In the case of my application, the developers had relied on implicit data conversion and data sorting when designing the reporting module.

In short, they had not included any ORDER BY clauses and were comparing CHAR’s with NUMBER’s.

 

Obviously when there were only 1 or 2 predicates in the WHERE clause, it didn’t make a difference wether sorting was implicit or not.

 

So after a migration, if you see strange sorting order and the ORA-01722: invalid number error, check your code.

A workaround is to set the  _optimizer_cost_model value to IO.

 

Pierre