Самонастраивающаяся база данных:
управляемые приложения и настройка 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

Оглавление

Часть I

• Введение
• Опция Automatic SQL Tuning
• Оптимизатор автоматической настройки - Automatic Tuning Optimizer
  • Статистический Анализ
  • Профилирование SQL-предложений - SQL Profiling
    • SQL Profile
  • Анализ пути доступа
  • Анализ SQL- структуры

Часть II

• Объекты настройки STS
• Интерфейс настройки SQL Tuning
  • Настройка ADDM SQL
  • Настройка головных SQL-предложений
  • Настройка STS
  • Настройка опций
  • Обзор рекомендаций SQL Tuning
• Пакет DBMS_SQLTUNE
  • Настройка управления задачами (Task Management)
  • Управление SQL Profile
  • Управление STS (SQL Tuning Set)
• Заключение

Часть I

Введение

За прошедшее десятилетие выявились две четкие тенденции:

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

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

Одна из важных частей настройки работы систем баз данных – это настройка SQL-предложений. Настройка SQL включает в себя три основных этапа:

1. Определить SQL-предложения, имеющие наибольшую нагрузку (другими словами, топ-предложения), которые ответственны за большую часть работы приложений и потребление системных ресурсов, путем просмотра хронологии SQL-деятельности, доступной в системе (например, статистика кэша курсоров сохраняется в динамическом представлении V$SQL).
2. Проверить, что планы выполнения, предложенные оптимизатором запросов для данных предложений, выполняются достаточно хорошо.
3. Определить действия, необходимые для исправления плохо выполняемых SQL-предложений, для получения улучшенных планов.

Эти три этапа повторяются до тех пор, пока система не достигнет удовлетворительного уровня, или до тех пор, пока не останется предложений для настройки. Эксперты – АБД или разработчики приложений, которые имеют глубокие знания в области приложений и систем баз данных, обычно производят именно такую настройку.

Корректирующие действия могут включать один или несколько следующих шагов:

1. Накапливать и обновлять статистические данные, используемые оптимизатором запросов для построения планов выполнения. Например, создание гистограммы в столбце, содержащем асимметричные (skewed) данные.
2. Изменить значение некоторого параметра конфигурации, который будет влиять на построение плана исполнений оптимизатором запросов. Например, применить значение optimizer_mode к first_rows_10.
3. Переписать SQL-предложение для использования в соответствующей SQL конструкции. Например, когда возможно, заменить оператор UNION на UNION-ALL.
4. Создать или удалить структуру доступа к данным таблицы. Например, создать индекс или материализованное представление.
5. Добавить указания оптимизатора в предложение. Например, использовать указатель (hint) INDEX в таблице для изменения full table scan (полное сканирование таблицы) на index range scan (сканирование индексного диапазона).

Процесс ручной настройки SQL ставит перед разработчиком несколько задач. Во-первых, он требует высококвалифицированной экспертной оценки в нескольких сложных областях: оптимизация запросов, разработка доступа, SQL-проектирование (design). Во-вторых, это трудоемкий процесс, потому как каждое предложение уникально и требует индивидуального решения, и, кроме того, число предложений может быть очень велико, например, больше тысячи. В-третьих, требуются глубокие знания структуры схемы (то есть, определение представлений, индексов, размеров таблиц, и т.д.) и модели используемых данных приложений. Наконец, SQL настройка является непрерывным процессом, потому что объем SQL-работы все время изменяется, например, когда используется модуль нового приложения. Далее, изменения в структуре доступа к данным (например, когда создается или удаляется индекс или материализованное представление), очень вероятно внесут изменения в планы выполнения, вынуждая разработчика приложений начать все заново. Рисунок 1 иллюстрирует ручной процесс настройки SQL.

Рисунок 1. Ручная настройка SQL

Для помощи администратору базы данных (АБД) и прикладному разработчику, столкнувшихся с этой проблемой, несколько компаний, выпускающих программное обеспечение, разработали инструменты диагностики и мониторинга, которые пытаются найти узкие места работы системы базы данных и предложить действия для их устранения. Некоторые из этих инструментов делают, в большинстве случаев, очень хорошую работу. Но, как правило, это инструменты не интегрированы с системным компонентом, на который они нацелены. Например, оптимизатор запросов – это компонент системы, ответственный за планы выполнения SQL-предложений. На самом же деле, оптимизатор запросов - это “черный ящик” (black box) для этих инструментов, и поэтому они должны интерпретировать информацию вне базы данных, чтобы осуществить настройку. Как следствие, результаты их настройки менее надежны и ограничены в области действия. Кроме того, недостаток интеграции приводит к тому, что внешние инструменты всегда отстают от последних обновлений и улучшений оптимизатора запросов.

В Oracle 10g большее внимание уделяется созданию самоуправляемых систем баз данных. Для осуществления автоматической настройки и мониторинга также как и в системе, работающей по требованию (on-demand), была введена опция, названная AWR (Automatic Workload Repository - автоматический репоиторий нагрузки). AWR каждые 30 минут (по умолчанию) просматривает данные производительности системы и постоянно (по умолчанию в течение 7 дней) хранит их, как историю системной рабочей нагрузки. Например, среди других функции AWR идентифицирует главные SQL-предложения, которые интенсивно расходуют ресурсы: использование процессора, чтение буферов, физические операции с дисками, вызовы синтаксического анализа (parse calls), использование разделяемой памяти и т.д. в каждом интервале времени. Другая управляющая опция ADDM (Automatic Database Diagnostics Monitor – автоматический монитор диагностики) была введена для автоматизации задачи непрерывного контроля действий системы, идентификации высокого уровня потребления системных ресурсов, и узкие места работы. В плане SQL-настройки ADDM определяет наиболнее нагруженные (high load) SQL-предложения. Была добавлена еще одна опции управления, которая автоматизирует сбор статистических данных в текущем интервале. Опция Automatic Statistic Collection доступна по умолчанию для недавно созданных баз данных Oracle 10g.

В Oracle 10g процесс SQL-настройки был автоматизирован путем ввода новой управляющей опции Automatic SQL Tuning. Она одинаково хорошо предназначена для работы с приложениями, как с OLTP, так и Data Warehouse. Automatic SQL Tuning базируется на недавно добавленной автоматической возможности настройки оптимизатора запросов, называемой Automatic Tuning Optimizer. Automatic SQL Tuning доступна посредством советчика (advisor), названного SQL Tuning Advisor. SQL Tuning Advisor берет одно или несколько SQL-предложений и продуцирует хорошо настроенные планы, основываясь на рекомендациях по настройке. SQL-предложения могли быть идентифицированы при помощи ADDM, AWR или вручную. Ручной процесс может содержать, например, тестирование набора SQL-предложений, которые должны быть еще использованы, для измерения индивидуальной производительности и идентификации тех предложений, которые имеют недостаточную производительность. Для обеспечения этого в Oracle 10g мы ввели новый объект настройки, названный SQL Tuning Set (STS), который подробно описывается в главе “SQL Tuning Set”. Рисунок 2 дает высокоуровневое представление о том, как Automatic SQL Tuning работает в Oracle 10g.

Рисунок 2. Автоматическая настройка SQL

Остальная часть статьи организована следующим образом. Во-первых, мы объясним, как работает Automatic SQL Tuning, затем детально рассмотрим Automatic Tuning Optimizer. Далее, мы опишем SQL Tuning Set, который позволяет пользователю создавать и настраивать выбранные объемы работ SQL. Затем мы введем понятие первичного интерфейса Automatic SQL Tuning, использую для этого Enterprise Manager и иллюстрируя это примерами. Мы приведем описание пакета DBMS_SQLTUNE, который группирует процедуры SQL-настройки, используемые для SQL Tuning Advisor API, а также для управления SQL Tuning Set и SQL Profiles. В заключение статьи мы подведем итог, выполнив сравнение настроек в Oracle9i и Oracle 10g.

Опция Automatic SQL Tuning

Automatic SQL Tuning тесно связана с оптимизатором запросов. Это дает несколько преимуществ:

1. настройка осуществляется системным компонентом, который, в конечном счете, ответственен за план выполнения и, следовательно, за SQL-производительность,
2. процесс настройки полностью основывается на стоимости, и это, естественно, учитывает любые изменения и расширения, сделанные в оптимизаторе запросов,
3. процесс настройки принимает во внимание прошлую статистику выполнения SQL-предложений и настраивает параметры настройки оптимизатора для того или иного предложения, и
4. собирает вспомогательную информацию вместе с правильной статистикой, основанной на том, что считает полезным оптимизатор запросов.

Фактически Automatic SQL Tuning основана на усовершенствованной версии оптимизатора запросов. Обычный оптимизатор запросов был усовершенствован в Oracle 10g для выполнения дополнительного анализа перед процессом построения плана выполнения SQL-предложения. Мы будем называть такое состояние оптимизатора запросов, как Automatic Tuning Optimizer, для того, чтобы отличать это состояние от стандартного. Оптимизатор запросов (в обычном состоянии) имеет строгие ограничения на время и системные ресурсы, которые он может использовать для поиска наилучшего плана выполнения заданного SQL-предложения. Приемлемое время оптимизации обычно меньше секунды и не больше, чем несколько секунд. Из-за этого строгого требования оптимизатор может выполнять лишь ограниченный план поиска, используя эвристику тогда и только тогда, когда это может сократить время на оптимизацию. Следовательно, оптимизатор запросов не может выполнить трудоемкий анализ и этапы проверки перед процессом генерации плана.

Напротив, выполнение Automatic Tuning Optimizer обычно занимает намного больше времени, обычно несколько минут для того чтобы выполнить необходимые исследования и этапы проверки, как части процесса настройки. Таким образом, Automatic Tuning Optimizer имеет гораздо более высокую вероятность сгенерировать хорошо отлаженный план. Automatic Tuning Optimizer использует динамический отбор и частичное выполнение (то есть, выполнение фрагментов SQL-предложений), для проверки собственной оценки стоимости, избирательности и кардинального числа (cardinality). Он также использует предысторию выполнения SQL-предложений для определения оптимальных параметров настройки (например, режима оптимизатора).

Функциональные возможности Automatic Tuning Optimizer реализуются при помощи советчика SQL Tuning Advisor. SQL Tuning Advisor принимает SQL-предложение и передает его наряду с другими входными параметрами в Automatic Tuning Optimizer. Потом Automatic Tuning Optimizer выполняет четыре типа анализа в процессе генерации плана.

Функциональные возможности Automatic Tuning Optimizer доступны через советчика, названного SQL Tuning Advisor. SQL Tuning Advisor принимает SQL-предложение и передает его наряду с другими входными параметрами в Automatic Tuning Optimizer. Потом Automatic Tuning Optimizer выполняет четыре типа анализа в процессе генерации плана.

1. Statistics Analysis (Статистический Анализ): Automatic Tuning Optimizer проверяет каждый объект запроса на пропущенные или устаревшие данные статистики и дает рекомендации для сбора соответствующей (relevant) статистики. Он также собирает вспомогательную информацию для поставки отсутствующей или исправления устаревшей статистики в случае, если не выполнены рекомендации.
2. SQL Profiling (Профилирвание SQL ): Automatic Tuning Optimizer проверяет собственные оценки и собирает вспомогательную информацию, чтобы удалить ошибочные оценки. Он также собирает вспомогательную информацию в форме самоопределяемых параметров настройки оптимизатора (например, по первым строкам, а не по всем строкам - first rows vs. all rows), основанных на прошлой истории выполнения SQL-предложений. Он формирует SQL Profile, используя вспомогательную информацию, и дает рекомендации для его создания. Когда SQL Profile уже создан, он задействует оптимизатор запросов (в обычном состоянии) для генерации хорошо отлаженного плана.
3. Access Path Analysis (Анализ путей доступа): Automatic Tuning Optimizer выявляет, какие новые индексы могут быть использованы для улучшения доступа к каждой таблице в запросе и затем делает соответствующие рекомендации для их создания.
4. SQL Structure Analysis (Анализ SQL-структуры): в этом случае Automatic Tuning Optimizer пытается идентифицировать SQL-предложения, планы которых определяются как неудачные, и советует, как их реструктурировать. Предложения по изменениям SQL- кода могут относиться как к синтаксису, так и к семантике.

Каждый тип анализа детально рассмотрен в следующей главе “Automatic Tuning Optimizer”.

Результаты (outputs), сгенерированные Automatic Tuning Optimizer, передаются пользователю через SQL Tuning Advisor в виде совета (advice). Совет состоит из одной или нескольких рекомендаций, каждая из которых снабжается объяснением и оценкой выгоды при осуществлении. Пользователю предоставляется возможность принятия совета, и, таким образом, настройка соответствующего SQL-предложения завершается.

Automatic Tuning Optimizer вместе с SQL Tuning Advisor составляют компонент Automatic SQL Tuning сервера Oracle. Архитектура Automatic SQL Tuning, как показано на рисунке 3, иллюстрирует функциональные отношения между Automatic Tuning Optimizer и SQL Tuning Advisor.

Automatic SQL Tuning является частью продвигаемой Oracle стратегии перехода на управляемую по советам модель администрирования баз данных. Модель предполагает, что для критических функций управления базой данных сервер базы данных должен дать полезный совет пользователям о том, как лучше пользоваться ими. Механизм самой базы данных теперь сделан более интеллектуальным, чтобы пользователи Oracle для управления своими базами данных наиболее оптимальным способом больше не полагались на сторонние инструменты и решения.

Рисунок 3. Архитектура опции Automatic SQL Tuning

Оптимизатор автоматической настройки - Automatic Tuning Optimizer

Важно обратить внимание на то, что Automatic Tuning Optimizer – это оптимизатор запросов, который работает в специальным автоматическом режиме настройки. Поэтому Automatic Tuning Optimizer выполняет те же функции, что и обычный оптимизатор запросов, но с дополнительными возможностями и функциональностью. Эта дополнительная функциональность включает в себя этапы проверки правильности собственных внутренних оценок, а также поддержки внешней информации (например, статистические данные). Сюда же включаются этапы исследования для нахождения новых путей доступа, существенно повышающих производительность, и анализ возможности проведения изменений в SQL-предложениях для эффективной обработки данных.

SQL Tuning Advisor переводит оптимизатор в режим автоматической настройки, чтобы получить совет для SQL-предложении. Такой совет может состоять из различных рекомендаций от Automatic Tuning Optimizer, касающихся статистики, плана запросов, путей доступа (например, индексов) и SQL-конструкций. В дополнение к сгенерированным рекомендациям он может собрать определенную информацию для собственного использования SQL-предложением. Это - вспомогательная информация, которая будет использоваться вместе со статистикой базы данных. Automatic Tuning Optimizer использует несколько типов анализа: статистический анализ, SQL-профилирование (profiling), анализ пути доступа и анализ SQL-структуры. Каждый из этих анализов может дать отдельную рекомендацию для SQL-предложения.

Статистический Анализ

Оптимизатор запросов в обычном режиме опирается на данные статистики. Важно, чтобы статистика была своевременно собрана и сохранена для оптимизации заданного SQL-предложения. Цель анализа статистики состоит в том, чтобы проверить, не пропущены или не устарели ли эти данные. Automatic Tuning Optimizer протоколирует типы статистических данных, которые фактически используются в процессе генерации плана, который потом проверяется. Например, для предиката равенства он протоколирует статистику различных значений столбца, тогда как для предиката диапазона он регистрирует статистику минимального и максимального значения столбца.

Как только запись фактически используемой статистики закончена, Automatic Tuning Optimizer продолжает проверять, имеются ли в наличии статистические данные, ассоциируемые с объектами запроса (то есть, таблица, индекс или материализованное представление). Если статистика доступна, то проверяется ее точность (то есть, свежесть и актуальность). Чтобы проверить точность статистики, он будет брать данные из соответствующего объекта запроса, и использовать выбранный результат для проверки точности.

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

Заметим, что анализ статистики имеет два вида вывода:

1. рекомендации для анализа объектов, для которых либо не найдена статистика, либо найдена, но устаревшая, и
2. вспомогательная информация для возмещения опущенной или корректировки устаревшей статистики.

Это хорошая идея для осуществления рекомендаций Analyze и повторного запуска Automatic SQL Advisor. Вспомогательная информация используется в случае, если рекомендации Analyze не были осуществлены. Вспомогательная информация сохраняется в SQL Profile, который описан в следующем разделе.

Пофилирование SQL-предложений - SQL Profiling

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

1. использование внутренней заданной по умолчанию избирательности предиката при отсутствие статистических данных (например, таблица не проанализирована),
2. когда используется сложный предикат, так что оптимизатор запросов не может понять, какие данные будут фильтроваться с помощью этого предиката,
3. наличие корреляции данных между столбцами таблицы, что означает что предположение о корреляции оптимизатором не верно,
4. асимметрия (skewed) или разреженность (sparse) отношений объединения между таблицами, что является очень трудным для поимки в виде статистики,
5. существование корреляции данных между столбцами двух или более таблиц (например, отношения объединение между продуктом, местоположением и продажами в таблице, где число продаж (#sales) зимних ботинок очень велико в New-York, но очень мало (возможно равная нулю) в Arizona).

Для генерации хорошего плана оптимизатором запросов важно сделать так, чтобы оценки не содержали больших ошибок. Следовательно, для Automatic Tuning Optimizer очень важно проверить точность собственных оценок и удалить или, в крайнем случае, значительно уменьшить ошибки в них.

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

В течение SQL Profiling выполняются этапы проверки Automatic Tuning Optimizer для проверки правильности своих собственных оценок. Проверка правильности состоит из анализа выборки данных путем применения к ней соответствующих предикатов. Новая оценка производится по результатам выборки данных. Эта новая оценка будет точнее, потому как размер выборки может быть в случае необходимости откорректирован для гарантированно высокого уровня точности. Новая оценка сравнивается с обычной, и, если различия будут достаточно велики, то продуцируется фактор коррекции, чтобы привести в соответствие обычную статистику с новой. Другой метод правильности проверки оценки состоит в выполнении фрагмента SQL-предложения (то есть, частичное выполнение). Метод частичного выполнения эффективнее, чем метод выборки, когда соответствующие предикаты обеспечивают эффективный доступ. Automatic Tuning Optimizer выбирает соответствующий оценочный метод проверки правильности.

Automatic Tuning Optimizer для выставления правильных настроек также использует прошлую статистику выполнения SQL-предложения. Например, если текущая статистика говорит о том, что SQL-предложение в большинстве случаев выполняется частично, тогда настройку переключают в режим first_rows. Это будет выбираемая настройка для настраиваемого SQL-предложения.

Automatic Tuning Optimizer формирует SQL Profile, если он сгенерировал вспомогательную информацию в течение анализа статистики (то есть, факторы настройки статистики) или в течение SQL Profiling (то есть, корректирующие факторы, настраивающие установки оптимизатора). Когда SQL Profile сформирован, он генерирует рекомендацию для пользователя по созданию SQL-профиля.

Поскольку используемые для оценочной статистик методы выборки данных или частичного выполнения могут быть дорогими или отнимающими много времени процессами, то SQL Profiling не выполняется в режиме Limited (ограничено). Вам следует использовать режим Comprehensive (комплексный), чтобы позволить Automatic Tuning Optimizer сгенерировать SQL Profile.

SQL Profile

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

Когда создается SQL Profile, он постоянно хранится в словаре данных. Однажды созданный SQL Profile будет использоваться каждый раз оптимизатором запроса, при компиляции (так как оптимальный) соответствующего SQL-предложения для создания хорошо отлаженного плана. Для управления SQL Profile предоставляется полный набор функций (см. раздел “DBMS_SQLTUNE Package”).

На рисунке 4 показан поток процессов создания и использования SQL Profile. Процесс состоит из двух отдельных стадий: стадия настройки SQL и стадия обычной оптимизации. На стадии настройки SQL АБД выбирает SQL-предложение для автоматической настройки и запускает SQL Tuning Advisor, используя GUI интерфейс Enterprise Manager (см. раздел “SQL Tuning Interface”) или использует интерфейс командной строки (см. раздел “DBMS_SQLTUNE Package”). SQL Tuning Advisor вовлекает Automatic Tuning Optimizer, возможно, с SQL Profile в генерирование рекомендаций настройки. Если SQL Profile создан, то АБД может принять его. Когда SQL Profile создан, то он сохраняется в словаре данных. Тогда на следующей стадии, когда конечный пользователь запускает то же самое SQL-предложение, оптимизатор запросов (в обычном режиме) использует SQL Profile для генерации хорошо отлаженного плана. Использование SQL Profile остается полностью прозрачным для конечного пользователя.

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

Рисунок 4. Создание и использование SQL Profile

Вспомогательная информация, содержащаяся в SQL Profile, сохраняется таким способом, что она остается релевантной даже после внесения в базу данных таких изменений, как добавление или удаление индексов, увеличение размеров таблиц и периодический сбор статистики базы данных. Однако, SQL Profile может не приспособиться к массовым изменениям в базе данных или к изменениям, которые происходят в течение продолжительного времени. В этом случае должен быть сформирован новый SQL Profile взамен старого. Например, когда SQL Profile устаревает, выполнение соответствующего SQL-предложения становится заметно хуже. В таком случае заданное SQL-предложение может снова быть показано сильно нагруженное, или топ, становясь, таким образом, снова целью для Automatic SQL Tuning.

Анализ пути доступа

Automatic Tuning Optimizer предоставляет советы и по индексам. Эффективное индексирование – это хорошо известная методика настройки, которая может значительно улучшить выполнение SQL-предложений, исключив полный просмотр данных. Любые индексные рекомендации, сгенерированные Automatic Tuning Optimizer, являются специфичными для заданного настраиваемого SQL-предложения. За счет этого обеспечивается быстрое нахождение проблемы, связанные с конкретным SQL-предложением.

Поскольку Automatic Tuning Optimizer не производит анализ, как его рекомендации влияют на рабочую нагрузку SQL-предложения, он рекомендует напускать Access Advisor на SQL-предложение, чтобы представить его нагруженность. Access Advisor накапливает все советы, поставленные по нагруженности каждого SQL-предложения и объединяет их в глобальные рекомендации по полной нагруженности .

Анализ SQL- структуры

Часто SQL-предложение может быть слишком ресурсоемким только потому, что оно плохо написано. Это обычно случается, когда существуют различные (но не обязательно семантически эквивалентные) способы написания предложения для достижения того же самого результата. Например, SQL-предложение может давать тот же результат, когда его оператор UNION заменяется на UNION-ALL. Тот же самый результат возможен, если для исключения порождения двойных строк, устранение дублирования делается при помощи избыточного оператора UNION. В этом случае его лучше заменить на UNION-ALL, что устранит из плана выполнений затратный этап устранения дублирования. Другой пример – это использование подзапроса NOT IN в то время, как подзапрос NOT EXIST продуцировал бы тот же результат намного более эффективно.

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

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

Automatic Tuning Optimizer выполняет анализ SQL-структуры, чтобы обнаружить плохо написанные SQL-предложения, и рекомендует подвергнуть результат пользовательской поверке на предмет нахождения альтернативных способов написания SQL-предложения для улучшения его производительности. Для помощи в написании правильных SQL-предложений и в целях профилактики разработчики могут запускать SQL Tuning Advisor в режиме Limited.

В процессе построения плана при анализе SQL-структуры Automatic Tuning Optimizer генерирует обширные аннотации и диагностики и связывает их с планом выполнения. Аннотации содержат решения, сделанные оптимизатором, и приводят основания таких решений. Используя эти основания, ассоциированные с дорогостоящими операторами плана выполнения, Automatic Tuning Optimizer дает рекомендации или о том, как переписать SQL-предложение, или о том, как изменить схему, для улучшения производительности.

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

1. Semantic-Based Constructs: конструкция типа подзапрос NOT IN, когда заменяется соответствующим, но не являющимся семантическим эквивалентом подзапроса NOT EXISTS, может привести к существенному повышению производительности. Однако такая замена возможна, только если в связанных (join) столбцах объединения не присутствуют NULL-значения, гарантируя, таким образом, один и тот же результат при использовании любого из этих операторов. Другой пример – замена UNION на UNION-ALL, если нет вероятности получения дублированных строк в результате.
2. Syntax-based Constructs: многие из них связаны с тем, как предикаты определены в SQL-предложении. Например, если предикат, такой как col=:bnd используется с col и :bnd, имеющих разные типы, то такой предикат не может быть использован как наездник (driver) индекса. Точно так же предикат, вовлекающий функцию или выражение (например, func(col)=:bnd, col+1=:bnd), не может быть использован как наездник индекса, если нет функционального индекса на выражение или на самое функцию.
3. Design Issue: случайное использование, например, декартового произведения (Cartesian product) является обычной проблемой, встречающейся в том случае, когда одна из таблиц не соединена ни к какой-либо другой таблице в SQL-предложении. Это может произойти, если в запросе участвует большое число таблиц.

Часть II