Analysis and Selection of the Optimizer Mode for Obtaining the Optimal Plan for the Execution of a Query in the ORACLE DBMS

Full Text

ВВЕДЕНИЕ

В настоящее время, в связи с динамично возрастающим объемом обрабатываемой информации, решение проблемы оптимизации производительности при ограниченном наборе ресурсов особенно актуально для эффективной работы автоматизированных систем, основанных на СУБД Oracle [1].

За выработку алгоритма выполнения запроса в Oracle отвечает часть СУБД, называемая оптимизатором. Такой алгоритм в терминологии Oracle называется планом выполнения запроса. [2] При применении неоптимального плана выполнения запроса время реакции СУБД может возрасти на десятки, а то и тысячи раз. В связи с этим встает задача выбора режима оптимизатора, что позволит при правильном его применении сократить потребление ресурсов, избавиться от узких мест в базе данных, уменьшить время отклика, и как следствие, предупредить возможные инциденты производительности [3].

Так как цена ошибки может оказаться велика, очень важно понимать, какие факторы могут оказывать влияние на работу оптимизатора в каждом конкретном случае [4]. Целью данного исследования является анализ таких факторов, а также предоставление рекомендаций по выбору режима оптимизатора, позволяющих получить оптимальный план выполнения запроса.

МЕТОДОЛОГИЯ

Типология практических задач приоритетного выбора оптимизатора представлена в следующих классификациях:

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

В Oracle имеется два режима оптимизатора “Rule based optimizer” (RBO) и “Cost based optimizer” (CBO).

RBO – режим оптимизатора, основанный на анализе определенных правил. Основная его особенность заключается в попытке сформировать план, более предпочтительный с точки зрения эффективного доступа к данным. [5] Степень «эффективности» в данном случае можно получить исходя из фиксированной таблицы методов доступа к данным (таблица 1), ранжированных по критерию предпочтительности [6].

Таблица 1. Метод доступа к данным [Data access method]

Объем информации, используемый этим оптимизатором для выработки плана, достаточно невелик и зависит от следующих факторов [7].

1. Версия СУБД (оптимизатора). В новых версиях разработчику доступно исправление обнаруженных ошибок оптимизатора и добавление новых свойств поведения.
2. Синтаксис запроса. В зависимости от текста оператора SQL одинаковые по смыслу запросы могут иметь разные планы выполнения.
3. Наличие, отсутствие, а также свойства вспомогательных хранимых структур (индексов, кластеров) или основных (индексно-организованная таблица). Этот фактор оказывает значительное влияние на выбор плана. Например, соединение будет выполняться в том же порядке, в котором указаны таблицы во фразе FROM. Но при наличии индекса RBO изменит этот порядок в соответствии с таблицей методов доступа, тем самым кардинально поменяв план одного и того же запроса [4].

CBO – режим оптимизатора, основанный на анализе стоимости. Его задача заключается в оптимизации затрат ресурсов компьютера на выполнение каждого отдельного запроса. К таким ресурсам относятся: процессорное время, расход оперативной памяти и число обращений к диску. По сути, CBO является математическим процессором, использующим формулы для вычисления стоимости оператора SQL. Он вычисляет стоимость всех возможных вариантов исполнения оператора SQL и выбирает один вариант с наименьшей стоимостью [8].

Формула вычисления стоимости оператора SQL имеет вид:

$IO+\frac{CPU}{1000}+NetIO\cdot \mathrm{1,5.}$ (1)

где IO соответствует физическим операциям ввода/ вывода;

CPU – логическим операциям ввода/вывода;

NetIO – логическим операциям ввода/вывода удаленной базы данных.

Физические операции ввода/вывода являются наиболее дорогими, поэтому они составляют основную часть стоимости [9].

Поскольку этот оптимизатор использует значительный объем информации для выработки плана выполнения запроса, то и число факторов, влияющих на выбор оптимального плана больше, чем у RBO [7].

1. Версия СУБД (оптимизатора). В ранних версиях CBO было много ошибок: оптимизатор оказывал «медвежью услугу», непредсказуемо отклоняясь от хороших планов выполнения запросов. В последних версиях отмечается устойчивость планов оптимизатора.
2. Синтаксис запроса – аналогично RBO.
3. Наличие, отсутствие, а также свойства вспомогательных хранимых структур (индексов, кластеров) – аналогично RBO.
4. Настройка параметров инициализации. С помощью этих параметров можно переключить схему работы на более раннюю или новую, повысить или понизить вес определенных узлов в списке вариантов, повысить или понизить вероятность использования индекса и т.п.
5. Наличие или отсутствие собранной статистики по используемым в момент выполнения запроса объектам [4].

РЕЗУЛЬТАТЫ

Рассмотрим случаи, для которых больше подойдет использование RBO либо CBO.

Так как RBO умеет распознавать только правила и не чувствителен к данным, в качестве примера была создана таблица MY_TABLE c двумя полями (ID, OBJECT_NAME), неуникальный индекс IDX_ID для поля ID. При этом статистика по таблице не собрана. Заполним таблицу данными и для первой строки установим ID = 100 (рис. 1).

Рис. 1. Заполнение таблицы MY_TABLE

Получившееся распределение данных в таблице MY_TABLE является неравномерным и содержит одну запись, где ID = 100 и 178804, где ID = 1 (рис. 2).

Рис. 2. Распределение данных в таблице MY_TABLE

Сравним планы выполнения двух SQL-запросов в режиме RBO (рис. 3).

Рис. 3. Сравнение планов выполнения SQL-запросов в режиме RBO

Пример доказывает, что построенный план с использованием RBO является неоптимальным. Так как для ID = 1 большинство данных соответствуют условиям предиката, полное сканирование таблицы для данного случая будет наиболее предпочтительным. Анализируя правила, RBO выбрал метод доступа по индексу, т.к. его ранг выше, чем у полного табличного сканирования (см. табл. 1). Индексирование увеличило дополнительные накладные расходы, что подтверждается статистикой выполнения запросов, представленных на рис. 5 (запросы 1.1, 1.2), т.к. сначала выполняется поиск значения ключа в индексе, а затем доступ к данным по ROWID из полученного ключа.

Построим планы этих запросов в режиме CBO и сравним результаты их выполнения (рис. 4).

Рис. 4. Сравнение планов выполнения SQL запросов в режиме CBO

Для тех же запросов CBO выбрал оптимальный план, т.к. он является чувствительным к данным и корректируется во времени в зависимости от объема данных. Так, при условии ID = 1 выполняется полное табличное сканирование, при ID = 100 сканируется индекс с интервалом. Статика выполнения запросов в режиме CBO представлена на рис. 5 (запросы 2.1, 2.2).

Рис. 5. Статистика выполнения запросов

Несмотря на то, что оптимизатор CBO работает систематизировано, он не всегда использует одинаковый план в похожих случаях. Точность вычисления оптимального плана CBO напрямую зависит от доступа к статистическим данным системы [10]. Создадим таблицу MY_TABLE2 с двумя полями (COLUMN1, COLUMN2), неуникальный индекс IDX_COLUMN2 для поля COLUMN2. Заполним первое поле данными от 1 до 100000, второе поле заполним числом 100. Проанализируем планы выполнения двух запросов до сбора статистики (рис. 6).

Рис. 6. Сравнение планов до сбора статистики

Построенные планы ничем не отличаются. Для второго запроса появилась стоимость на основе defaults. Соберем статистику, используя утилиты DBMS_STATS и процедуры gather_table_stats и gather_index_stats по названию схемы и таблицы(индекса). Сравним планы выполнения этих же запросов после сбора статистики (рис. 7).

Рис. 7. Сравнение планов после сбора статистики

План выполнения первого запроса не изменился и соответствует таблице методов доступа RBO, т.к. для RBO статистика не доступна. Для второго запроса выполнено полное сканирование таблицы, несмотря на имеющуюся подсказку. Это говорит о том, что в действительности сработал режим CBO, т.к. все подсказки, кроме RULE включают CBO, и для этого оптимизатора подсказки являются лишь одним из возможных вариантов, а не прямыми директивами. С помощью применения подсказки можно добиться изменения плана выполнения запроса [11]. Получение альтернативного плана выполнения запроса может быть достаточным для конкретного случая, но более ограниченным по сравнению со случаем наличия актуальной статистики, при котором вариантов выполнения планов может получиться больше, и сами эти планы могут оказаться более подходящими, чем при ее отсутствии.

ОБСУЖДЕНИЕ

Несмотря на то, что в настоящее время Oracle рекомендует использовать режим CBO, в современных IT-компаниях широко применяются оба режима оптимизатора и ведутся споры среди разработчиков о необходимости применения того или иного режимов оптимизатора для конкретного случая, т.к. полученные CBO планы не всегда являются идеальными, поэтому описанная в данной статье проблема выбора режима оптимизатора является особенно актуальной. В научном сообществе практически отсутствуют российские публикации на данную тематику. Среди зарубежных публикаций, в основном, имеются работы, описывающие новые возможности режима CBO, связанные с выходом определенной версии, но не описывающие сравнение двух режимом между собой и конкретные случаи, для которых больше подойдет использование CBO либо RBO [13–15]. Особенностью данного исследования является анализ факторов, влияющих на план выполнения запроса для каждого из режимов, описание преимуществ и недостатков данных режимов, сравнение их применения для конкретных случаев, а также предоставление рекомендаций по выбору режима оптимизатора, позволяющего получить оптимальный план выполнения запроса.

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

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

К недостаткам CBO также можно отнести и то, что он не работает фиксировано во всех версиях Oracle, поэтому планы при смене версий могут отличаться. Это создает дополнительную нагрузку на разработчиков за счет необходимости создания хранимых планов выполнения для возможности использования оптимизатором конкретного плана выполнения запроса и позволяя добиться определенной стабильности.

Также нужно учитывать, что разработчики могут обладать большей информацией, чем CBO, о оптимальном методе доступа для конкретной задачи, т.к., в отличии от CBO, им известны потребности пользователей и узкие места, что позволяет за счет применения подсказок и других методов добиться проактивной оптимизации производительности на этапе разработки [11]. Немаловажное значение имеет квалификация самих разработчиков, т.к. неправильное применение подсказок и синтаксиса запросов может значительно повлиять на производительность системы в худшую сторону [12].

Продуктивность работы CBO напрямую зависит от того, насколько точно собрана статистика. Если статистические данные отсутствует или являются устаревшими, этим оптимизатором могут приниматься очень неэффективные решения. Фактор статистики является главным для данного режима оптимизации. Но в случае, если статистика собирается хорошо и своевременно, стоимостной режим является предпочтительным по сравнению с RBO, т.к. кроме всего прочего, учитывает самые недавние статистические данные по объектам базы данных, использует дополнительные объемы информации и гистограммы распределения данных, что позволяет показывает более высокую скорость выполнения запросов по сравнению с RBO [17].

Главным недостатком RBO является отсутствие гибкости и адаптируемости. Он не умеет определять самый «дешевый» метод доступа и зачастую тратит вслепую ресурсы на реализацию фиксированного набора правил независимо от того, была необходимость в этом или нет [18].

К преимуществам RBO можно отнести стабильность планов выполнения. Так, если стоит задача по масштабированию одной и той же системы для разных клиентов, обладающих разными техническими ресурсами, по-разному ведущими учет статистической информации и администрирование, связанное с настройкой параметров инициализации, то для достижения надежности и предупреждения инцидентов производительности более предпочтительным будет являться использование RBO.

ЗАКЛЮЧЕНИЕ

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

About the authors

Galina A. Unkovskaia

Author for correspondence.
Email: gunkovskaia@gmail.com
ORCID iD: 0000-0001-9348-8102
SPIN-code: 1818-3304

master’s degree

References

Supplementary files

Supplementary Files

Action

1. JATS XML

Download

2. Fig. 1. Filling in the table MY_TABLE

Download (64KB)

Indexing metadata

3. Fig. 2. Distribution of data in the table MY_TABLE

Download (277KB)

Indexing metadata

4. Fig. 3. Comparison of SQL query execution plans in RBO mode

Download (383KB)

Indexing metadata

5. Fig. 4. Comparison of SQL query execution plans in CBO mode

Download (380KB)

Indexing metadata

6. Fig. 5. Query execution statistics

Download (528KB)

Indexing metadata

7. Fig. 6. Comparison of plans before collecting statistics

Download (457KB)

Indexing metadata

8. Fig. 7. Comparison of plans after collecting statistics

Download (464KB)

Indexing metadata