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

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

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

Введение

Механизм SQL Plan Management

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

Автоматическое получение планов – “на лету”

Массовая загрузка

Выбор планов

Изменение планов

Использование и управление SQL Management Base

Параметры инициализации

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

Мониторинг SQL Plan Management

Утилита Enterprise Manager

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

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

Планы массовой загрузки

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

Проекция опорного SQL-плана в план выполнения

Преобразование непринятого плана

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

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

Заключение

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

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

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

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

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

Создайте опорные планы выполнения SQL, представляющие принятые (проверенные) планы выполнения для всех релевантных SQL-операторов. Опорные планов выполнения SQL хранятся в архивах планов (plan history) в базе SQL Management Base в табличном пространстве SYSAUX.

3. Эволюция опорного SQL-плана:

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

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

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

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

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

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

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

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

Сразу после создания опорных планов выполнения SQL они начнут использоваться, гарантируя, что при переходе от 10gR2 к 11gR1 не произойдет никаких изменений плана. Если оптимизатор в базе данных Oracle 11g придумает другой план выполнения, то этот план будет добавлен к архиву планов и будет отмечен, как подлежащий проверке. Он будет отмечен как принятый только в том случае, если его производительность будет столь же хороша, как у текущего опорного 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 11g любой вендор программного обеспечения от третьих фирм может начать поставку своего прикладного программного обеспечения наряду с соответствующими опорными планами выполнения SQL для вновь вводимых SQL-операторов. Это служит гарантией, что для всех SQL-операторов, являющихся частью опорного плана выполнения SQL, сначала используются планы, про которые известно, что они давали хорошую производительность в стандартной тестовой конфигурации. Альтернативно, если приложение было разработано собственными силами или проходило тестирование внутри фирмы, правильные планы могут быть экспортированы из тестовой системы и импортированы в промышленную версию с помощью следующих шагов:

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

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

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

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

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

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

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

Использование и управление базой SQL MANAGEMENT BASE

Параметры инициализации

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

Журнал операторов, архивы планов и опорные планы 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 не применяется, если это табличное пространство недоступно.

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

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

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

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

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

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

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

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

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

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

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

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

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

1. 
   Кликните по кнопке-выключателю перед планом и выберите расположенную над ней кнопку evolve
   
2. 
   Откроется страница эволюции опорного плана SQL с тремя вариантами кнопки-выключателя:
   

3. 
   Кликните по OK
   
4. 
   Появится основная страница опорного плана SQL. Вы должны увидеть, что эволюционирует задание, перечисленное в разделе заданий в правой верхней стороне страницы. (В случае необходимости кликните по кнопке обновления (refresh)).
   
   
   

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

Этот оператор 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, используя следующий запрос:

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