В 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');
Я знала, что в Oracle 10g появилось очень много новых событий ожидания (wait events) но даже не думала, что настоолько много! Первым делом решила проверить это и посчитала кол-во событий ожидания в версиях 9i и 10g:
