oracle baseline что это

23 Managing SQL Plan Baselines

You can manage SQL plan baselines using the DBMS_SPM package.

This chapter contains the following topics:

About SQL Plan Management

SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.

The main components of SQL plan management are as follows:

This section contains the following topics:

Purpose of SQL Plan Management

The primary goal of SQL plan management is to prevent performance regressions caused by plan changes. A secondary goal is to gracefully adapt to changes such as new optimizer statistics or indexes by verifying and accepting only plan changes that improve performance.

SQL plan baselines cannot help when an event has caused irreversible execution plan changes, such as dropping an index.

Benefits of SQL Plan Management

SQL plan management can improve or preserve SQL performance in database upgrades and system and data changes.

Specifically, benefits include:

A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements.

Most plan changes result in either improvement or no performance change. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade.

When you upgrade, the database only uses plans from the plan baseline. The database puts new plans that are not in the current baseline into a holding area, and later evaluates them to determine whether they use fewer resources than the current plan in the baseline. If the plans perform better, then the database promotes them into the baseline; otherwise, the database does not promote them.

Ongoing system and data changes can affect plans for some SQL statements, potentially causing performance regressions.

SQL plan baselines help minimize performance regressions and stabilize SQL performance.

Deployment of new application modules introduces new SQL statements into the database.

The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.

Oracle Database Upgrade Guide to learn how to upgrade an Oracle database

Differences Between SQL Plan Baselines and SQL Profiles

Both SQL profiles and SQL plan baselines help improve the performance of SQL statements by ensuring that the optimizer uses only optimal plans.

Both profiles and baselines are internally implemented using hints. However, these mechanisms have significant differences, including the following:

In general, SQL plan baselines are proactive, whereas SQL profiles are reactive.

Typically, you create SQL plan baselines before significant performance problems occur. SQL plan baselines prevent the optimizer from using suboptimal plans in the future.

The database creates SQL profiles when you invoke SQL Tuning Advisor, which you do typically only after a SQL statement has shown high-load symptoms. SQL profiles are primarily useful by providing the ongoing resolution of optimizer mistakes that have led to suboptimal plans. Because the SQL profile mechanism is reactive, it cannot guarantee stable performance as drastic database changes occur.

Figure 23-1 SQL Plan Baselines and SQL Profiles

Description of «Figure 23-1 SQL Plan Baselines and SQL Profiles»

SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.

A SQL plan baseline is a set of accepted plans. Each plan is implemented using a set of outline hints that fully specify a particular plan. SQL profiles are also implemented using hints, but these hints do not specify any specific plan. Rather, the hints correct miscalculations in the optimizer estimates that lead to suboptimal plans. For example, a hint may correct the cardinality estimate of a table.

Because a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline. For example, changes in initialization parameters and optimizer statistics enable the optimizer to choose a better plan.

Oracle recommends that you use SQL Tuning Advisor. In this way, you follow the recommendations made by the advisor for SQL profiles and plan baselines rather than trying to determine which mechanism is best for each SQL statement.

Источник

Oracle baseline что это

Рассматривается система управления планами (SPM), введенная в версии Oracle 11 применительно к повторяющимся запросам приложения. Она позволяет формировать и хранить для запросов допустимые наборы планов (baselines), заставить СУБД работать только по ним и тем самым избежать в отдельных случаях непредусмотренного падения производительности при обработке.

Введение

Как известно, СУБД, получив от приложения запрос, сначала строит программу вычисления ответа («план»), и тут же эту программу отрабатывает. Теоретически план можно построить наилучшим образом: получить множество всех возможных для данного запроса планов и выбрать из них наиболее поизводительный в нужном отношении. На практике же любая промышленная СУБД, включая Oracle, вырабатывает план на скорую руку, достигая при этом приемлемого времени ответа, но зато жертвуя точностью решения задачи. Как следствие, применяемые к поступающим запросам планы нередко оказываются не самыми лучшими, и общая производительность СУБД страдает.

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

Исторически первым инструментом последней категории стали «очертания» (outline) запросов, появившиеся в версии 8.1. «Очертание» запроса – это план, «схваченный» в подходящий момент времени и сохраненный для последующего употребления. Пользователь получает возможность в пределах конкретных сеансов «включать» «очертание», заставляя СУБД пользоваться этим конкретным планом независимо от текущих обстоятельств. После версии 11 эта техника фиксации плана прекращает свое официальное существование.

Вторым по времени инструментом стал «профиль» (profile) запроса, введенный в версии 10. Профили могут возникнуть в результате специальной процедуры анализа СУБД запроса, представляющего интерес, в рамках работы советника SQL Tuning Advisor. Пользователь имеет право в любой момент включить имеющийся профиль, и тогда текущий план запроса подкорректируется в сторону улучшения. Влиянием на применяемый СУБД план будет включение и отключение профиля по мере необходимости.

В версии 11 в Oracle появилось третье по счету средство указания СУБД, каким определенным планом ей воспользоваться при обработке того или иного запроса. Оно получило название «управления планами»: SQL plan management (SPM). Его основное назначение – не дать возможность пустить обработку повторяющихся запросов приложения по «плохому» плану. «Плохим» же план может оказаться ненароком при смене обстоятельств очередного поступления запроса – значений переменных привязки, статистики объектов, переменных СУБД или сеанса и даже версии СУБД или ОС.

Система управления планами запросов

Режим учета SPM (использования основных линий планов) устанавливается значением TRUE параметра СУБД OPTIMIZER_USE_SQL_PLAN_BASELINES. Это значение умолчательное. Изменить его можно как на уровне СУБД, так и отдельных сеансов.

При поступлении в СУБД запроса, для него вырабатывается план. Далее, если OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE, запрос выполняется по этому плану. Если = TRUE, и план присутствует и в истории планов, и в основной линии, он также принимается к исполнению. Если же план отсутствует либо в истории, либо в основной линии, для исполнения запроса будет выбран наиболее «легкий» из имеющихся в основной линии. Но если план отсутствует в истории, он дополнительно будет туда занесен.

Содержимое SMB представлено в таблице DBA_SQL_PLAN_BASELINES. На деле это, конечно, виртуальная таблица, то есть view, показывающая данные из реальных таблиц SQLOBJ$, SQLOBJ$AUXDATA и SQL$TEXT в схеме SYS). Эти данные общесистемные, а потому аналогичных таблиц с префиксами USER и ALL не существует. Вот некоторые поля этой таблицы:

Помимо этого сведения о базовой линии планов для запроса в текстовом виде предоставляет функция DISPLAY_SQL_PLAN_BASELINE из пакета DBMS_XPLAN.

Совершению действий с SPM служит пакет DBMS_SPM. Этот же пакет используется в OEM для графического доступа к его собственной функциональности.

Далее SPM рассматривается на примере употребления непосредственно программным образом.

Подготовка к примеру

Для примера подобран нереально простой запрос. Это сделано осознано воимя доходчивости изложения техники.

В примере будут переключения в схемы SCOTT и SYS, но предполагается, что работа выполняется в SQL*Plus без выхода из этой программы, что позволит сохранить значения переменных.

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

Предполагается, что основная линия запросов изначально пуста. Исходно план запроса не зависит от того, применяет оптимизатор управление планами, или нет:

Загрузка плана в базу управления запросами

Вот случай, достойный памяти и занесения в литературу!

Плиний Младший, Панегирик императору Траяну

Сейчас для интересующего нас запроса СУБД завела рабочую память в общей области курсоров в shared pool. Загрузим оттуда план (первый по счету) в основную линию в базе управления запросами SMB, сославшись на идентификатор курсора SQL ID:

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

Несмотря на то, что в нашем случае запрос попал в SMB по ссылке на SQL ID, в самой базе он идентифицируется ключом SQL_HANDLE, который автоматически порождается по подписи запроса, в свою очередь вычисляемой по нормализованому тексту. Это позволяет хранить план в AWR долговременно, независимо от того, представлен ли запрос вообще в курсорной области в данный момент, и под каким именно SQL ID представлен.

Обратите внимание, что использованный способ загрузки плана в основную лонию автоматически выставил признаки ENABLED и ACCEPTED в состояние ‘YES’, то есть единственный пока план в SMB и в рабочем состоянии, и включен в основную линию.

Использование основной линии планов запроса оптимизатором CBO

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

Первый раз оптимизатор построил новый план, с учетом индекса, но в SMB его не обнаружилось. Тогда оптимизатор занес план в историю и выполнил запрос по единственному в основной линии плану – старому. Со второго раза рабочая область в shared pool оказалась заведена, но запрос по-прежнему был отработан по единственному в основной линии старому плану. Если же управление планами отключить, СУБД отработает по более выгодному в этой версии оптимизатора новому плану:

Пополнение основной линии планов путем оценки планов

По результату выполненых действий в основной линии планов для нашего запроса оказалось два плана: с учетом индекса (признак ACCEPTED = ‘NO’) и без учета (признак ACCEPTED = ‘YES’):

Можно выдать оптимизатору задание проверить с планы признаками ACCEPTED = ‘NO’ (то есть учтеные в SMB, но не причисленые к приемлемым) на эффективность и включить их в основную линию (пометить как «приемлемые»), если они окажутся не хуже ранее там имевшихся:

Первую проверку изменений в SMB выполним по признаку ACCEPTED в справочной таблице:

Вторую проверку выполним по содержимому переменной REPORT, составленному функцией EVOLVE_SQL_PLAN_BASELINE:

Вердикт о влючении плана в основную линию производится на основании сравнения взвешенных суммарных оценок поперечисленным в отчете показателям («Compound improvement ratio»; точное правило не оглашается). В нашем случае он оказался благоприятным для плана-кандидата (коэффициент улучшения эффективности >= 1.5).

Теперь можно проверить прежним манером, как появление второго плана в основной линии отразилось на отработке запроса, выдав последовательность:

На этот раз планы окажутся одинаковыми, «новыми» (INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID).

Прочие способы управления основной линией планов

Пополнение и ручная чистка основной линии планов

Основную линию планов какого-нибудь запроса можно пополнять («развивать», evolve): вручную либо автоматически.

Ручное пополнение основной линии в результате запуска задания на проверку приемлемости плана выполняется функцией EVOLVE_SQL_PLAN_BASELINE и демонстрировалось выше.

Процедура LOAD_PLANS_FROM_SQLSET позволяет загружать основную линию планы из настроечного набора (SQL tuning set). Настроечный набор может быть получен любым доступным путем, например перенесен из другой БД, возможно даже из версии 10.

Процедуры PACK_STGTAB_BASELINE и UNPACK_STGTAB_BASELINE разрешают сохранить планы основных линий в специально созданной таблице и загружать их из такой таблицы.

Включение в сеансе параметра СУБД OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES в состояние TRUE вызовет автоматическое пополнение SMB планами запросов, поступающих из приложения. Для запросов приложения, основные линии планов которых желательно исключить из процедуры автоматического пополнения (то есть «зафиксировать»), можно использовать значение атрибута FIXED = ‘YES’ планов, составляющих соответствующую линию. Наличие планов с атрибутом FIXED = ‘YES’ препятствует только автоматическому пополнению и не сказывается на возможности добавлять планы вручную, по SQL ID и по настроечному набору.

Автоматическое пополнение основных линий также может осуществляться в результате «одобрения» (принятия) администратором профиля, рекомендованного для запроса советником SQL Tuning Advisor. По умолчанию этот советник запускается автоматическим заданием в «окошко поддержки» СУБД ежесуточно.

Путь попадания плана в основную линию обозначен в таблице DBA_SQL_PLAN_BASELINES в поле ORIGIN:

Ручное удаление плана из основной линии выполняется функцией DROP_SQL_PLAN_BASELINE.

Изменение свойств планов в SMB

Процедура ALTER_SQL_PLAN_BASELINE позволяет устанавливать атрибутам ENABLED, FIXED и AUTOPURGE плана требуемые значения явочным порядком. Пример:

Теперь система управления планами снова откажется применять «новый» план к запросу, несмотря на его присутствие в основной линии планов – благодаря нерабочему состоянию.

Регулирование накопления и хранения планов в основных линиях

Узнать текущие характеристики регламента накопления и хранения SMB можно запросом:

Изменить эти характеристики можно процедурой CONFIGURE, например:

Уроки системы управления планами в Oracle

Система управления планами в Oracle способна не только сохранить производительность при смене обстоятельств запуска запросов, но и преподать пользователю уроки.

Рассмотрим план для запроса SELECT DISTINCT … (далее все аналогично для запросов с UNION и GROUP BY). Как известно, в версиях до 9 включительно этот запрос обрабатывался с применением внутренней сортировки SORT UNIQUE. С версии 10 оптимизатор предлагает для такого запроса план с HASH UNIQUE, внутренней процедурой расстановки строк, с «хешированием». Большинство пользователей, обративших на это внимание, посчитали его целесообразным новшеством, улучшающим производительность отработки. Однако попытка применить для таких запросов управление планами (хотя бы ради сохранения производительности) заставляет в этом усомниться.

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

Включим план в основную линию, как выше. Это будет план с SORT UNIQUE. Поменяем обстоятельства выдачи запроса, например:

Получится план с HASH UNIQUE. Однако попытка дополнить им основную линию планов запроса функцией EVOLVE_SQL_PLAN_BASELINE обречена. SPM не считает новый план, который дают версии 10+ для этого запроса, лучше прежнего!

Более пристальное изучение обнаруживает, что план с HASH UNIQUE имеет большую стоимость обработки (cost), нежели «старый» с SORT UNIQUE (10 единиц против 5), хотя с ростом размера таблицы этот проигрыш и сокращается.

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

Источник

DB blogs and notes

Об особенностях работы с различными СУБД на commodity hardware

Ещё один взгляд на Oracle SQL Plan Management

Профили — новое (относительно) слово в БД. Профили нагрузки на файловую систему, профили работы сессии, профили SQL-запросов, … Наконец, Baseline или Oracle SQL Plan Management — новое средство работы с планами выполнения запросов в процессе жизни приложения, позволяющее управлять выбором плана выполнения и его эволюциями.

Технические вопросы уже не раз обсуждались и информации на эту тему масса:

Ниже предпринимается ещё одна попытка рассказать о принципах работы SPM на одном простом примере.

Чем принципиально SPM отличается от Outline? SPM не хранит ни плана выполнения, ни каких-либо хинтов оптимизатору, которые нужно будет вставить в запрос. В действительности это и не нужно: среда выполнения может измениться, и хинты либо перестанут работать, либо будут работать не так, как ожидалось.

SPM хранит код плана выполнения, который мы хотели бы получить. Т.е. по сути является целевой функцией. Т.е. теперь помимо first_rows, all_rows есть возможность указать код плана выполнения, который мы хотели бы получить.
Работает это так. Предположим, у нас есть запрос (1):

И мы хотели бы получить nested loop в плане выполнения этого запроса (это просто пример).
Берём исходный запрос и прописываем туда нужные хинты (2):

Получаем план выполнения

Для одного и того же самого запроса мы можем легко найти в документации советы по созданию baseline. Но что, если мы хотим получить такой-же план выполнения для другого запроса (1)? А влиять на код приложения и вставить хинт напрямую в текст запроса нельзя (нет времени и т.д.).

С помощью SPM это делается достаточно легко — для текста запроса (1) мы указываем в качестве цели план выполнения запроса (2):

Иногда этого достаточно, но можете убедиться сами: для запроса (1) такой SPM не работает.

Разобраться, в чём дело, помогает трассировка 10053. Но прежде, чем лезть в файлы отладки, давайте посмотрим на предполагаемый план выполнения запроса:

Видим, что план отличается от того, на который мы рассчитывали — вместо операции Nested loop в нём Filter. Это не ошибка. Смотрим секцию SPM в трассировке 10053:

Что произошло? Оптимизатор запросов на этапе выбора плана выполнения определил, что есть SPM для данного sql_id. На основе подсказок из профиля SPM после этапа перечисления попытался получить план с заданным номером и не смог этого сделать. То же самое мы видели, когда пытались вывести план выполнения SPM с помощью пакета DBMS_XPLAN. В этот момент уже невозможно сказать, каким должен был быть план выполнения (известен только его идентификатор). Достоверно известно, что для заданного sql_id в текущем окружении его получить невозможно. Так что стандартно выбирается план выполнения с минимальной стоимостью.

Причины подобного поведения с запросом (1) — эвристики оптимизатора. Часть операций в определённых условиях никогда не рассматриваются. С помощью хинтов нам удалось в запросе (2) заставить оптимизатор выбрать одну из таких операций, но при попытке сказать оптимизатору для запроса (1): «Используй план с номером n», мы потерпели неудачу — план с номером n в нормальной ситуации вообще не рассматривается.

Другие причины, почему может не использоваться SPM:

От статистики и данных в таблицах SPM не зависит (хорошие новости) 🙂

Постскриптум

Вопрос 1. Что же за хинты в таком случае хранятся в виде XML в профиле? Хинты оптимизатору, с помощью которых он пытается получить целевой план выполнения. Эти хинты не влияют на запрос и нужны лишь для ускорения поиска. Действительно, не выполнять же дорогостоящую фазу оптимизации каждый раз заново.

Вопрос 2. А что же в таком случае показывает display_sql_plan_baseline? План выполнения, который удалось получить с помощью этих хинтов. Бывает, как в примере выше, что эти хинты действуют совсем не так, как задумывалось. Хорошо, что в этом случае SPM не оказывает влияния на план выполнения.

Источник

Четыре способа корректировки планов запросов по образцу без изменения кода.

Илья Деев,
«Innova Systems»,
член RuOUG

Источник: Russian Oracle User Group with the library and forum options,
http://www.ruoug.org/events/20121206/index.html
[От редакции FORS Magazine: дополнением к данной статье является одноименная презентация И.Деева, представленная им на семинаре Российской группы пользователей Oracle 06.12.2012.]

[От редакции FORS Magazine: в процессе обсуждения этой статьи уже после её опубликования обнаружилась небольшая неточность: в скрипте 1.1 выдача привилегий была корректна только для Oracle Database 11g и не применима в версии 10g. В настоящем тексте эта неточность исправлена. К сожалению, исправить также оперативно эту неточность в pgf-форматах этого выпуска журнала не представляется возможным, поэтому в следующем 6-м номере журнала будет опубликовано соответствующее письмо в редакцию нашего автора Ильи Деева.]

Введение

В процессе эксплуатации баз данных иногда возникает необходимость скорректировать планы запросов, не прибегая к изменению исходного кода. Такие средства влияния на план запроса как корректировка статистики, изменение параметров сессии, построение SQL профиля не всегда доступны, не всегда гарантированно ведут к нужному результату, а также могут иметь нежелательные побочные эффекты. Корректировка статистики объектов базы данных и установка параметров в сессии могут повлиять не только на проблемные запросы, но и на другие. Построение SQL профилей возможно только в Oracle Enterprise Edition при использовании платных Diagnostic &Tuning Pack. Однако, даже обладая этими инструментами, к сожалению, не всегда удается получить удовлетворительный результат. В данной заметке описываются способы решения проблемы с помощью метода настройки плана запроса по образцу.

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

Небольшое отступление по поводу хинтов. С одной стороны, набор хинтов – это результат работы оптимизатора. Начиная с Oracle 10.2 список хинтов запроса доступен через вывод плана запроса с помощью пакета DBMS_XPLAN с заданием формата вывода ‘outline’:

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

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

Тестовые данные

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

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

Способ №1. Использование функционала SQL Plan Management. Создание SQL plan baseline.

Способ, который задействует функционал SQL Plan Management, может быть применен в Oracle 11 Enterprise Edition. План настроенного запроса-образца с помощью процедуры dbms_spm.load_plan_from_cursor_cache загружается в качестве плана для настраиваемого запроса, текст которого явно указывается в соответствующем параметре процедуры. При этом создается SQL plan baseline.

В примере, представленном ниже, подразумевается, что параметр optimizer_capture_sql_plan_baselines имеет значение false (в этом случае SQL baseline для каждого запроса автоматически не создается). Необходимы следующие привилегии:

Скрипт 1.1 Привилегии для выполнения скрипта с использованием SQL Baselines:

Скрипт 1.2. Пример настройки с использованием SQL plan baseline:

В разделе Note отчета dbms_xplan.display указывается используемый SQL plan baseline.

Название SQL plan baseline для запроса отражается также в поле V$SQL.SQL_PLAN_BASELINE.

В Oracle 11 SE скрипты выполнятся, однако никакого эффекта на план запроса не окажут.

Cледует иметь в виду, что для такого типа запросов, как multitable insert, невозможно создать SQL plan baseline, см., например: http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/#comment-49382

Способ №2. Создание SQL патчей.

Еще один способ изменения планов запросов без правки исходного кода в Oracle 11g – использование функционала SQL Repair Advisor, а именно – создание SQL патчей, которые предназначены для изменения планов запросов в случае непредвиденных проблем. Принцип использования в нашем случае такой же как и ранее – получение набора хинтов настроенного запроса и создание на их основе SQL патча для проблемного запроса. Для создания SQL патча необходимы права на вызов пакета sys.dbms_sqldiag_internal.

Скрипт 2.1. Привилегии для использования SQL патчей

Скрипт 2.2 показывает, как можно автоматизировать создание SQL патча:

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

Следует заметить, что используемые в SQL патче хинты должны быть именно в системном виде. При попытке использовать в SQL патче хинт в виде /*+ full(drop_tbl) */ патч будет создан, но план запроса не изменится.

SQL Repair Advisor является бесплатной опцией Oracle EE. Однако, функционалом создания SQL патчей удается воспользоваться и в других редакциях Oracle 11g, включая XE.

Способ №3. Настройка с использованием SQL profiles.

Скрипт 3.1. Привилегии для использования профилей.

Скрипт 3.2. Пример использования настройки по образцу с помощью SQL profiles

В разделе Note отчета указано имя используемого SQL profile. В примере используется категория профилей DEFAULT. При необходимости, можно изменить ее наименование. Категория профилей по умолчанию задается параметром sqltune_category:

Название SQL профиля для запроса отражается также в поле V$SQL.SQL_PROFILE.

Иногда бывает также необходимо отменить действие хинтов, входящих в текст запроса.

Скрипт 3.3 Отмена действия хинтов

Способ №4. Редактирование private outline и создание на его основе public outline.

Наконец, четвертый способ настройки основан на функционале Stored Outlines и заключается в подмене хинтов private outline проблемного запроса хинтами настроенного запроса и последующего создания public outline. Особенно актуален такой вариант для версии Standard Edition, где Plan Stability является доступной опцией. Этот способ настройки работает во всех редакциях версий 9,10,11.

Итак, допустим, нужно оптимизировать выполнение некоего запроса с помощью outline. План действий следующий:

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

Скрипт 4.1. Предоставление привилегий для работы с outlines.

Скрипт 4.2 пошагово реализует изложенный выше план.

Для использования категории PROD на уровне всего инстанса необходимо выполнить:

Таблицы, к которым ведут синонимы OL$, OL$HINTS, OL$NODES в Oracle 10 и Oracle 11 являются временными таблицами, и поскольку их содержимое очищается, необходимо выполнить весь скрипт в одной сессии.

Для удаления настроенного outline нужно выполнить команду:

Скрипт 4.3 объединяет шаги предыдущего скрипта в один PL/SQL скрипт:

При проверке работы настроенного запроса нужно не забывать устанавливать категорию для outline, если она не установлена на уровне инстанса.

Большим достоинством этого метода является то, что он подходит для разных версий и редакций Oracle – 9, 10, 11, EE, SE, XE. Недостатком этого способа, помимо большей сложности скрипта, является то, что в 11g функционал Stored Outlines официально объявлен устаревшим. В документации outlines настоятельно рекомендуется заменить на SQL plan baselines.

Выводы

Как было показано, в разных редакциях разных версий Oracle всегда найдется способ исправить план неправильно выполняющегося запроса без изменения кода, для этого имеются достаточно широкие возможности. В перспективе хотелось бы видеть, чтобы функционал SQL Plan Baselines работал не только в Enterprise Edition. Объявление функционала Stored Outlines устаревшим логически требует его замены на функционал SQL Plan Management во всех редакциях Oracle. Это было бы самым удобным вариантом для пользователей.

Таблица 1. Рассмотренные способы настройки и версии Oraсle.

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

Источник