Сбор статистики оптимизатора

Правильный сбор статистики оптимизатора со временем играет все более важную роль для общей произволительности приложения. Оптимизатор становится все более и более
сложным (и мы все надеемся что разумным :), для правильных выводов ему нужны достоверные данные. Задачей администратора СУБД является обеспечить достоверность
этих данных.


Как показал Tom Kyte, сбор статистики оптимизатора нужно выполнять с помощью dbms_stats.

Однако, остаются неопределенными такие важные параметры как % сбора статистики (estimate_percent), число бакетов (bucket), да и набор колонок, для которых следует выполнять сбор статистики. Как показал Wolfgang Breitling, в статье "A LOOK UNDER THE HOOD OF CBO: THE 10053 EVENT", следует собирать гистограммы не только для индексных колонок.

Начиная с 9i в составе dbms_stats появились методы, позволяющие заставить Oracle самому выбрать наилучшие параметры

Так можно указать estimate_percent => dbms_stats.auto_sample_size, а для числа бакетов указать method_opt=>'for all columns size skewonly'

Однако, как и всякая магия - эта работает не всегда.

Очень рекомендую познакомится с блестящей презентацией "What's up with dbms_stats ?" посвещенной оценке работы dbms_stats.


Из моего опыта следует:

1)Высокий % (~90%) совпадения с реальными данными
получается уже при estimate_percent=60%.

При этом время сбора статистики для 60% примерно на треть меньше, чем для сбора
полной статистики (compute).

Таким образом можно рекомендовать проводить первоначальный
сбор статистики с estimate_percent = 60%. Это дает хорошую оценку времени сбора
полной статистики и хорошее приближение к реальным данным.


2) Для реальной таблицы (~40 млн строк)
method_opt=>'for all indexed columns' не выделил более 75 бакетов даже для колонок
с более чем 12 млн уникальных значений, в то время как
method_opt=>'for all indexed columns size skewonly' skew выделил до 200 бакетов.




Выводы:
Несмотря на общее недовольство авторов презентаций новомодной автоматикой dbms_stats, она явно может оказаться полезной. собрать статистику и сохранить ее для дальнейшего анализа в отдельной таблице с помощью dbms_stats.export_table_stats крайне рекомендуется.

С помощью method_opt=>'for all columns size skewonly' можно также собрать статистику по неиндексным колонкам.

А если включить мониторинг (ALTER TABLE...MONITORING) то последующий сбор статистики будет только для тех объектов, по которым были изменения.


Однако, наличие ошибок в версии 9i (сбор статистики по первичному ключу) не позволяет быть уверенным 100%, что эта процедура работает оптимальным образом.

В моем случае method_opt=>'for all indexed columns size skewonly'
все-таки привел к замедлению приложения по сравнению с 'for all indexed columns'.

В результате сравнения трассировок причина оказалась в дополнительных событиях ожидания 'db file sequential read' для операции insert. Данные ожидания были связаны с чтением индексных блоков. Почему при другом методе сбора статистики этого не происходит - загадка.

Так что рекомендую протестировать как это работает в Вашей версии.

В 10g есть одна неприятная ошибка связанная с RMAN. В отсутсвии статистик на fixed объектах любая сессия RMAN потребляет все доступное temp пространтсво. Исправляется с помощью вызова dbms_stats.GATHER_FIXED_OBJECTS_STATS