TIP #22 : Statistics gathering in Oracle 10g.

In 10g there is Auto stats gathering job which is called GATHER_STATS_JOB.This job is scheduled in dba_scheduler_jobs and is in the charge of gathering statistics automatically.
For having updated statistics, the following conditions should be met.
1. GATHER_STATS_JOB should be enabled.
2. statistics_level should be TYPICAL or ALL.

Monitoring/Nomonitoring is deprecated in 10g and as long as statistics_level is not BASIC, Oracle will track modifications.

In 10g , if optimizer_dynamic_sampling is 2 or higher, it means that if Oracle run query against table without any stats, it dynamically tries to gather stats on that.

1 comment:

Bricks in Chennai said...

I actually enjoyed reading through this posting.Many thanks.

Statistics Jobs