Автоматическая настройка запросов с помощью SQL Tuning Advisor: получение набора хинтов настроенного профиля и проверка результатов настройки.

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

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

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

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

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

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

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

Отбирать запросы для настройки можно из библиотечного кэша через представление 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

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

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

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

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

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

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

Как уже было отмечено, после настройки необходимо проверить, какой в результате получен выигрыш. Нужно понять, стал ли запрос работать лучше и насколько лучше. При тестировании выполнения запроса достаточно проверять ключевые параметры - количество логических чтений, использование процессорного времени и общее время выполнения запроса. Как можно осуществить эту проверку, т.е. как реально выполнить запрос, содержащий BIND-переменные? В Oracle 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 сек (однако, изменение значения данного параметра в сторону уменьшения может снизить производительность, поэтому в рабочей системе этого делать не стоит). Следует отметить, что подхватываются и отображаются лишь те BIND-переменные, которые имеют простые типы и используются в условиях WHERE и HAVING. BIND-переменные, используемые только в списке выбираемых столбцов, будут иметь значение NULL. Нужно отметить, что просмотреть значения BIND-переменных не удастся в случае установки значения параметра STATISTICS_LEVEL в BASIC. Помимо просмотра значений переменных привязки в V$SQL_BIND_CAPTURE и V$SQL, в содержимом V$SQL_PLAN.OTHER_XML в секции peeked_binds можно обнаружить значения переменных, которые используются при построении плана запроса. Используя известные значения переменных, можно создать динамически выполняемый код для тестирования SQL-запроса с использованием выборки данных через BULK COLLECT в соответствующую запросу коллекцию. Определить набор возвращаемых запросом столбцов и их типов можно с помощью пакета DBMS_SQL, в котором есть соответствующая процедура DESCRIBE_COLUMNS2.

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

Для запуска настройки и выполнения 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 11g, так и в утилитах сторонних производителей, например, Quest SQL Optimizer for Oracle. Если же возникают какие-то особые требования, можно реализовать подобную систему своими силами.

---

Дополнительные материалы: демонстрационные скрипты
Информация по теме: SQL Profiles by Christian Antognini