Automatic Optimizer Statistics Collection

В Oracle 10g сбор статистики для оптимизатора выполняется автоматически запланированным джобом (scheduled job) GATHER_STATS_JOB. Данная статистика об объектах используется оптимизатором (Cost-Based Optimizer) для построения эффективных планов выполнения для запросов, тем самым значительно уменьшая время выполнения запросов.

По умолчанию, сбор статистики выполняется по ночам с 22:00 до 06:00 утра и весь день в выходные дни.
Собирается статистика только по тем объектам, у которых отсутствует статистика, или устарела.

Каким образом Oracle узнает, что статистика устарела?
Данные о кол-ве DML операциий (INSERT, DELETE, UPDATE) над объектом с момента последнего сбора статистики фиксируются в SGA, которые периодически записываются в таблицу DBA_TAB_MODIFICATIONS. База данных использует эти данные для того, чтобы определить устарела ли статистика объекта.

Из документации Oracle:

Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:
Missing statistics
Stale statistics

This job is created automatically at database creation time and is managed by the Scheduler. This Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends. The GATHER_STATS_JOB continues until it finishes, even if it exceeds the allocated time for the maintenance window. The default behavior of the maintenance window can be changed.

Запланированный джоб GATHER_STATS_JOB:

SQL> select owner, job_name, program_name, enabled from dba_scheduler_jobs
where job_name='GATHER_STATS_JOB';

OWNER      JOB_NAME           PROGRAM_NAME         ENABLED
---------- ------------------ -------------------- -----
SYS        GATHER_STATS_JOB   GATHER_STATS_PROG    TRUE

Выключить Автоматический Сбор Статистики для Оптимизатора, можно отключив джоб:

SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
PL/SQL procedure successfully completed.


SQL> select owner, job_name, program_name, enabled from dba_scheduler_jobs
where job_name='GATHER_STATS_JOB';

OWNER      JOB_NAME           PROGRAM_NAME         ENABLED
---------- ------------------ -------------------- -----
SYS        GATHER_STATS_JOB   GATHER_STATS_PROG    FALSE

Если в базе данных имеются таблицы, которые часто обновляются, то частый сбор статистики может негативно повлиять на производительность базы данных. Для того, чтоб исключить объекты из автоматического или любого другого сбора статистики можно "закрепить" ее статистику:

begin
 dbms_stats.gather_table_stats('SCOTT','EMP');
 dbms_stats.lock_table_stats('SCOTT','EMP');
end;
/

Теперь, по этой таблице невозможно будет собрать статистику ни автоматически, ни вручную:

SQL>exec dbms_stats.gather_table_stats('SCOTT','EMP');

begin dbms_stats.gather_table_stats('SCOTT','EMP'); end;
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 2 

Снять блокировку статистики:

dbms_stats.unlock_table_stats('SCOTT','EMP');