Практика использования хинтов в Oracle. Подстановка хинтов в SQL-запрос без модификации запроса

 ВАЛЕРИЙ МИХЕИЧЕВ, эксперт Oracle, ОСАО «Ингосстрах», Valery.Mikheitchev@ingos.ru

Практика использования хинтов в Oracle
Подстановка хинтов в SQL-запрос без модификации запроса

В процессе эксплуатации баз данных иногда возникает необходимость скорректировать планы SQL-запросов, не прибегая к изменению текста SQL-запроса

В этом могут помочь методы, появившиеся в Oracle, начиная с Oracle 9i и развитые в Oracle 11g, позволяющие подставлять хинты (подсказки оптимизатору) в запрос, не меняя текст работающего запроса и не компилируя заново хранимую процедуру (функция, процедур, пакет), в которой он находится.

Практика показала, что эти методы особенно эффективны в период перехода от одной версии Oracle к другой. Так, например, при переходе с версии с Oracle 11.2.0.3 на 11.2.0.4 или на Oracle 12c планы выполнения запросов, которые были оптимальны в предыдущей версии, порой становятся далеко не оптимальными.

В этом случае вернуться к оптимальному плану выполнения позволяют разные приемы, например модификация структуры запроса или добавление хинта (например, хинта index) в запрос с последующей перекомпиляцией хранимой процедуры, в которой он находится. Может быть также применен хинт в запросе, возвращающий работу оптимизатора к предыдущим версиям Oracle, в которых планы выполнения были оптимальными. Как показала практика, в Oracle 11g вэтом могут помочь хинты:

/*+ optimizer_features_enable=’10.2.0.5’ */

/*+ optimizer_features_enable ('11.1.0.7') */

При наличии проблем с планами выполнения в запросах с bind-переменными (связанными переменными) оптимизировать план выполнения запроса может помочь (помимо хинта, такого как index) еще хинт:

/*+ opt_param ('_optim_peek_user_binds' 'false') */

Данный хинт изменяет значение недокументированного параметра инициализации _optim_peek_user_binds, влияющего на планы выполнения с bind-переменными, с true (устанавливаемый по умолчанию) на значение false.

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

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

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

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

Начиная с Oracle 9i появился метод замены или добавления хинтов в запрос, к которому нет доступа на редактирование, известный как метод использования хранимых шаблонов (Stored Outlines). Данный метод позволяет не только подставлять, но и заменять существующий хинт на другой, причем как в основном запросе, так и в подзапросах у запроса.

Вместе с тем данный метод не нашел у нас широкого применения, в силу того, что столкнулись с проблемой использования этого метода в запросах, в которых имеются bind-переменные (т.е. в запросах типа SELECT * FROM agreement WHERE isn=:b), в то время как большинство запросов у нас базируется на bind-переменных.

Существуют другие методы подстановки хинта в запрос, например SQL profiles, вместе с тем у нас более востребованными оказались методы, появившиеся в Oracle 11g, такие как SQL plan baseline и SQL patch. Это обусловлено удобством работы с ними и возможностью работы с запросами, которые содержат bind-переменные.

Статью целиком читайте в журнале «Системный администратор», №09 за 2016 г. на страницах 58-61.

PDF-версию данного номера можно приобрести в нашем магазине.

Комментарии могут оставлять только зарегистрированные пользователи