Оптимизатор запросов в СУРБД Оracle

Источник: С. Н. Смирнов, И. С. Задворьев.
Источник: Работаем с Oracle: Учебное пособие. М: Гелиос АРВ, 2002. — с. 417-445

Оптимизатор запросов представляет собой программный продукт, который является важнейшей частью сервера Oracle и пред назначен для оптимизации — поиска наиболее эффективного способа доступа к данным. Оптимизатор должен выбрать та кую последовательность действий, которая обеспечит самый эффективный путь доступа к данным, и сформировать план выполнения запроса, основанный на найденных методах доступа. Под методом доступа (access path) подразумевается вариант алгоритма доступа, а под планом выполнения (execution plan) — последовательность выполняемых действий, которые обеспечивают выбранные методы доступа. Су ществует два основных вида оптимизаторов:

Для управления оптимизатором используются специали- зированные подсказки (hint), которые записываются в SQL- выражениях. Подсказки влияют на выбор сервером конкрет ного способа обращения к данным. Представленный в лис тинге пример содержит выражение SELECT, включаю- щее подсказку оптимизатору для использования индекса. Здесь предполагается, что для указанной таблицы существует только один индекс. Для задания в подсказке конкретногр индекса из нескольких имеющихся следует использовать имя таблицы и имя нужного индекса.

SQL> SELECT /*+ index*/ At2,At3 2 FROM Tab! WHERE Atl=10;

Подсказка является частью комментария, следующего сразу же после начала оператора SQL (ключевых слов DELETE, UPDATE, SELECT), и обозначается символом "+". после конструкции, начинающей комментарий. В предыду- щем примере подсказка начинается с символов "/*", за кото рыми следуют символы "+", пробел и заканчивается симво лами "*/". Пример другой записи подсказки приведен в лис тинге:

SQL> SELECT --+ index 2 At2, At3 FROM Tabl WHERE Atl=10;

Оптимизатор воспринимает только один набор подсказок, оформленный как комментарий, для данного SQL-выражения или блока выражений. Например, для составного запроса, со стоящего из двух подзапросов, объединенных оператором UNION, подсказка в первом выражении будет учитываться, при формировании плана только для него и не будет учиты ваться для второго. Подсказки с неверным синтаксисом игно рируются, начиная с места ошибки.

Ранжирование методов доступа

Интуитивно понятно, что обращение к данным с исполь зованием различных методов доступа будет отличаться как по применяемым механизмам, так и по времени и эффективно сти. Оптимизатор Oracle при работе использует ранги мето дов доступа. Сипсок содержит список рангов методов доступа в порядке эффективности: от самого быстрого до са мого медленного.

1. Одна строка по ее идентификатору (по ROWID)

2. Одна строка по объединению кластеров

3. Одна строка по хэш-ключу кластера с уникальным или первичным ключом

4. Одна строка по уникальному или первичному ключу^

5. Объединение кластеров

6. Ключ хэш-кластера

7. Ключ индексированного кластера

8. Составной индекс

9. Индекс на основе одного столбца

10. Ограниченный диапазон поиска по индексированным столбцам

11. Неограниченный диапазон поиска по индексированным столбцам

12. Соединение через сортировку слиянием

13. Поиск максимального или минимального значения по индексированным столбцам

14. Упорядочение по индексированным столбцам

15. Полное сканирование таблицы

Таким образом, если оптимизатор основан на анализе правил, то он будет выбирать из всех доступных для данного запроса планов выполнения наиболее эффективный, опираясь на возможные методы доступа и ранги этих методов. С по мощью некоторых изменений записи выражений SQL можно способствовать более удачному выбору метода доступа. При мер в листинге содержит тот же запрос, что и в предыду щем примере, но с измененным оператора критерием отбора данных.

SQL> SELECT —+ index 2 At2,At3 FROM Tabl WHERE Atl>45612367;

При использовании стратегии, основанной на анализе правил, и наличии первичного ключа — столбца Atl, для вы полнения этого запроса будет применен метод доступа II (неограниченный диапазон поиска по индексированным столбцам).

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

SQL> SELECT At2,At3 FROM Tabl 2 WHERE Atl>45612367 AND Atl<99999999;

Анализ запросов с целью повышения скорости их выполнения

Изменение SQL-выражений на основе знаний о данных, индексах, связях таблиц для повышения эффективности их выполнения, называется коррекцией запросов (query rewriting). Изменение предложений SQL отличается от напи сания новых предложений. Для того чтобы эффективно пере писывать запросы, необходимо в течение некоторого времени накопить знания о системе. Сюда относятся сведения о том, какие предложения SQL нуждаются в переписывании в связи с их частым использованием или использованием ими значи тельных ресурсов, какие данные ими обрабатываются, каковы характеристики и распределение этих данных, какие логиче ские условия в выражениях можно убрать или трансформиро вать в связи с логикой функционирования системы. При ре шении задач оптимизации проблемных запросов необходимо следовать следующим рекомендациям:

1. Во-первых, при необходимости доступа к значительной части строк какой-либо таблицы полное сканирование (full scan) является более эффективным, чем использование ин дексов. Граница применения данных методов доступа в об щем случае составляет 5-10% записей таблицы, к которым обращается запрос. Дело в том, что для сканирования индекса и извлечения строки требуются, по крайней мере, две опера ции чтения для каждой строки (одна — для чтения индекса, другая для чтения данных из таблицы). А при полном скани ровании таблицы для извлечения строки требуется только одна операция чтения. При доступе к большому количеству строк становится очевидной неэффективность использования индекса по сравнению с полным сканированием таблицы, при котором строки считываются непосредственно из таблицы. Для небольших таблиц полное сканирование практически всегда оказывается эффективнее использования индекса.

2. Во-вторых, на различных этапах выполнения запросов следует максимально использовать результаты предыдущих этапов. Например, если результирующий набор данных тре буется отсортировать по значениям некоторого столбца, то при выполнении операции соединения таблиц можно указать способ выполнения этой Операции, при котором будет прове дена сортировка этих значений. Полученные результаты бу дут использованы при окончательной сортировке.

3. В-третьих, при использовании различных видов подза просов на основе знаний о данных следует учитывать особен ности вычисления специальных предикатов и применения операторов теоретико-множественных операций. Например, оператор MINUS может выполняться гораздо быстрее, чем запросы с WHERE NOT IN (SELECT) или WHERE NOT EXISTS.

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

Задание режима оптимизации

Одним из важнейших вопросов при работе с оптимизато ром является выбор режима его работы. Описанные выше ви ды оптимизации могут быть заданы на уровне экземпляра, сессии или SQL-выражения. Указание режима оптимизации на уровне экземпляра осуществляют администраторы баз данных с помощью параметров инициализации экземпляра. Для указания режима оптимизации на уровне сессии следует использовать выражение следующего вида: ALTER SESSION SET ОРТ1МКЕК_ООАЬ=<режим>;

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

Если в запросе указана подсказка ALL_ROWS или FIRST_ROWS, но словарь данных не содержит статистики для таблиц, перечисленных во фразе FROM, то оптимизатор воспользуется сведениями о параметрах хранения этих таб- лиц, чтобы оценить статистику и на этой основе выбрать план исполнения. Для задания режима оптимизации на уровне вы- ражения следует использовать ключевые слова методов дос- тупов, либо использовать одно из приведенных выше значений (CHOOSE, RULE, FIRST_ROWS, ALL_ROWS). При использовании в SQL- выражении любой подсказки, отличной от RULE, осуществ- ляется автоматический выбор оптимизатора по стоимости!

Подсказки, специфицирующие метод доступа:

Обзор индексов Oracle

Понятие индекса было введено при изложении назначе ния основных объектов базы данных Oracle. Напомним, что индекс — это объект базы данных, предназначенный для по вышения производительности при проведении выборки дан- ных. Цель использования индекса состоит в том, чтобы полу чить требуемые в запросе данные более эффективным, по сравнению с полным просмотром таблицы, способом. Индек сы улучшают производительность тех запросов, которые вы бирают небольшой процент строк из таблицы с помощью от носительно простых условий во фразе WHERE. Индексиро вать целесообразно лишь столбцы, обладающие высокой се лективностью.

Селективность (selectivity) столбца — это процент строк, имеющих одинаковое значение для индексированного столб ца. Селективность столбца высокая, если мало строк имеют одинаковые значения для этого столбца. Oracle автоматиче ски создает индексы для первичных ключей или столбцов, для которых существует ограничение на уникальность значе ний. Эти индексы наиболее эффективны (что видно из табли цы рангов методов доступа). Столбцы с мало различающими ся значениями имеют низкую селективность. Отметим важ ный факт — если для индексированного столбца логическое условие накладывается не на значение столбца, а на результат некоторой функции от него, то этот индекс не используется.

Индексы бывают двух видов — простые и составные. Со ставной индекс — это индекс, включающий более чем один столбец. Можно совместно проиндексировать два или более столбца, каждый из которых обладает низкой селективно стью, а пара их значений — высокой. Если все столбцы, ис пользуемые запросом, входят в составной индекс, то обраще ния к таблицам можно вовсе избежать — вся данные будут считаны только из индекса. Для эффективного использования составного индекса необходимо, чтобы логические условия были наложены на ведущие столбцы индекса, то есть на те столбцы, которые были указаны первыми при создании ин- декса.

В индексах хранятся значения из одного или нескольких стоЛ*бцов таблицы и значения ROWID для каждого из храни- мых значений столбца (столбцов). Сервер Oracle, чтобы найти строку в таблице по заданному значению столбца, ищет соот- ветствующие ROWID в индексе и затем сразу переходит к указанным ими строкам в таблице. В Oracle имеется несколь- ко типов индексов:

Эффективное кодирование SQL-выражений

Почти все типичные проблемы, возникающие при на стройке производительности, связаны с неэффективным ко дированием SQL-выражений либо с неоптимальным исполь зованием индексов.

Одной из наиболее очевидных проблем эффективного кодирования SQL-выражений является следующая. Если за ранее известно, что запросом будет возвращено большое ко личество строк таблицы, то более целесообразным является использование такого метода доступа, как полное сканирова ние (особенности использования полного сканирования таб лиц рассматривались в предыдущих параграфах). Другая проблема заключается в избыточном сканировании и может быть проиллюстрирована в приведенном ниже примере. Тре буется выполнить два запроса:
SELECT Atl FROM Tab! WHERE Atl>9; SELECT At2/Atl FROM Tabl WHERE Atl>9;
Очевидно, что требуемые результаты могут быть получе ны с помощью одного сканирования:
SELECT Atl,At2/Atl FROM Tabl WHERE Atl>9;
Также не следует выбирать данные из таблицы, если идентичные сведения можно вывести каким-то другим спосо бом. Следует руководствоваться следующим общим прави лом: лучше пожертвовать расходами на дополнительное вы числение, чем на дополнительные операции с устройствами хранения. Исключение составляют выражения, в которых большое число раз вызываются встроенные функции SQL (значения которых вычисляются для каждой строки больших таблиц или их соединений). В этом случае следует пересмот реть выражение и исключить лишние вызовы.

Особенно внимательно следует отнестись к использова нию операторов DISTINCT, ORDER BY и UNION, поскольку при выполнении этих операций происходит создание времен ных таблиц с дополнительными накладными расходами на выполняемую сортировку. При необходимости использования оператора DISTINCT желательно сохранять результаты его выполнения для последующего использования. Вместо опе ратора UNION рекомендуется применять оператор UNION ALL (если это допускается логикой запроса), при использова нии которого не выполняется исключение дубликатов и, со ответственно, сортировка. Рассмотрим использование операторов теоретико- множественных операций MINUS и INTERSECT. Как было указано ранее, оператор UNION и подзапросы могут в значи тельной степени повлиять на производительность приложе ния, поэтому вместо него следует использовать операторы MINUS и INTERSECT. Рассмотрим в качестве примера такой запрос:
SELECT Atl FROM Tab! WHERE Atl NOT IN (SELECT Atl FROM Tab2);.
Его можно переписать в таком виде:
SELECT Atl FROM Tab! MINUS'SELECT Atl FROM Tab2;
Оба этих запроса фактически возвращают один результат (если результирующий набор не содержит дубликатов). С по мощью просмотра статистических сведений о выполнении запросов можно обнаружить, что общее количество логиче ских операций чтения для первого варианта запроса гораздо больше, чем для второго. Использование оператора MINUS может оказаться очень эффективным.

Логические выражения используются для оценки истин ности (с результатом TRUE) или ложности (с результатом FALSE) высказываний. Использование встроенных функций Oracle для гибкой подстановки в логические выражения зна чений данных может значительно улучшить производитель ность системы. Рассмотрим в качестве примера таблицу, в которой регистрируется наступление некоторых событий. Пусть требуется узнать число событий, произошедших до 1 марта 2002 года, между 1 марта 2002 и 1 апреля 2002 года и с 1 апреля 2002 года по настоящее время. Первое решение — выполнить три следующих запроса:
SELECT COUNT(*) FROM Tab2 WHERE At2 < TO_DATE('01032002', 'ddmmyyyy'); SELECT COUNT(*) FROM Tab2 WHERE At 2 BETWEEN TO_DATE (' 01.032002 ',' ddmmyyyy') AND TO_DATE('01042002','ddmmyyyy1); SELECT COUNT(*) FROM Tab2 WHERE At2 > TO_DATE('01042002', 'ddmmyyyy');
Для получения всей необходимой информации, скорее всего, придется три раза выполнить полное сканирование таблицы ТаЬ2. Этот пример хорош тем, что в нем приходится иметь дело с тремя разными интервалами, которым должно принадлежать значение столбца At2 и, на первый взгляд, не понятно, каким образом можно применить здесь двузначную логику? Прежде всего, следует выполнить логическое преоб разование. В Oracle для этой цели предусмотрена специальная функция DECODE. При внимательном ознакомлении с при веденным ниже примером ее использования можно обнару жить, что данный запрос приводит к тем же результатам, но за счет всего одного сканирования:

SELECT SUM(DECODE(SIGN{TO_DATE('01032002','ddmmyyyy')- At2),-1,0,1)) suml, SUM(DECIDE(SIGN(At2- TO_DATE('01032002','ddmmyyyy')),-1,0, DECODE (SIGiJ(TO_DATE('01042002' ,'ddmmyyyy')- At2),-1,0,1))) sum2, SUM(DECODE(SIGN(At2- TO_DATE( '01042002','ddmmyyyy')),-1,0,1}) sum3 FROM Tab2;
Эффективность выполнения данного запроса, по сравне нию с исходными тремя, будет просто невероятной. Однако читаемость программы и удобство ее сопровождения заметно ухудшаются. Хотя, если вспомнить определения функций DECODE, SIGN и семантику операций с датами в Oracle, ло гика вычисления выражений suml, sum2, sum3 становится прозрачной.

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

Изменение плана выполнения запроса

При формировании плана выполнения запроса оптимиза тор обычно принимает верные решения. Как правило, 95% всех запросов в системе не нуждаются в дополнительной на стройке. Оставшиеся 5% проблемных запросов подлежат Ли бо ручной коррекции, которая была рассмотрена выше, либо принудительному изменению и фиксации плана с помощью подсказок. На основе сведений о данных в таблицах для за просов, которые выполняются недостаточно быстро, пользо- ватель может выбрать иной план выполнения, чем тот, что автоматически генерирует оптимизатор.

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