The Search For Intelligent Life in the Cost-Based Optimizer
by Tim Gorman

Вычисление "физического ввода/вывода" по отношению к "логическому вводу/выводу"

В обязанности стоимостного оптимизатора входит вычисление стоимости, которая примерно равна физическому вводу/выводу. Это не так просто, как звучит...

Подсчет операций логического чтения намного проще, и это как раз то, что делает CBO. Стоимостной оптимизатор учитывает информацию в словаре данных о размере таблиц и индексов, количестве строк в таблицах, количестве различных (distinct) ключей в индексах, числе уровней в B*-Tree индексе, среднем числе блоков таблицы на отдельное значение, среднем числе листьевых (leaf) узлов на отдельное значение. Он также знает, как работают четыре метода соединения таблиц, знает информацию о селективности (selectivity) данных в столбцах (или ее отсутствии) из гистограмм в словаре данных, знает различия в работе B*-Tree и Bitmap индексов. Учитывая все эти вещи, формулы, встроенные в CBO (которые Oracle не разглашает), могут быстро и аккуратно определить, как много операций логического чтения можно ожидать для каждого из нескольких, десятков, сотен или тысяч возможных планов исполнения для любого заданного оператора SQL. CBO будет подсчитывать количество логических операций чтения для всех возможных планов исполнения, пытаться преобразовать их к числу физических операций чтения (стоимости) и выбрать наименьшее результирующее значение.

Но как CBO осуществляет переход от общего числа логических операций чтения к общему числу физических операций чтения?

Для операций FTS это относительно просто! Оптимизатор берет общее число логических операций чтения (т.е. число блоков в таблице), делит его на значение параметра DB_FILE_MULTIBLOCK_READ_COUNT, и, о-о-оп ля, мы имеем число физических операций чтения. Это предельно верная формула, поскольку операции FTS характеризуются очень низким (почти отсутствующим) коэффициентом попадания в Буферный Кэш.

Для операций индексного сканирования, которые всегда кэшируются намного лучше, эта формула нуждается в корректировке...

Операции индексного сканирования реализуются с использованием одноблочных операций чтения (для UNIQUE, RANGE и полного индексного сканирования (FAST Index Scan), однако быстрое полное индексное сканирование (FAST FULL Index Scan) использует многоблочные операции чтения аналогично операциям FTS), поэтому корректировка на параметр DB_FILE_MULTIBLOCK_READ_COUNT здесь не используется. Вместо этого, начиная с Oracle8 релиз 8.0 для согласования между логическим и физическим вводом/выводом был введен новый параметр OPTIMIZER_INDEX_CACHING:

CALCULATED-LOGICAL-READS * (1 - (OPTIMIZER_INDEX_CACHING / 100)) = CALCULATED-PHYSICAL-READS = COST

Тем не менее, этот параметр имеет неудачное значение по умолчанию, установленное в ноль. Подставьте значение "0" в формулу, и число логических операции чтения будут транслироваться "один к одному" в число физических операции чтения. Значение по умолчанию приводит данную формулу к тривиальному виду. Этого просто не может быть в реальной ситуации!

Мне нравится устанавливать OPTIMIZER_INDEX_CACHING в значение 90, означающее, что оптимизатор ожидает найти в Буферном Кэше данные для 90% всех логических операций чтения при индексном доступе. Вероятнее всего для большинства приложений этот процент будет близок к 95% или 98%, или даже 100%. Но 90% - это хорошее, умеренное значение для применения. Начните с него, и Вы должны заметить впечатляющую разницу в том, как CBO будет принимать решения.

Не верьте на слово. Испытайте! Проверьте для себя эти утверждения опытным путем.

Вы можете изменить этот параметр, используя команду ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90. Значения лежат в диапазоне от 0 (по умолчанию) до 99. Начните с 90, затем попробуйте различные значения. Я склонен видеть сумасшедшее поведение оптимизатора при значении 99; испытайте это. Запустите эту команду и сделайте EXPLAIN PLAN на запросе, причиняющем особенное беспокойство, после удаления из него подсказок (hints), которые Вы ранее так усердно добавляли. Разница в том, что CBO будет выбирать правильный план. Если Вы найдете, что это происходит достаточно часто при тестовых условиях, возможно, Вы захотите установить этот параметр в файле "init.ora" и посмотреть, что будет происходить с общей производительностью системы при глобальной установке.

Похоже на новую жизнь для старого, угрюмого CBO...