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

Как работает стоимостной оптимизатор

CBO - это математический процессор. Он использует формулы для вычисления стоимости оператора SQL. Стоимость, по сути, приводится к числу физических операций ввода/вывода, то есть к числу логических операций ввода/вывода, которые привели к запросам подстистемы ввода/вывода вместо полной обработки оператора SQL в буферном кэше в Oracle SGA.

Просто ради интереса, тот же самый запрос из примера выше, запущенный на той же базе данных версии 8.1.7 с использованием CBO (вместо RBO), отработал за 0.58 секунды (вместо 21.98 секунд) и произвел только 10 операций логического чтения (вместо 1.6 миллиона). Такое отличие полностью определяется тем, как работает CBO. CBO анализирует все возможные методы доступа, используя ту часть информации из словаря данных, которая доступна RBO и дополнительную информацию о данных (т.е. число строк, размер таблиц и индексов, распределение данных в столбцах и так далее). Затем он вычисляет число логических операций ввода/вывода для каждого возможного метода доступа и выбирает один с наименьшей стоимостью.

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

Рассмотрим в том же ключе процесс игры в шахматы. В действительности, я играю в шахматы примерно также, как работает RBO. Я могу продумать только следующий ход и не способен представить два, три или десять ходов вперед для одного возможного варианта, тем более для нескольких. Я нечасто выигрываю.

В отличие от RBO, CBO определяет стоимость всех возможных планов исполнения. Как и RBO, он работает пошагово, начиная с анализа всех возможных вариантов доступа к таблице, затем рассчитывает последующие соединения или доступ к таблицам. Каждый шаг плана исполнения рассчитывается рекурсивно в данной манере. CBO пробует все возможные варианты (permutations¹⁰)до ограничения, налагаемого параметром инициализации OPTIMIZER_MAX_PERMUTATIONS (по умолчанию 80000). Это достаточно большое число для анализа, и CBO часто рассматривает сотни или тысячи вариантов менее чем за секунду.

ЗАМЕЧАНИЕ: Если Вы сомневаетесь, то существует trace event "CBO trace dump" (event 10053), описанный на моем web-сайте (http://www.EvDBT.com/library.htm) внизу web-страницы. Используя этот event, Вы можете получить информацию обо всех вариантах, принятых во внимание оптимизатором, в трассировочном файле ".trc" в каталоге USER_DUMP_DEST сервера базы данных.

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