Управление 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-плана Получите планы 'на лету' или выполните массовую загрузку 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-оператора будет найден новый план, то он будет добавлен к архиву планов выполнения и отмечен, как подлежащий проверке. Он отмечается как принятый только в том случае, если его производительность будет лучше, чем производительность плана, выбранного из текущего опорного.
Операция массовой загрузки Планы выполнения в операциях массовой загрузки особенно полезны, когда проводится обновление базы данных с предыдущей версии до Oracle Database 11 g , или при развертывании нового приложения. Операция массовой загрузки может быть выполнена вместе с автоматическим получением плана или вместо него. Планы выполнения, которые были загружены с помощью операции массовой загрузки, автоматически принимаются для создания новых опорных планов выполнения SQL или для добавления к существующим планам. Для массовой загрузки в SQL Management Base могут быть использованы три различные методики:
Из набора настроек SQL (STS) Можно получить планы (критической) рабочей нагрузки SQL для набора настроек SQL (STS), а затем загрузить их в SQL Management Base, как опорные SQL-планы, используя для этого процедуру PL/SQL DBMS_SPM.LOAD_PLANS_FROM_SQLSET, или через Oracle Enterprise Manager (EM). При следующем выполнении этих операторов будут использованы опорные планы выполнения SQL. Массовая загрузка планов выполнения из 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, сначала используются планы, про которые известно, что они давали хорошую производительность в стандартной тестовой конфигурации. Альтернативно, если приложение было разработано собственными силами или проходило тестирование внутри фирмы, правильные планы могут быть экспортированы из тестовой системы и импортированы в промышленную версию с помощью следующих шагов:
Выбор опорного плана выполнения SQL При работе с SPM для выполнения будут выбраны только известные или проверенные планы При каждой компиляции SQL-оператора для построения плана выполнения с наилучшей стоимостью оптимизатор сначала использует традиционный метод поиска по стоимости. Если параметр инициализации OPTIMIZER_USE_PLAN_BASELINES, установлен на TRUE(значение по умолчанию), то перед тем, как будет выполнен стоимостной план, оптимизатор попробует найти соответствующий план в опорном плане выполнения SQL-оператора. Эти действия выполняются как операция в оперативной памяти, так что в работу любого приложения не вносится никаких измеримых накладных расходов. Если соответствие найдено, оптимизатор продолжит работу с этим планом. В противном случае, если не будет найдено никакого соответствия, то недавно сгенерированный план будет добавлен к архиву выполнения планов; он должен быть подвергнут проверке, прежде чем его можно будет принять, как опорный план выполнения. Вместо выполнения недавно сгенерированного плана оптимизатор будет оценивать каждый из принятых планов для SQL-оператора и выбирать из них план с самой низкой стоимостью выполнения (заметьте, что в опорном плане может насчитываться более одного проверенного/принятого плана для данного оператора). Однако, если какое-либо изменение в системе (например, удаление индекса) приведет к ситуации, когда все принятые планы станут невоспроизводимыми, то оптимизатор будет использовать недавно сгенерированный стоимостной план.
Также можно повлиять на выбор плана оптимизатором, когда он его выбирает. Планы могут быть отмечены, как фиксированные (fixed SQL plan baselines). Фиксированные опорные планы выполнения SQL указывают оптимизатору, что предпочтительные. Если оптимизатор будет оценивать опорные планы и один из планов окажется фиксированным, то оптимизатор будет оценивать только этот фиксированный план и остановится на нем, если только он является воспроизводимым. Если фиксированный план (ы) будет невоспроизводимым, то оптимизатор возвратится к оценке оставшихся опорных планов выполнения SQL и выберет план с наименьшей стоимостью. Отметьте, что оценка стоимости плана гораздо ниже, чем стоимость полного разбора. Оптимизатор не исследует все возможные методы доступа, а только один определенный путь доступа. Эволюция опорного плана выполнения SQL Планы можно эволюционировать вручную или подтверждать в любое время, или же можно запланировать задание базы данных для выполнения процесса эволюции Когда оптимизатор находит для SQL-оператора новый план выполнения, этот план добавляется к архиву, как еще непринятый план, который должен быть проверен, прежде чем он сможет стать принятым планом. Можно развить план выполнения SQL-оператора, используя Oracle Enterprise Manager или выполняя в командной строке функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. При использовании любого из этих методов имеются три выбора:
Первый вариант ведет к оценке нового плана, чтобы увидеть, работает ли он лучше, чем выбранный план. Если это так, то новый план будет добавлен к опорному плану, как принятый, а если нет, то новый план останется в архиве планов, как непринятый план, но его атрибут LAST_VERIFIED будет обновлен - в него будет занесена текущая отметка даты/времени. Функция возвращает форматированный текстовый отчет, в котором содержатся действия, выполненные функцией, и дается параллельный показ статистики производительности нового и первоначального планов. Если выбирается вариант 2, то новый план будет добавлен к опорному плану выполнения SQL как принятый план без проверки его производительности. Отчет также будет сгенерирован. По варианту 3 выполняется оценка нового плана, чтобы увидеть, выполняется ли он лучше, чем выбранный план, но даже если это так, новый план не будет принят автоматически. После оценки будет всего лишь сгенерирован отчет. Использование и управление базой SQL MANAGEMENT BASE Для управления SPM в файле init.ora имеются два параметра:
Управление потреблением дисковой памяти в 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 не применяется, если это табличное пространство недоступно.
Мониторинг SQL PLAN MANAGEMENT Для ведения мониторинга SPM используйте EMDBControl или новое словарное представление DBA_SQL_PLAN_BASELINES. Для ведения мониторинга функциональности SPM в Oracle Database 11 g было введено несколько новых экранов управления предприятием и представлений АБД. Enterprise Manager Все аспекты управления и мониторинга опорных SQL-планов могут быть выполнены через Enterprise Manager Database Control. Начало работы Чтобы попасть на страницу опорного плана выполнения SQL:
С основной страницы можно управлять параметрами init.ora, планировать задания по загрузке или эволюции, а также изменять некоторые атрибуты существующих опорных планов SQL. Изменение значений параметров init.ora В левой верхней стороне основной страницы опорного плана имеется раздел установок, в котором перечисляются параметры, контролирующие SQL Plan Management. Достаточно беглого взгляда на этот раздел, чтобы понять, включен или нет автоматическое получение опорных планов, а также понять, используется или нет опорный план SQL. Для изменения значения параметра init.ora:
Массовая загрузка планов Можно загрузить планы прямо из кэша курсора, используя кнопку загрузки с правой стороны над списком опорных планов SQL. Можно загрузить планы для всех операторов в кэше курсора, или же можно выбрать подмножество планов.
Изменение атрибутов С основной страницы опорного плана SQL можно изменить любой атрибут опорного плана. Для изменения атрибута
Просмотр плана выполнения опорного плана SQL Чтобы просмотреть фактический план выполнения для опорного плана SQL, кликните по имени плана. Для рассмотрения планов выполнения из опорных планов для данного оператора SQL кликните по кнопке SQL Text. Эволюция опорного плана SQL Находясь на основной странице опорного плана SQL, можно увидеть, какие планы приняты, а какие - нет. Если вы захотите проследить эволюцию непринятого плана:
Мониторинг 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-оператора находится новый план, он не будет использован, пока для него не будет подтверждено, что его производительность сопоставима с производительностью текущего плана или лучше ее. |