Управление SQL-планами в Oracle Database 11g

Источник: oracle

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

Oracle Database 11 g является первой базой данных на рынке, которая способна разрешить эту проблему. Механизм SQL Plan Management (SPM - управление планами выполнения SQL-операторов) предлагает инфраструктуру полностью прозрачного управляемого преобразования планов выполнения. Используя SPM, оптимизатор автоматически управляет планами выполнения и гарантирует, что используются только известные или принятые планы. Когда для SQL-оператора находится новый план выполнения, он не используется до тех пор, пока не будет проверен базой данных, и не будет показано, что он имеет сопоставимую или лучшую производительность, чем текущий план.

Механизм SQL PLAN MANAGEMENT

Гарантируемая стабильность и контролируемая эволюция плана

Механизм управления планами выполнения SQL-операторов (SPM) гарантирует, что изменение плана оператора никогда не приведет к ухудшению его производительности во время выполнения. Чтобы гарантировать это, используются только принятые планы выполнения; любая эволюция плана впоследствии будет отслежена и оценена и будет принята, как проверенная, только в том случае, если новый план приводит во время выполнения к каким-либо изменениям или усовершенствованиям. SQL Plan Management состоит из трех основных компонентов:

Получение опорного SQL-плана:
Создайте опорные планы выполнения SQL, представляющие принятые (проверенные) планы выполнения для всех релевантных SQL-операторов. Опорные планов выполнения SQL хранятся в архивах планов (plan history) в базе SQL Management Base в табличном пространстве SYSAUX.
Выбор опорного плана выполнения SQL:
Убедитесь, что только принятые планы выполнения используются для операторов с опорными SQL-планами и отслеживайте все новые планы выполнения в архиве планов (plan history) для операторов. Архив планов выполнения состоит из принятых и непринятых планов. Непринятый план может быть неверифицированным (недавно найденный, но пока непроверенный) или отклоненным (верифицированный, но сочтенный непроизводительным).
Эволюция опорного SQL-плана:
Оцените все непроверенные планы выполнения для данного оператора, содержащиеся в архиве, чтобы они получили статус принятых или отклоненных планов.

Рис. 0. База SQL Management, состоящая из журнала операторов и архивов планов для повторяющихся SQL-операторов (repeatable SQL Statements).

Получение опорного SQL-плана

Получите планы 'на лету' или выполните массовую загрузку SPM планами из кэша курсора, набора настройки SQL или импортируйте планы из другой системы.

Для того чтобы смог заработать SPM, сначала нужно заполнить SQL Management Base текущими стоимостными планами выполнения, которые станут опорными планами выполнения для каждого SQL-оператора. Имеется два различных способа заполнения SQL Management Base:

Автоматический захват данных планов выполнения
Массовая загрузка планов выполнения

Automatic plan capture can be switched on by setting the init.ora parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE (the default value is false) . With automatic plan capture enabled, the SPM repository will be automatically populated for any repeatable SQL statement. To identify repeatable SQL statements, the optimizer will log the identity (SQL Signature) of each SQL statement into a statement log the first time it is compiled. If the SQL statement is processed again (executed or compiled) the presence of its identity in the statement

Автоматическое получение планов выполнения - "на лету"

Включение автоматического получения планов производится установкой параметра OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES на TRUE (значение по умолчанию -FALSE) в файле init.ora. При этом репозиторий SPM будет заполняться автоматически для любого повторяющегося SQL-оператора. Для определения повторяющегося SQL-оператора оптимизатор при первой компиляции регистрирует идентификационный параметр (сигнатуру SQL) каждого SQL-оператора в журнале операторов. Если SQL-оператор обрабатывается снова (выполняется или компилируется), то наличие его идентификационного параметра в журнале операторов укажет на то, что подобный оператор является повторяющимся. Для оператора будет создан архив SQL-планов выполнения, в который вкладывается информация, используемая оптимизатором для воспроизведения планов выполнения, как-то: текст оператора SQL, иерархическая структура, переменные связывания и среда компиляции.

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

Рис. 1. База SQL Management, состоящая из журнала операторов и архивов планов выполнения для повторяющихся операторов.

Операция массовой загрузки

Планы выполнения в операциях массовой загрузки особенно полезны, когда проводится обновление базы данных с предыдущей версии до Oracle Database 11 g , или при развертывании нового приложения. Операция массовой загрузки может быть выполнена вместе с автоматическим получением плана или вместо него. Планы выполнения, которые были загружены с помощью операции массовой загрузки, автоматически принимаются для создания новых опорных планов выполнения SQL или для добавления к существующим планам. Для массовой загрузки в SQL Management Base могут быть использованы три различные методики:

Заполнение планов выполнения для данного набора SQL-настроек (STS - SQL Tuning Set)
Использование планов выполнения, имеющихся в настоящее время в кэше курсора
Распаковка существующих опорных планов выполнения SQL из промежуточной таблицы

Из набора настроек SQL (STS)

Можно получить планы (критической) рабочей нагрузки SQL для набора настроек SQL (STS), а затем загрузить их в SQL Management Base, как опорные SQL-планы, используя для этого процедуру PL/SQL DBMS_SPM.LOAD_PLANS_FROM_SQLSET, или через Oracle Enterprise Manager (EM). При следующем выполнении этих операторов будут использованы опорные планы выполнения SQL.

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

Создайте в Oracle Database 10gR2 STS, включающий план выполнения для каждого из SQL-операторов.
Загрузите STS в промежуточную таблицу и экспортируйте его в плоский файл.
Импортируйте промежуточную таблицу из плоского файла в Oracle Database 11 g и выгрузите STS.
Используйте EM или DBMS_SPM.LOAD_PLANS_FROM_SQLSET, чтобы загрузить планы выполнения в SQL Management Base.

Рис. 2. Массовая загрузка SMB для обновления (апгрейда) базы данных с использованием STS.

Сразу после создания опорных планов выполнения SQL они начнут использоваться, гарантируя, что при переходе от 10gR2 к 11 g R1 не произойдет никаких изменений плана. Если оптимизатор в базе данных Oracle 11 g придумает другой план выполнения, то этот план будет добавлен к архиву планов и будет отмечен, как подлежащий проверке. Он будет отмечен как принятый только в том случае, если его производительность будет столь же хороша, как у текущего опорного SQL-плана (для 10gR2), или лучше.

Из кэша курсора

Имеется возможность загрузить планы операторов в SQL Management Base непосредственно из кэша курсора. Применяя фильтр по имени модуля, схеме или SQL_ID, можно идентифицировать SQL-оператор или набор из SQL-операторов, данные о которых нужно собрать. Для загрузки планов может быть использована процедура PL/SQL DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. Или жеэто можно сделать через Oracle Enterprise Manager. При следующем выполнении этих операторов будут использоваться их опорные SQL-планы.

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

Распаковка опорных планов из промежуточной таблицы

Развертывание нового модуля приложения означает введение в базу данных совершенно новых SQL-операторов. С Oracle Database 11 g любой вендор программного обеспечения от третьих фирм может начать поставку своего прикладного программного обеспечения наряду с соответствующими опорными планами выполнения SQL для вновь вводимых SQL-операторов. Это служит гарантией, что для всех SQL-операторов, являющихся частью опорного плана выполнения SQL, сначала используются планы, про которые известно, что они давали хорошую производительность в стандартной тестовой конфигурации. Альтернативно, если приложение было разработано собственными силами или проходило тестирование внутри фирмы, правильные планы могут быть экспортированы из тестовой системы и импортированы в промышленную версию с помощью следующих шагов:

В первоначальной системе создайте промежуточную таблицу, используя процедуру DBMS_SPM.CREATE_STGTAB_BASELINE
Упакуйте опорные планы выполнения SQL, которые вы хотите экспортировать из базы управления SQL в промежуточную таблицу, используя для этого функцию DBMS_SPM.PACK_STGTAB_BASELINE.
Экспортируйте промежуточную таблицу в плоский файл, используя команду экспорта или технологию Data Pump.
Передайте полученный плоский файл в целевую систему.
Импортируйте промежуточную таблицу из плоского файла, используя команду импорта или технологию Data Pump.
Распакуйте опорные планы выполнения SQL из промежуточной таблицы в базу данных управления SQL в целевой системе, используя функцию DBMS_SPM.UNPACK_STGTAB_BASELINE.

Рис. 3. Импорт опорных планов выполнения SQL из тестовой системы при реализации нового приложения

Выбор опорного плана выполнения SQL

При работе с SPM для выполнения будут выбраны только известные или проверенные планы

При каждой компиляции SQL-оператора для построения плана выполнения с наилучшей стоимостью оптимизатор сначала использует традиционный метод поиска по стоимости. Если параметр инициализации OPTIMIZER_USE_PLAN_BASELINES, установлен на TRUE(значение по умолчанию), то перед тем, как будет выполнен стоимостной план, оптимизатор попробует найти соответствующий план в опорном плане выполнения SQL-оператора. Эти действия выполняются как операция в оперативной памяти, так что в работу любого приложения не вносится никаких измеримых накладных расходов. Если соответствие найдено, оптимизатор продолжит работу с этим планом. В противном случае, если не будет найдено никакого соответствия, то недавно сгенерированный план будет добавлен к архиву выполнения планов; он должен быть подвергнут проверке, прежде чем его можно будет принять, как опорный план выполнения. Вместо выполнения недавно сгенерированного плана оптимизатор будет оценивать каждый из принятых планов для SQL-оператора и выбирать из них план с самой низкой стоимостью выполнения (заметьте, что в опорном плане может насчитываться более одного проверенного/принятого плана для данного оператора). Однако, если какое-либо изменение в системе (например, удаление индекса) приведет к ситуации, когда все принятые планы станут невоспроизводимыми, то оптимизатор будет использовать недавно сгенерированный стоимостной план.

Рисунок 4. Как с помощью SPM выбирается план выполнения

Также можно повлиять на выбор плана оптимизатором, когда он его выбирает. Планы могут быть отмечены, как фиксированные (fixed SQL plan baselines). Фиксированные опорные планы выполнения SQL указывают оптимизатору, что предпочтительные. Если оптимизатор будет оценивать опорные планы и один из планов окажется фиксированным, то оптимизатор будет оценивать только этот фиксированный план и остановится на нем, если только он является воспроизводимым. Если фиксированный план (ы) будет невоспроизводимым, то оптимизатор возвратится к оценке оставшихся опорных планов выполнения SQL и выберет план с наименьшей стоимостью. Отметьте, что оценка стоимости плана гораздо ниже, чем стоимость полного разбора. Оптимизатор не исследует все возможные методы доступа, а только один определенный путь доступа.

Эволюция опорного плана выполнения SQL

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

Когда оптимизатор находит для SQL-оператора новый план выполнения, этот план добавляется к архиву, как еще непринятый план, который должен быть проверен, прежде чем он сможет стать принятым планом. Можно развить план выполнения SQL-оператора, используя Oracle Enterprise Manager или выполняя в командной строке функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. При использовании любого из этих методов имеются три выбора:

Принять план только в том случае, если он выполняется лучше, чем существующий опорный план выполнения SQL
Принять план, не делая проверки производительности
Запустить сравнение производительности и сгенерировать отчет, не развивая нового плана.

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

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

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

Использование и управление базой SQL MANAGEMENT BASE
Параметры инициализации

Для управления SPM в файле init.ora имеются два параметра:

optimizer_capture_sql_plan_baselines - управляет автоматическим получением новых опорных планов выполнения для повторяющихся SQL-операторов. В 11 g R1 этот параметр по умолчанию установлен на FALSE.
optimizer_use_sql_plan_baselines - управляет использованием опорных SQL-планов. Когда он активирован, оптимизатор ищет в опорных SQL-планах планы для SQL-оператора, подлежащего компиляции. Если какие-либо планы найдены, то оптимизатор оценивает каждый план и выберет тот, у которого будет самая низкая стоимость. В 11 g R1 по умолчанию этот параметр установлен на TRUE.

Управление потреблением дисковой памяти в SQL Management Base

Журнал операторов, архивы планов и опорные планы SQL хранятся в SQL Management Base. База SQL Management Base - это часть словаря базы данных, хранящаяся в табличном пространстве SYSAUX. По умолчанию максимальный размер отводимого для SQL Management Base дискового пространства не должен превышать 10% размера табличного пространства SYSAUX. Однако, используя для этого процедуру PL/SQL DBMS_SPM.CONFIGURE, можно заменить это предельное значение любым значением в диапазоне от 1% до 50%. Еженедельно выполняемый фоновый процесс измеряет общий объем пространства, занятого SQL Management Base, и когда ранее определенный предел превышается, этот процесс генерирует предупреждение в журнале предупреждений (alert log).

Кроме того, имеется запланированная к еженедельному выполнению задача чистки, управляющая дисковым пространством, используемым SPM в SQL Management Base. Задача автоматически выполняется во время технологического окна и удаляет любые планы, которые не использовались в течение более чем 53 недель. Тем самым достигается гарантия, что любые SQL-операторы, выполняемые хотя бы один раз в год, останутся доступными. Изменить период сохранения неиспользуемых планов можно, используя DBMS_SPM.CONFIGURE либо Enterprise Manager. Новое значение может находиться в диапазоне от 5 до 523 недель (немногим более 10 лет).

Поскольку SQL Management Base полностью хранится внутри табличного пространства SYSAUX, SPM не применяется, если это табличное пространство недоступно.

Рис. 5. Изменение установки продолжительности использования плана в EM

Мониторинг SQL PLAN MANAGEMENT

Для ведения мониторинга SPM используйте EMDBControl или новое словарное представление DBA_SQL_PLAN_BASELINES.

Для ведения мониторинга функциональности SPM в Oracle Database 11 g было введено несколько новых экранов управления предприятием и представлений АБД.

Enterprise Manager

Все аспекты управления и мониторинга опорных SQL-планов могут быть выполнены через Enterprise Manager Database Control.

Начало работы

Чтобы попасть на страницу опорного плана выполнения SQL:

Обратитесь к домашней странице базы данных в Enterprise Manager.
В верхней части страницы кликните по кнопке Server, чтобы отобразить страницу сервера.
В разделе оптимизатора запросов кликните по кнопке SQL Plan Control.
Появится страница SQL Plan Control. См. интерактивную справку для получения информации об этой странице.
В верхней части страницы кликните по кнопке SQL Plan Baseline, чтобы отобразить подстраницу опорных планов SQL.

Рис. 6. Домашняя страница опорного плана в Oracle Enterprise Manager DB Control

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

Изменение значений параметров init.ora

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

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

Рис. 7. Установка в EM связанных с SPM параметров файла init.ora

Массовая загрузка планов

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

Кликните по кнопке загрузки
Появится страница загрузки опорного плана SQL. Выберите переключатель загрузки планов для "load from the cursor cache" ("загрузка из кэша курсора")
Вручную введите один или несколько SQL_ID или кликните по иконке с фонариком, чтобы увидеть список всех SQL_ID и текст SQL для каждого плана в кэше курсора.
После выбора SQL_ID заполните информацию, связанную с планированием задания (по умолчанию - немедленная загрузка)
Кликните по OK

Рисунок 8. Массовая загрузка опорных планов SQL из курсора кэша в EM

Изменение атрибутов

С основной страницы опорного плана SQL можно изменить любой атрибут опорного плана. Для изменения атрибута

Кликните по кнопке-выключателю перед опорным планом
Кликните по кнопке атрибута, который вы желаете изменить
Появится окно диалога, в котором вам будет предложено подтвердить ваш выбор. Кликните по OK

Просмотр плана выполнения опорного плана SQL

Чтобы просмотреть фактический план выполнения для опорного плана SQL, кликните по имени плана. Для рассмотрения планов выполнения из опорных планов для данного оператора SQL кликните по кнопке SQL Text.

Эволюция опорного плана SQL

Находясь на основной странице опорного плана SQL, можно увидеть, какие планы приняты, а какие - нет. Если вы захотите проследить эволюцию непринятого плана:

Кликните по кнопке-выключателю перед планом и выберите расположенную над ней кнопку evolve
Откроется страница эволюции опорного плана SQL с тремя вариантами кнопки-выключателя:
1. Verify Performance (проверить производительность) - если надо иметь гарантии, что непринятый план выполняется столь же хорошо, как существующий опорный план выполнения SQL, или даже лучше его, выберите YES. Если вы уже знаете, что непринятый план имеет хорошую производительность, и хотели бы обойти проверку, выберите NO.
2. Time Limit(ограничение времени) - применяется только в том случае, если в поле Verify Performance выбрано Yes. Режим Auto (Авто) означает, что Oracle сама решит, как много времени расходовать на подтверждение производительности непринятых планов. Режим Unlimited (Неограниченное) означает, что процесс проверки плана будет выполняться вплоть до его завершения. Specify (Определить) означает, что нужно установить временной лимит для процесса проверки плана.
3. Action (Действие) - хотите ли вы, чтобы новый план был автоматически принят, или вы просто хотите получить на выходе отчет о результатах проверки процесса, базируясь на котором вы сможете решить, принимать новый план, или нет.
Кликните по OK
Появится основная страница опорного плана SQL. Вы должны увидеть, что эволюционирует задание, перечисленное в разделе заданий в правой верхней стороне страницы. (В случае необходимости кликните по кнопке обновления (refresh)).

Рис. 9. Эволюция плана

Мониторинг SPM через представления АБД

В представлении DBA_SQL_PLAN_BASELINES представлена информация об опорных планах SQL, которые в данный момент созданы для конкретных SQL-операторов.

select sql_handle, sql_text, plan_name, origin, 
enabled, accepted, fixed, autopurge 
from dba_sqljplan_baselines;

Этот оператор select возвращает следующие строки:

В этом примере у одного и того же SQL-оператора имеется два плана, причем оба они были получены автоматически. Один из планов (SYS_SQL_PLAN_4be) является опорным планом для плана выполнения, поскольку он является и допустимым, и принятым. Другой план (SYS_SQL_PLAN_lea) - это непринятый план, который был поставлен в очередь для изменения или проверки. Он был автоматически получен и поставлен в очередь для проверки; значение параметра accepted для него установлено на NO. Ни один из планов не является фиксированным, и оба они подлежат автоматической чистке.

Чтобы проверить детализированный план выполнения для любого опорного плана, можно использовать процедуру DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.

Кроме того, если изучить представление V$SQL, появляется возможность проверить, использует ли SQL-оператор опорный план. Если SQ-оператор использует опорный план SQL plan, то plan_name для плана, выбранного из опорного плана SQL, будет содержаться в столбце sql_plan_baseline представления V$SQL. Можно соединить представление V$SQL и представление DBA_SQL_PLAN_BASELINES, используя следующий запрос:

Select s.sql_text, b.plan_name, b.origin, b.accepted 
From dba_sql_plan_baselines b, v$sql s 
Where s. exact_matching_signature = b.signature 
And   s.SQL_PLAN_BASELINE = b.plan_name;

Интеграция с автоматической настройкой SQL

Во время технологических окон Oracle Database 11 g автоматически выполняет SQL Tuning Advisor - часть пакета настройки и диагностики (Tuning and Diagnostic pack). Эта задача автоматической настройки SQL нацелена на SQL-операторы, создающие высокую нагрузку на систему. Такие операторы определяются по данным о производительности выполнения, которые собираются в снэпшотах автоматически управляемого репозитория рабочей нагрузки (AWR). Если SQL Tuning Advisor найдет лучший план выполнения для SQL-оператора, он порекомендует SQL-профиль. Для некоторых из этих SQL-операторов с высокой нагрузкой опорные SQL-планы могли быть уже созданы. Если рекомендация для SQL-профиля, сделанная задачей автоматической настройки SQL, будет реализована, то план выполнения, найденный SQL Tuning Advisor, будет добавлен как принятый опорный план SQL.

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

ЗАКЛЮЧЕНИЕ

Новая опция Oracle Database 11 g , которая называется SQL Plan Management (SPM), обеспечивает управляемую эволюцию планов выполнения. В случае использования SPM оптимизатор автоматически управляет планами выполнения и гарантирует, что будут использоваться только известные или проверенные планы. Когда для SQL-оператора находится новый план, он не будет использован, пока для него не будет подтверждено, что его производительность сопоставима с производительностью текущего плана или лучше ее.