SQL Tuning Advisor: хинты и результаты настройки профиля

Автор: Илья Деев, Ассоциация CBOSS, Oracle PL/SQL Developer Certified Associate

Расширенное название этой статьи: "Автоматическая настройка запросов с помощью SQL Tuning Adviser: получение набора хинтов настроенного профиля и проверка результатов настройки". В ней приводится метод получения набора хинтов, соответствующих профилю SQL-запроса, полученного с помощью пакета DBMS_SQLTUNE, а также рассказывается о способе тестирования настроенных запросов.

Oracle Database 10g содержит полезный инструмент SQL Tuning Advisor, который может выдавать советы по настройке SQL-запросов в виде отчетов и создавать так называемые профили, позволяющие при том же исходном наборе объектов (например, без создания новых индексов) построить более эффективный план запроса. Расширяя область применения SQL Tuning Advisor , разработана утилита для автоматизированной настройки SQL, которая может в фоновом режиме определять наиболее ресурсозатратные запросы, автоматически их настраивать и выполнять проверку результатов настройки.

В Oracle Database 11g опция Real Application Testing (RAT) включает функционал SQL Performance Analyzer (SPA), предназначенный для тестирования работы запросов при изменении планов их выполнения (в результате изменения параметров инициализации, создания индексов, настройки профилей и т.п.). Однако, для многих разработчиков еще остается актуальной настройка и проверка запросов в Oracle Database 10g. В статье приведено описание реализации относительно простыми средствами функционала, подобного SPA, применинельно к Oracle Database 10g.

Использование SQL Tuning Advisor

Наиболее интересным результатом работы SQL Tuning Advisor является профиль запроса. По сути, профиль представляет собой набор метаданных, полученных оптимизатором при включении более продвинутого, но и более ресурсоемкого режима (tuning mode). Подготовленные метаданные позволяют использовать заранее настроенный план, который, с точки зрения оптимизатора, является наилучшим для текущего распределения данных в таблицах, текущего набора значений параметров инициализации, существующих индексов и т.д. В отличие от OUTLINES, которые однозначно принуждают выбрать заданный план выполнения, SQL-профили позволяют оптимизатору реагировать на изменение окружения (обновление статистики, изменение значений параметров инициализации, изменения в схеме данных и т.п.).

SQL Tuning Advisor - инструмент очень полезный. Однако, как оказалось, он нередко ошибается. Да-да! Как ни странно, средство, предназначенное для улучшения производительности, при неаккуратном его использовании может приводить к ухудшению производительности настроенных запросов. Впервые пришлось столкнуться с этой проблемой при попытке настройки с помощью DBMS_SQLTUNE одного из сложных иерархических запросов, тщательно настроенного ранее с помощью хинтов.

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

Небольшой пример результатов настройки. На одной из тестовых схем было настроено 64 запроса. Если бы не отсеивались результаты настройки запросов, которые были настроены неудачно, суммарный проигрыш по всему объему чтений настроенных запросов был бы примерно в 5 раз! Однако, после анализа показателей статистики при реальном выполнении запросов были отсеяны результаты ошибочной настройки по 36 запросам. В итоге общий объем чтений сократился в 3 раза. Данный анализ не учитывал количество реальных выполнений каждого запроса в системе (V$SQL.EXECUTIONS), т.е. рассчитан минимально возможный выигрыш, соответствующий однократному выполнению каждого запроса.

Выбор запросов для настройки

Выбирать запросы для настройки можно из библиотечного кэша через представление V$SQL. Параметрами могут служить, в общем случае, все столбцы представления, но особенно полезными являются:

• SQL_TEXT и SQL_FULLTEXT - текст запроса;
• PARSING_SCHEMA_NAME - имя схемы;
• MODULE - имя модуля, которое наследуется из сессии, выполняющей запрос;
• PROGRAM_ID - номер объекта, полезно в случае настройки запросов пакетов, хранимых процедур и функций. Следует также отметить значение в столбце PROGRAM_LINE#, указывающее на конкретную строчку объекта, с которой начинается текст запроса;
• BUFFER_GETS и DISK_READS - количество буферных и дисковых чтений;
• CPU_TIME - время CPU, потраченное на все этапы выполнения запроса;
• ELAPSED_TIME - общее время, потраченное на все этапы выполнения запроса;
• EXECUTIONS - количество выполнений запроса.

Получение набора хинтов, соответствующих профилю запроса

Нередко разработчики стремятся максимально стабилизировать план выполнения запросов, защищаясь от различных неожиданностей, возникающих при смене версии Oracle, при изменении статистики, параметров инициализации и т.п. Один из способов, позволяющих зафиксировать план выполнения, - применение хинтов. Однако, зачастую при подборе правильного набора хинтов возникают затруднения. Иногда бывают ситуации, когда неверно подобранные хинты могут ухудшать план выполнения запроса. Интересной возможностью в этом случае может стать получение набора хинтов на основе настроенного профиля запроса. Оказалось, что SQL Tuning Advisor при настройке сохраняет набор соответствующих хинтов для настроенного SQL-запроса во внутреннем системном виде.

Набор хинтов для соответствующей задачи настройки с номером TASK_ID хранится в столбце OTHER_XML представления USER_SQLTUNE_PLANS в строке столбца ID, имеющей значение 1. Предположим, что задача по настройке запроса называется 'TEST1'. Тогда набор хинтов в системном виде можно получить следующим запросом:

select extractValue(value(d), '/hint') hint_text
  from (select other_xml 
          from user_sqltune_plans
         where task_id = (select task_id 
                            from user_advisor_tasks
                           where task_name = 'TEST1')
           and id = 1
           and attribute = 'Using SQL profile') add_data,
        table(XMLSequence(XMLType(add_data.other_xml).extract('other_xml/outline_data/hint'))) d;

Результат выполнения может быть примерно таким:

USE_MERGE(@"SEL$1" "E"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('optimizer_index_caching' 70)
OPT_PARAM('optimizer_index_cost_adj' 15)
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS

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

USE_MERGE(E)
LEADING(D E)
FULL(E)
INDEX(D PK_DEPT)
ALL_ROWS

Стоит заметить, что при создании плана каждого запроса, начиная с Oracle 10g, в V$SQL_PLAN.OTHER_XML в строке с id= 1 также записывается набор хинтов в системном виде.

Остается перевести представление хинтов из системного вида в обычный. В частности, необходимо преобразовать обозначение индексов в виде названия таблицы с перечисленным набором колонок, в обычное, представляющее собой собственно имя индекса. Одной из сложностей является расстановка хинтов в необходимых местах при настройке сложных запросов. Неудобным для использования оказалось системное представление названий блоков запроса (блоки соответствуют подзапросам). На помощь пришел хинт QB_NAME, позволяющий задать для блоков запроса внутреннее имя. Как оказалось, это имя сохраняется в системном представлении хинтов. Поскольку каждый хинт в системном представлении привязывается к соответствующему блоку запроса, стало возможным расставить хинты в нужных местах запроса. При этом пришлось проигнорировать некоторые хинты, которые относились к промежуточным наборам данных, получаемых при выполнении запросов, например, результатам соединения таблиц через HASH JOIN, которые на уровне исходного текста запроса не имеют имени. Практика показала, что такие упрощения обычно проходят без отрицательных последствий. Таким образом, имеем следующий порядок получения набора хинтов и нового текста SQL-запроса:

1. Очистка текста запроса от старых хинтов (для получения нового текста запроса с новыми хинтами), расстановка хинтов QB_NAME по блокам запроса. Для каждого подзапроса устанавливается собственное название блока.
2. Настройка запроса с помощью DBMS_SQLTUNE.
3. Проверка создания профиля.
4. Если профиль создан, чтение списка хинтов и преобразование их в стандартный вид.
5. Расстановка полученных хинтов в нужных местах запроса.

Судя по результатам настройки, при работе DBMS_SQLTUNE игнорируются большинство заданных в тексте запроса хинтов, в том числе ALL_ROWS и FIRST_ROWS. Но сохраняет свое действие, например, хинт QB_NAME. При этом установка значения параметра OPTIMEZER_MODE оказывает безусловное воздействие на результат настройки. Если профиль был настроен для значения параметра OPTIMIZER_MODE, равного FIRST_ROWS, то при смене значения этого параметра на ALL_ROWS профиль использоваться не будет.

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

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

Если необходимо лишь проверить результаты настройки профиля в рабочей системе (если набор хинтов не интересует), из перечисленных выше пунктов остаются только пункты 2 и 3.

Проверка результатов настройки запросов реальным их выполнением

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

В Oracle Database 10g ответ на этот вопрос упрощается тем фактом, что наконец-то появился интерфейс к значениям BIND-переменных на уровне представлений. Во-первых, в V$SQL и V$SQLAREA появилось поле BIND_DATA типа RAW, в котором содержится набор значений BIND-переменных (чтение можно осуществлять с помощью DBMS_SQLTUNE.EXTRACT_BINDS). Во-вторых, появилось представление V$SQL_BIND_CAPTURE, в котором содержатся периодически обновляемые значения связываемых переменных. Период обновления BIND_DATA в секундах может устанавливаться недокументированным параметром _cursor_bind_capture_interval, значение которого по умолчанию равно 400 сек (однако, изменение значения данного параметра в сторону уменьшения может снизить производительность, поэтому в рабочей системе этого делать не стоит). [Примечание А.Бачина, гл.редактора OM/RE: изменение значений недокументированных параметров ведет к прекращению техподдержки системы со стороны Oracle на все время их действия.] Следует отметить, что подхватываются и отображаются лишь те BIND-переменные, которые имеют простые типы и используются в условиях WHERE и HAVING. BIND-переменные, используемые только в списке выбираемых столбцов, будут иметь значение NULL. Нужно отметить, что просмотреть значения BIND-переменных не удастся в случае установки значения параметра STATISTICS_LEVEL в BASIC.

Помимо просмотра значений переменных привязки в V$SQL_BIND_CAPTURE и V$SQL, в V$SQL_PLAN.OTHER_XML в содержимом XML-элемента peeked_binds можно обнаружить значения переменных, которые используются при построении плана запроса. Используя известные значения переменных, можно создать динамически выполняемый код для тестирования SQL-запроса с использованием выборки данных через BULK COLLECT в соответствующую запросу коллекцию. Определить набор возвращаемых запросом столбцов и их типов можно с помощью пакета DBMS_SQL, в котором есть соответствующая процедура DESCRIBE_COLUMNS2. Следует отметить, что в случае попадания в список столбцов запроса столбца с пользовательским типом данных, этот тип будет определен как XMLTYPE, что приведет к ошибке несоответствия типов при попытке выбрать данные с помощью сформированного автоматически скрипта. Проверить настроенные варианты таких запросов придется, явно указав вручную необходимый тип вместо типа XMLTYPE. Конечно, при тестировании запросов нужно учитывать особенности обработки данных. В случае изменения объема и содержимого таблиц (например, при очистке таблиц, данные в которые сначала загружаются из внешних файлов, а потом очищаются после обработки) настройка запросов в промышленной системе может привести к совершенно неправильным результатам.

Для запуска настройки и выполнения SQL в автоматическом режиме полезно использовать следующие параметры:

• Режим оптимизации (FIRST_ROWS, ALL_ROWS)
• Время настройки каждого запроса (параметр процедуры DBMS_SQLTUNE.CREATE_TUNING_TASK).
• Задание повторного анализа SQL-оператора, который уже был проанализирован. Это полезно, например, при задании большего времени анализа, когда предыдущий анализ был оборван из-за нехватки установленного времени оптимизации. При анализе очередного SQL-запроса для определения того, был ли проанализирован подобный запрос, может использоваться функция пакета DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE в режиме, который игнорирует отличия запросов в переменных, если они заданы литералами.
• Ограничение количества выводимых запросом записей. Реализуется через параметр LIMIT при выборке данных через BULK COLLECT.
• Задание выполнения запросов дважды. Настройка полезна для учета разогрева кэша. Параметры статистики могут браться по второму выполнению запроса.

После выполнения запроса и извлечения результата легко проанализировать показатели статистики, сравнив характеристики выполнения в различных вариантах:

1. Исходный запрос.
2. Исходный запрос без хинтов, если хинты присутствовали в изначальном запросе. (Некоторые SQL-запросы могут иметь неоптимальный набор хинтов, напрасно ограничивающих свободу действий оптимизатора.)
3. Запрос с настроенным и включенным профилем.
4. Запрос с набором полученных из профиля хинтов (для проверки соответствия профиля и набора хинтов).

Не во всех случаях требуется проводить все эксперименты. Если профиль не был настроен с помощью DBMS_SQLTUNE, то выполнение запроса в вариантах 3 и 4 исключается. Если исходный запрос не имел хинтов и для него не был настроен профиль, результаты выполнения не с чем сравнивать.

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

Резюме.

Разработанная на основе описанных принципов утилита позволяет получить по результатам работы SQL Tuning Advisor текст SQL-запросов с набором хинтов, задающим наиболее оптимальный, с точки зрения оптимизатора, план выполнения. Получаемые наборы хинтов также могут послужить хорошими обучающими примерами в случае активного использования хинтов при настройке запросов. Реальное выполнение запросов до и после настройки позволяет численно оценить выигрыш от настройки по ключевым показателям (использование CPU, объем чтений, время выполнения). Однако, необходимо иметь в виду преимущества непосредственного использования SQL Tuning Advisor на рабочей базе данных: настройка запросов на реальных данных, возможность изменения плана в лучшую сторону при изменениях в схеме данных, изменении параметров инициализации или распределения данных в таблицах. В случае настройки запросов на тестовой системе необходимо воспроизвести реальные объемы данных или хотя бы реальное относительное распределение данных в таблицах, запросы к которым настраиваются. Также желательно тестировать настройку запросов с разными значениями BIND-переменных.

Помимо анализа эффективности профилей, описанные подходы тестирования могут быть полезны при исследовании влияния различных изменений на выполнение запросов. Можно создавать наборы совместно тестируемых запросов, группируя их по модулям, по подсистемам, по таблицам, к которым происходят обращения и другим произвольным признакам. Сохранение параметров статистики и значений настроек, влияющих на работу оптимизатора при выполнении запросов, может дать картину влияния изменения окружения на потребление ими ресурсов. Данный функционал реализован как в SPA в Oracle Database 11g, так и в утилитах сторонних производителей, например, Quest SQL Optimizer for Oracle. Если же возникают какие-то особые требования, можно реализовать подобную систему своими силами.

• Обратиться в "Интерфейс" за дополнительной информацией/по вопросу приобретения продуктов
• Подробнее о продуктах Oracle
• Приобрести продукты Oracle в ITShop.ru
• Курсы по продуктам Oracle

• Демонстрационные скрипты