Сколько стоит Стоимость?

Причиной, исписать эту страничку стал однотипный вопрос, достаточно часто задаваемый разработчиками: "Что такое стоимость (cost) запроса"?

Каждый, кто пишет код под базы данных Oracle, рано или поздно сталкивается с ситуацией, когда необходимо улучшить тот или иной запрос. Далее, как правило, начинается "проба пера": некоторое количество тестовых итераций, из которых в дальнейшем программист выбирает лучшую. Здесь и далее я подразумеваю, что используется стоимостной оптимизатор (CBO).

Критерий выбора очевиден - это время выполнения запроса. По моим скромным наблюдениям, чаще всего лучший вариант ищут простым перебором всех разумных (и не разумных) вариантов, реже - с учетом стоимости запроса на уровне больше/меньше, еще реже - анализируя почему растет/уменьшается эта самая стоимость.

Стоимость запроса - это ОЦЕНКА оптимизатора относительно эффективности запроса. У нее нет реальных единиц измерения. Поэтому Вы можете представлять ее себе в $$, рублях ;), секундах, единицах работы и т.д. Стоимость запроса скорее подразумевает некие абстрактные единицы измерения и служит лишь для относительной оценки эффективности вариантов запроса на уровне больше/меньше, хуже/лучше.

Величина стоимости запроса весьма слабо связана с типом доступа к данным. Не верьте тем, кто говорит Вам, что стоимость запроса, использующего к примеру диапазонное сканирование индекса, должна быть меньше 50, а иначе запрос явно неэффективен. Какая стоимость должна быть у оптимального запроса? К сожалению, я не могу вам ответить на этот простой и логичный вопрос потому, что он не совсем корректен. Так же как вряд ли кто ясно сможет сформулировать определение "оптимального запроса".

В целом, значение стоимости запроса есть функция оцениваемого (опять таки) числа операций логического чтения блоков данных. При вычислении стоимости оптимизатор не учитывает эффект кэширования блоков данных. Поэтому, на мой взгляд, разработчик, ориентируясь при оптимизации запросов на значение стоимости, не должен делать смутных предположений относительно того, какая часть блоков будет извлечена из кэша. Если так проще, считайте, что все данные будут читаться с диска. Оптимизатор (до версии 8.1.7 включительно) не учитывает затраты CPU (обещано в версии 9i), оценивается только ввод/вывод.

Логика, которой руководствуется оптимизатор, достаточна сложна. При расчете стоимости во внимание принимаются десятки факторов и их комбинаций, на которые, в свою очередь, накладываются поправочные коэффициенты, учитывающие вероятностные характеристики, тип запроса и пр. Перечисление этих факторов и различных ситуаций "А ЕСЛИ" потребует от меня гораздо больших усилий и времени и, видимо, займет значительно больше места чем эта страничка. Что еще хуже, большинство умалчиваемых характеристик (например default selectivity) меняются от версии к версии. Что постоянно? Общая стратегия оптимизатора, согласно которой он пытается в соединениях в качестве driving-таблицы выбрать таблицу с наименьшим значением cardinality, чтобы минимизировать эту характеристику для общего результата.

В силу индивидуальности каждого запроса и объема данных, которыми он оперирует, нельзя говорить о стоимости как о величине, однозначно (абсолютно) определяющей эффективность запроса. Однако Вы можете сравнивать стоимость нескольких вариантов запроса. Чем ниже стоимость - тем эффективнее запрос.

Но не всегда и это правило работает. Вы, возможно, могли время от времени наблюдать ситуацию, когда запрос с большей стоимостью отрабатывает быстрее другого варианта, стоимость которого ниже. Действительно, такая ситуация встречается и этому может быть несколько причин. Однако, если в данном случае разница в стоимости (или времени выполнения) двух вариантов запроса существенна, то вероятно оптимизатор ошибается. Здесь под словом "ошибается" я подразумеваю тот факт, что оптимизатор выбирает не самый оптимальный план из всех возможных. Стоимость - это не более чем ОЦЕНКА оптимизатора. Если оптимизатор ошибается - значит его оценки (стоимость в их числе) неверны. Оптимизатор может выбрать не самый оптимальный план по следующим причинам:

неправильная оценка значений bind-переменных
не собрана или устарела статистика на таблицах и индексах, вовлеченных в запрос
в запросе не хватает данных, позволяющих оптимизатору сделать более точную оценку
ошибки в коде самого оптимизатора

Рассматривайте стоимость запроса как некую прогнозируемую величину, которая как и любой прогноз (погоды например :) не всегда объективна.
Однако, я не разделяю мнения скептиков, которые считают, что CBO ошибается в половине случаев. Мой скептис "укладывается" в 2-3% ;) Однако, если код написан плохо, не поможет никакой суперинтеллектуальный оптимизатор.

Вы можете также взглянуть на пример когда ошибается CBO.

Надеюсь, что не запутал Вас еще больше ;) и что-то оказалось полезным на этой страничке.

PS. Спасибо Вячеславу Лейчинскому за объективные замечания по этой страничке.