Примечание. Одним из факторов, препятствующих планам Oracle отправить оптимизатор по правилам в

Одним из факторов, препятствующих планам Oracle отправить оптимизатор по правилам "в отставку", является большой объем собственного прикладного кода компании, который должен подвергнуться широкомасштабной и дорогостоящей настройке под использование оптимизации по правилам. Пока Oracle не решит вложить средства в переработку этого кода или не наделит оптимизатор по стоимости способностью генерировать эффективные пути доступа, оптимизатор по правилам, скорее всего, останется в строю.

Чем различаются эти режимы оптимизации? Оптимизация по правилам инвариантна: оптимизатор выбирает пути доступа согласно набору конкретных правил, отраженных в документации на Oracle7. Оптимизатор по стоимости учитывает статистику как по таблицам, так и по индексам и принимает управляемое данными решение о том, какой индекс использовать (если вообще использовать). Вспомните эмпирическое правило о котором мы упоминали выше (в разделе "Отключение индексов"): индекс отключается в случае, если в запросе возвращается более 15—20% строк таблицы. Но как на этапе разработки кода определить, сколько процентов строк будет возвращено? Даже если наш прогноз окажется верным, можно ли с уверенностью сказать, что распределение данных со временем не изменится?

Существует компромисс: предоставить право принять это решение оптимизатору, влияя на него там, где мы посчитаем необходимым (посредством подсказки). Как это сделать и каковы недостатки такого подхода?

Оптимизатор по стоимости работает путем оценки стоимости всех обоснованных вариантов оптимизации запроса и выбора варианта с минимальной стоимостью. "Стоимость" в данном контексте есть оценка числа посещений блоков базы данных и числа операций сетевого ввода-вывода. Использование центрального процессора при этом не учитывается. Расчет производится на основании статистической информации о таблице, хранящейся в словаре данных, которая вычисляется или оценивается по случайной выборке с помощью команды ANALYZE. Эта информация отражает хранение и распределение данных в разрезе таблиц, кластеров и индексов. Собирается следующая статистическая информация:

О таблице:

Общее количество строк
Количество блоков со строками
Количество пустых блоков
Средний объем свободного пространства на блок
Количество связанных блоков
Средняя длина строки

О столбце:

Количество различных значений
Наименьшее значение в столбце
Наибольшее значение в столбце

Об индексе:

Глубина (количество ветвей плюс лист)
Количество блоков-листьев
Количество различных ключевых значений
Среднее Количество блоков-листьев индекса на ключ
Среднее количество блоков данных на ключ
Количество логических блочных операций ввода-вывода при чтении всей таблицы с использованием индекса
(Только в версии 7.3) 75-точечная гистограмма, на которой отражаются индексные ключи; по этим данным степень избирательности любой индексной операции можно оценить с точностью до 2,5%

Даже при наличии столь обширной статистической информации оптимизатору по стоимости приходится делать ряд серьезных предположений относительно ее применимости и надежности (помните: есть ложь, гнусная ложь и статистика!).

Первое предположение состоит в том, что эта статистика точно отражает реальность. Эта информация не собирается автоматически, а вычисляется SQL-командой ANALYZE. Регулярный запуск этой команды является обязанностью администратора базы данных. Это означает, что данная информация, будучи сохраненной в словаре данных, подвержена старению и со временем становится менее надежной (вероятно, это грустное отражение нас самих). В худшем (и невероятном) случае может оказаться, что мы вычислили статистическую информацию, полностью удалили данные из таблицы и загрузили в нее новые данные. Это даст нам статистическую информацию, абсолютно не связанную с данными! Опыт показал, что даже таблицы, подверженные интенсивному обновлению, не проявляют тенденции к сильному изменению своей общей "формы" — поэтому это не очень ужасный кошмар. Тем не менее, следует планировать регулярное обновление статистической информации.

Еще одно предположение, которое неявно делает оптимизатор по стоимости (в версиях до 7.3), состоит в том, что значения в индексе распределены равномерно. Другими словами, если в таблице 500 тысяч строк и индексированный столбец может иметь два значения (например, Y и N), то оптимизатор по стоимости предполагает, что каждое значение встречается 250 тысяч раз, и вообще не будет использовать индекс. Если же N встречается только 100 тысяч раз, то, естественно, более эффективно выбирать эти значения при помощи индекса. На устранение этого недостатка направлены гистограммы в версии 7.3.

Итак, подытожим наши впечатления от оптимизатора по стоимости.

• В версиях до 7.2 его вообще не следует использовать.

• Если вы все же решили его использовать, регулярно выполняйте для всех таблиц команду ANALYZE с целью обновления статистики, если только вы не выполняли глобальный прием или удаление данных; раз в месяц будет достаточно.

• Если вы не работаете с версией 7.3, то применяйте оптимизатор по стоимости только в случае, если индексы, которые, вероятно, будут использоваться для выполнения запроса, имеют довольно равномерное распределение значений.

• Помните о том, что оптимизатор по правилам генерирует очень хорошие пути доступа для хорошо индексированных, высоконормализованных таблиц, к которым обращается аккуратно написанный код. С другой стороны, чем в меньшей степени вы контролируете структуру данных и SQL-код, тем выше вероятность того, что вы должным образом оцените оптимизатор по стоимости.

Дата добавления: 2015-08-09