Самонастраивающаяся база данных:
управляемые приложения и настройка SQL

Бенуа Дагервилл, Муджес А.Минхас, Халед Ягуб, Монахем Зайт, Монахем Зиауддин, Корпорация Oracle
(Benoit Dageville, Mughees A. Minhas, Khaled Yagoub, Mohamed Zait, Mohamed Ziauddin, all - Oracle Corp)

Источник: доклад на Oracle World-2003, Paris,
"The Self-Managing Database: Guided Application & SQL Tuning" - формат Word, 512 КБ
(файл с оригиналом доклада можно также скачать здесь)

Перевод: Oracle Magazine RE

Часть II

Оглавление

Объекты настройки STS (SQL Tuning Set)

ADDM автоматически идентифицирует наиболее нагруженные SQL- предложения для того, чтобы пользователь их выбрал и настроил. AWR позволяет выбирать головные (top) SQL-предложения в интервале времени. Однако пользователю может захотеться настраивать набор выбранных SQL-предложений в том порядке, который установит сам пользователь. Хороший пример такой ситуации, когда разработчик находится в процессе разработки и испытания новых SQL-предложений. До тех пор, пока некое SQL-предложение не встроено в систему, обычные SQL-источники (например, AWR) применяться не могут. Таким образом, нужен пользовательский механизм для создания его собственной рабочей SQL-нагрузки и применения к ней Automatic SQL Tunung. Таким механизмом является SQL Tuning Set (STS). Поддержка STS была введена в Oracle 10g для облегчения управления набором SQL-предложений, т.е. управление [набором] как единым модулем.

SQL Tuning Set - это база данных, которая хранит одно или более SQL-предложение вместе с их статистикой и состоянием выполнения и, возможно, с пользовательским ранжированием приоритетов. SQL-предложение может быть загружено в SQL Tuning Set из различных SQL-источников. SQL-источники включают Automatic Workload Repository (AWR), курсорный кэш и выбранный SQL, предоставляемый пользователем. Рисунок 5 показывает модель типичного применения STS. STS создается и затем загружается с SQL-предложением из одного из SQL-источников. На рисунке показано, что SQL-предложения выбираются, ранжируются и фильтруются, перед тем как загрузиться в SQL Tuning Set.

Рисунок 5. Вид модели SQL Tuning Set

Контекст выполнения, сохраненный с каждым SQL-предложением, включает: схему пользователя, имя модуля приложения и события, список связанных значений и среду трансляции курсора. Сохраняется статистика выполнения, включая истекшее время, процессорное время, логические чтения буферов, физические чтения дисков, число обработанных строк, выборок курсоров, число выполнений, число закончившихся выполнений, стоимость [плана] оптимизатора и тип команды. Фильтрация SQL-предложений может быть выполнена, используя имя модуля приложения и действие, а также любой статистики выполнения. Из этого следует, что ранжирование SQL-предложений может быть выполнено на основе любой комбинации статистики выполнения.

SQL Tuning Set может постоянно храниться в базе данных. Содержание SQL Tuning Set может обновляться, как будто STS - это объект, и может управляться при помощи процедур пакета DBMS_SQLTUNE. Однажды загруженный STS можно передавать на ввод в SQL Tuning Advisor, который потом применит автоматическую настройку SQL-предложений, подверженных влиянию других входных параметров, специфицированных пользователем. Другие вводы включают: лимит времени, рамки анализа настройки и т.д. Таким образом, SQL Tuning Set предоставляет собой мощный метод сбора и сохранения интересующего набора SQL-предложений, наряду с большим скоплением информации, которая помогает в процессе настройки.

Интерфейс настройки SQL Tuning

Утилита Enterprise Manager (ЕМ) может быть использована для идентификации высокой нагрузки и головных SQL-предложений. В Enterprise Manager есть несколько мест, из которых может быть запущен SQL Tuning Advisor с идентифицированным SQL-предложением (-ями) или с SQL Tuning Set.

Настройка ADDM SQL

Следующий EM-экран показывает идентификацию высоко нагруженных SQL-предложений с помощью Automatic Database Diagnostics Monitor (ADDM). Как известно, каждое из этих высоко нагруженных SQL-предложений потребляют существенную порцию одного или более системных ресурсов, типа времени CPU, логических чтений буферов (buffer gets), физических чтений дисков и т.д. Этот экран позволяет пользователю запускать SQL Tuning Advisor для выбранного высоко нагруженного SQL-предложения.

Рисунок 6. ADDM находит высоко нагруженные SQL-предложения

Настройка головных SQL

Другой SQL-источник - это список головных (top) SQL-предложений на EM-экране, что показано на рисунке 7.

Список головных SQL-предложений идентифицируется просмотром совокупности их статистик выполнения в течение выбранного окна времени. Головные SQL-предложения могут быть ранжированы на основе соответствующей статистики, например, использование CPU. Пользователь может выбрать одно или более головное SQL-предложение, идентифицируемых своими ID, и применить к ним SQL Tuning Advisor.

Рисунок 7. Головные SQL-предложения

Настройка STS

Enterprise Manager также позволяет вам просматривать различные SQL Tuning Set, созданные разными пользователями. STS может быть создан из списка головных SQL-предложений, или выбором SQL-предложения из диапазона снимков, созданных Automatic Workload Reporitory (AWR), или созданием собственного SQL-предложения. Следующий экран Enterprise Manger показывает, как запустить SQL Tuning Advisor на выбранном STS.

Рисунок 8. Экран SQL Tuning Sets

Опции настройки

Если SQL Tuning Advisor уже запущен, то Enterprise Manager будет автоматически создавать задачу настройки, предлагаемую пользователем, если тот имеет для этого соответствующую привилегию ADVISOR. Enterprise Manager по умолчанию показывает задачу настройки с автоматическими значениями в SQL Tuning Options, экран которого показан ниже. На этом экране пользователь может воспользоваться вариантами для изменения автоматических значений по умолчанию, имеющих отношение к задаче настройки.

Рисунок 9. Опции SQL Tuning

Одной из важных опций является выбор область действия задачи настройки. Если вы выбираете опцию Limited, то SQL Tuning Advisor выдает рекомендации, основанные на проверке статистики, анализе путей доступа и анализе структуры SQL. Никакие рекомендации SQL Profile не будут сгенерированны с ограничением Limited. Если вы выбираете опцию Comprehensive, то SQL Tuning Advisor сделает все рекомендации с ограничением Limited, плюс вызовет оптимизатор в режиме профилирования SQL для построения SQL Profile, если это возможно. По опции Comprehensive вы можете также определить срок для задачи настройки, который по умолчанию составляет 30 минут. Другая полезная опция задает немедленное выполнение задачи настройки или откладывает ее на более позднее время.

Обзор рекомендаций SQL Tuning

Как только задача настройки завершается, могут быть просмотрены рекомендации, сгенерированные SQL Tuning Advisor. Enterprise Manager показывает как обзор рекомендаций, так и их подробности. Следующий экран демонстрирует краткий обзор рекомендаций SQL Tuning Advisor для одного или нескольких настраиваемых SQL-предложений. Как показано ниже, есть только одна рекомендация для создания SQL Profile для SQL-предложения. Если вы выбираете SQL-предложение и нажимаете кнопку View Recomendations, то Enterprise Manager покажет подробности рекомендации.

Рисунок 10. Обзор рекомендаций SQL Tuning

Следующий экран показывает подробности, связанные с SQL Profile, и коэффициент улучшения работы, если эта рекомендация будет принята. Вы можете принять эту рекомендацию, нажав на кнопку Implement, создавая, таким образом, SQL Profile.

Рисунок 11. Подробности рекомендаций SQL Tuning

Пакет DBMS_SQLTUNE

Хотя для Automatic SQL Tuning главным интерфейсом является Oracle Enterprise Manager, для настройки SQL-предложений может использоваться и интерфейс командной строки пакета DBMS_SQLTUNE. DBMS_SQLTUNE - это новый пакет, добавленный в Oracle10g, и он содержит необходимые API для использования возможностей Automatic SQL Tuning, включая задачи для выполнения автоматической настройки предложений и управления SQL Profile и SQL Tuning Sets.

Настройка управления задачами (Task Management)

Важно заметить, что SQL Tuning Advisor, подобно всем другим управляющим советчикам, сформирован на основе общего Advisor Framework. Advisor Framework обеспечивает общую поддержку инфраструктуры для построения, сохранения и поиска советов, сгенерированных при помощи различных управляющих возможностей, включая SQL Tuning Advisor.Поэтому все процедуры настройки SQL работают с объектами задач советчика, названными задачами настройки. Это значит, что для выполнения автоматической настройки должна быть создана задача настройки. Использование SQL- процедур настройки, включая создание задач настройки, требует наличия привилегии ADVISOR.

Для выполнения автоматической SQL-настройки, используя пакет DBMS_SQLTUNE, первым шагом всегда будет создание задачи настройки вызовом процедуры create_tuning_task. Эта процедура создает задачу советчика и задает ей соответствующие параметры, согласно установленным пользователем входным параметрам.

Есть несколько разновидностей процедуры create_tuning_task, которые могут быть использованы для создания задач настройки для настройки одного SQL-предложения или множества предложений, сохраненных в SQL Tuning Set.

Следующий пример показывает одну форму процедуры create_tuning_task, которая позволяет передавать текст SQL-предложению непосредственно, как параметр. В этом примере текст предложения передается, как CLOB.

create_tuning_task(
         sql_text    => ‘select * from emp where emp_id = :bnd', 
         bind_list   => sql_binds(anydata.ConvertNumber(100)), 
         user_name   => ‘scott', 
         scope       => ‘comprehensive',
         time_limit  => 60,
         task_name   => ‘my_sql_tuning_task',
         description => ‘task to tune a query on a specified employee');

В этом примере целевое предложение использует связанную переменную bnd, значение которой (100) - это число, передающееся как функциональный аргумент типа SQL_BINDS. SQL_BINDS - это новый тип объектов, введенный в Oracle 10g. Параметр scott предоставляет имя схемы, в которой анализируется представление. Возможности настройки задачи передаются, как comprehensive, чтобы сообщить SQL Tuning Advisor, что нужно сделать полный анализ, включая генерацию SQL Profile. И, наконец, аргумент 60 - это предел по времени в секундах для настройки SQL-предложения.

Есть две другие формы этой процедуры, доступные для адресата специфического SQL-предложения, выбранного или из кэша курсоров, или из AutomaticWorkload Repository (AWR). В любом случае предложение будет идентифицировано передачей его SQL_ID вместо SQL-текста.

Когда задача настройки еще только успешно создана, она находится в первоначальном состоянии. Затем задача должна быть выполнена для запуска процесса настройки. Это достигается с помощью запуска процедуры execution_tuning_task, как показано ниже:

execute_tuning_task(task_name => ‘my_sql_tuning_task');

В любое время, после того как началось выполнение, пользователь может использовать соответствующую процедуру советчика для отмены, прерывания или сброса задачи. Пользователь может также проверить состояние задачи путем просмотра информации, помещенной в рабочее представление DBA_ADVISOR_LOG, или может запросить представление V$SESSIO_LOGOPS для отображения информации о прогрессе выполнения задачи. Эта информация включает оставшееся время на выполнение, количество результатов, выгоду и число предложений, использованных SQL Tuning Set для настройки.

Когда задача настройки закончена, результаты настройки могут быть визуализированы, вызовом процедуры report_tuning_task, как показано ниже:

set long 10000;
  select report_tuning_task(task_name => ‘my_sql_tuning_task')
  from dual;

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

Результаты Automatic SQL Tuning всегда могут быть просмотрены, используя структурные представления советчика АБД, такие как: DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_RATIONALE и т.д. Помимо этих представлений, которые являются общими для всех советчиков в Oracle 10g, Automatic SQL Tuning расширяет структуру, добавляя новые представления, которые могут отображать специфическую информацию SQL-настроек, такую как SQL-статистики, связанные присваивания и планы выполнения. Для проверки таких результатов пользователь может запросить представления DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS и DBA_SQLTUNE_PLANS.

Управление SQL Profile

Процедуры для управления SQL Profile также являются частью пакета DBMS_SQLTUNE. Когда SQL Profile рекомендуется SQL Tuning Advisor, то SQL Profile может быть создан вызовом процедуры accept_sql_profile, которая сохранит его в словаре данных. Для создания SQL Profile требуется привилегия CREATE ANY SQL PROFILE. Однажды созданный SQL Profile будет автоматически применяться ко всем следующим выполнениям такого же SQL-предложения. Например, следующий вызов процедуры сохраняет SQL Profile, произведенный автоматической настройкой SQL-предложения, связанного с задачей настройки my_sql_tuning_task. В этом примере SQL Profile получает имя my_sql_profile.

accept_sql_profile(
       task_name => ‘my_sql_tuning_task',
       name      => ‘my_sql_profile');

Информация о SQL Profile может быть получена через представление DBA_SQL_PROFILES. Пользователь может также изменить атрибуты существующего SQL Profile, выполняя процедуру alter_sql_profile. Чтобы сделать это, требуется привилегия ALTER ANY SQL PROFILE.

В следующем примере my_sql_profile атрибут состояния SQL Profile изменится на disabled, который означает, что SQL Profile больше не будет использоваться для генерации плана выполнения соответствующего SQL-предложения.

alter_sql_profile(
      name           => ‘my_sql_profile',
      attribute_name => ‘status',
      value          => ‘disabled');

Другие атрибуты SQL Profile, которые могут быть изменены: имя, описание и категория. Наконец, SQL Profile может быть удален из словаря данных, используя процедуру drop_sql_profile. Это требует привилегии DROP ANY SQL PROFILE.

Управление SQL Tuning Set

Типичный сценарий использования пакета DBMS_SQLTUNE для SQL Tuning Set (или просто sqlset) включает в себя создание нового SQL Tuning Set, загружая его набором высоко нагруженных SQL-предложений, выбирая и просматривая его содержимое для ручного анализа и дальнейшего модернизирования и выбора, затем запуск SQL Tuning Advisor для автоматической настройки всех предложений в SQL Tuning Set, и, наконец, исключение SQL Tuning Set после выполнения рекомендаций SQL Tuning Advisor.

В следующем примере процедура create_sqlset создает SQL Tuning Set с именем my_sql_tuning_set, который может быть использован для загрузки ввода-вывода интенсивных SQL-предложений, собранных в течение определенного периода времени.

create_sqlset(
      sqlset_name => 'my_sql_tuning_set',
      description => 'I/O intensive workload');

Эта процедура создает в базе данных пустой SQL Tuning Set. Обратите внимание на то, что для выполнения процедуры SQL Tuning Set пользователь должен иметь привилегию ADMINISTER SQL TUNING SET или ADMINISTER ANY SQL TUNING SET.

После создания SQL Tuning Set процедура load_sqlset может быть использована для заполнения его выбранными SQL-предложениями. Стандартный источник заполнения SQL Tuning Set - это Automatic Workload Repository (AWR), кэш курсоров или другой SQL Tuning Set, который был создан или загружен ранее. Для каждого из этих источников есть предопределенные табличные функции, которые могут быть использованы для извлечения и фильтрования исходного содержимого перед загрузкой в новый SQL Tuning Set.

Например, следующие вызовы процедуры используются для загрузки my_sql_tuning_set базовой строки AWR, называемой “peak baseline”, выбирая только те SQL-предложения, которые были выполнены не менее 10 раз, и которые имеют отношение (disk-reads/buffer-gets) более 50% в течение базового периода [времени]. SQL-предложения упорядочиваются по отношению (disk-reads/buffer-gets) и выбираются только 30 лучших SQL-предложений.

-- open a ref cursor to select from the specified baseline
  open baseline_ref_cursor for 
    select value(p)
    from table (dbms_sqltune.select_baseline(
         ‘peak baseline',
         ‘executions >= 10 and disk_reads/buffer_gets >= 0.5',  
          null, 
          disk_reads/buffer_gets, 
          null, null, null,
          30)) p;
  -- load statements and their stats from the baseline into the STS
  dbms_sqltune.load_sqlset(
       sqlset_name     => 'my_sql_tuning_set',
       populate_cursor => baseline_cur);

Теперь, когда SQL Tuning Set был создан и заполнен, АБД может просмотреть SQL-предложение в SQL Tuning Set, используя процедуру select_sqlset, как показано ниже:

SELECT * from TABLE(select_sqlset(
             'my_sql_tuning_set',
             '(disk_reads/buffer_gets) >= 0.75'));

В этом примере были отображены только SQL-предложения с отношением (disk-reads/buffer-gets) >75%. Подробности SQL Tuning Set, который был создан и загружен, могут быть просмотрены, используя представления АБД DBA_SQLSET, DBA_SQLSET_STATEMENTS и DBA_SQLSET_BINDS.

SQL-предложения могут также быть модифицированы и удалены из SQL Tuning Set, основанного на условиях поиска. Например, следующая процедура delete_sqlset удалит из my_sql_tuning_set все SQL-предложения, которые были выполнены меньше, чем 50 раз.

delete_sqlset(sqlset_name  => 'my_sql_tuning_set',
              basic_filter => 'executions < 50');

Наконец, когда SQL Tuning Set более не требуется (например, после настройки всех инструкций он содержит и осуществляет необходимые рекомендации) он может быть удален процедурой drop_sqlset, как показано далее:

drop_sqlset(sqlset_name => 'my_sql_tuning_set');

Заключение

В этой статье мы описали управляющий компонент Automatic SQL Tuning, который был добавлен в Oracle 10g. Automatic SQL Tuning обеспечивает автоматическую настройку SQL-предложений в виде набора всесторонних рекомендаций по настройке. Он тесно связан с оптимизатором запросов. Фактически, оптимизатор запросов работает в режиме автоматической настройки и генерирует рекомендации по настройке. Когда это потребуется, он также может сформировать SQL Profile в дополнение к рекомендациям. Пользователь может выбрать осуществляемые рекомендации, включая SQL Profile. Однажды созданный SQL Profile будет использоваться оптимизатором запросов для генерирования хорошо отлаженных планов для соответствующего SQL-предложения. Объект настройки, вызвавший SQL Tuning Set, представляется для осуществления возможности пользователя создавать собственную SQL-нагрузку для целевой настройки. Интерфейс для Automatic SQL Tuning был создан, используя Enterprise Manager, с возможностями выбора из разных SQL-источников и настройки SQL-предложений с различными возможностями настройки.

Мы завершаем эту статью, показав, как существенно Automatic SQL Tuning упрощает процесс настройки. Общая проблема наблюдений – это снижение эффективности SQL-предложений во время работы, так как через какое-то время увеличивается количество данных. Для SQL-предложений, внедренных в пакетные приложения, следующая таблица сравнивает шаги настройки SQL, выполняемые в Oracle 9i и Oracle 10g.

Шаги	Oracle 9i	Oracle 10g
1	Получение плана выполнения	Запускает SQL Tuning Advisor
2	Проверка объектов запроса и их размер	Выполнение рекомендаций
3	Просмотр и сравнение статистики плана выполнения со статистикой выполнения (сохраняется в представлении V$SQL)
4	Идентификация как будто это проблема “first rows”, потому что отображаются только недавние данные, несмотря на запрос к давней хронологии
5	Контакт с производителем приложения
6	Подготовка тестового варианта для производителя
7	Получение от производителя исправления “first rows”
8	Установка исправления в следующем цикле обслуживания

Как выше показано в таблице, усилия и время, потраченные экспертом-настройщиком на эту довольно обычную задачу в Oracle 9i, значительно больше по сравнению с Oracle 10g. Кроме того, в Oracle 9i клиент для исправления должен ждать [реакции] производителя приложения, что может занять недели и месяцы, тогда как в Oracle 10g разрешение проблем [следует] немедленно. Automatic SQL Tuning предлагает для настройки приложений комплексные, легкие в использование решения, которые могут одинаково эффективно быть использованы, как новичком, так и опытным пользователем.