Настройка SQL-запросов при помощи Optim Query Tuner: Часть 2. Настройка отдельных запросов

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

В первой части этой серии статей были рассмотрены основные концепции путей доступа и приемы чтения схем путей доступа в Optim Query Tuner. В данной статье представлена методология настройки отдельных запросов. На примере запроса мы покажем, как можно использовать для этого программу Optim Query Tuner. Процесс включает в себя форматирование и аннотирование запроса, а также анализ плана доступа, статистики, предикатов и индексов. Цель – убедиться в том, что оптимизатор IBM® DB2® обеспечен всей необходимой информацией для принятия наилучших решений по оптимизации производительности DB2-запросов, и узнать, как предоставить оптимизатору DB2 больше вариантов, улучшающих доступ (например, создать необходимые индексы).

Джин Фу, инженер , IBM

Джин Фу (Gene Fuh) – фотографияДжин Фу (Gene Fuh) работает в IBM над технологиями баз данных с 1994 года. Он пришел в организацию DB2 for z/OS в 2000 году после шести с половиной лет работы в DB2 LUW. В 2004 году Джин организовал группу по разработке DB2 OE/OSC (DB2 Optimization Expert и Optimization Service Center). Он был главным архитектором и куратором проекта до 2007 года, когда продукт был выпущен одновременно с DB2 9 for z/OS. В 2008 году Джин инициировал перевод технологии DB2 OE/OSC в решения по настройке, известные сейчас как Optim Query Tuner и Optim Query Workload Tuner. За 17 лет работы в IBM Джин зарегистрировал 48 патентов и опубликовал более 20 технических статей на академических и IBM-конференциях.



Кендрик Рен, программист-консультант, IBM

Кендрик Рен (Kendrick Ren) – фотографияКендрик Рен (Kendrick Ren) является техническим руководителем продуктов IBM Optim Query Tuner и Optim Query Workload Tuner в IBM Toronto lab. Он работал еще с первыми редакциями этих продуктов (тогда они назывались DB2 Optimization Expert и Optimization Service Center) с момента создания группы в 2004 году. Кендрик тесно сотрудничает с пользователями и бизнес-партнерами, использующими эти продукты, помогая им в области оптимизации запросов. До прихода в группу Optimization Expert Кендрик два года работал над продуктом IBM WebSphere Commerce Server.



Кэтрин Зейденстейн, старший инженер-программист, IBM

Кетрин Зейденстейн (Kathy Zeidenstein) – фотографияКэтрин Зейденстейн (Kathy Zeidenstein) работает в IBM уже много-много лет. В настоящее время она является членом группы IBM Optim Solutions Technical Enablement, где отвечает за взаимодействия и организацию работы сообщества. До этого она работала менеджером по маркетингу продуктов, использующих технологии текстового поиска и аналитики.



Цян Сун, программист-консультант, IBM

Цян Сун (Qiang Song) – фотографияЦян Сун (Qiang Song) является техническим руководителем разработки продуктов IBM Optim Query Tuner и Optim Query Workload Tuner. Он работает в IBM China Development Lab с 2005 года. Кроме того, он руководит службой поддержки пользователей. Цян имеет богатый опыт в настройке производительности SQL-запросов и в Eclipse-разработке.



12.03.2012

Введение

В первой части данной серии статей Настройка SQL-запросов при помощи Optim Query Tuner. Часть 1: введение в пути доступа была рассмотрена концепция пути доступа. Для конкретного SQL-выражения часто существует несколько вариантов путей доступа, и эти различные пути обычно имеют различные характеристики производительности. Перед выполнением SQL-выражения оптимизатор DB2 оценивает издержки возможных путей доступа и выбирает наилучший. Этот процесс реализуется на этапе подготовки (PREPARE) для динамического SQL-выражения или на этапе связывания (BIND) для статического SQL-выражения.

Хотя оптимизатор DB2 полезен для выбора наилучшего пути доступа, результат зависит от входных данных, которые часто недоступны для оптимизатора или не управляются им. Разработчикам и администраторам баз данных полезно уметь настраивать запросы так, чтобы предоставить оптимизатору DB2 наилучшую исходную информацию.

В этой статье вы познакомитесь с методологией настройки единичных запросов, включая обоснование важности умения настраивать запросы даже при наличии в DB2 оптимизатора мирового класса. Затем на примере запроса будет объяснена методология настройки с использованием соответствующих возможностей программы Optim Query Tuner, которые очень полезны для понимания, анализа и настройки конкретных запросов.

Отметим, что в этой статье описывается главным образом настройка SQL в системе DB2 for z/OS, но большинство концепций оптимизации и методология настройки SQL применимы также и для DB2 for Linux®, UNIX® и Windows®.

При желании поработать с примером запроса, описываемого в данной статье, можно загрузить файл проекта, ссылка на который приведена в разделе Загрузка, и импортировать его в Data Studio (автономный или IDE-пакет с Fix Pack 1 и старше) или в любой из продуктов Optim Query Tuner.

Чтобы импортировать пример проекта, выполните следующие действия:

  1. Откройте перспективу IBM Query Tuning в программе Data Studio или Optim Query Tuner.
  2. Выберите File > Import....
  3. В окне мастера импорта перейдите к Query Tuner > Projects и нажмите кнопку Next.
  4. Нажмите кнопку Browse и выберите каталог, содержащий загруженный zip-файл, чтобы просмотреть список проектов в окне Projects.
  5. Выберите samplequerytuningproject и нажмите кнопку Finish.
  6. В окне Project Explorer должен появиться пример проекта. Если окна Project Explorer нет, убедитесь, что вы находитесь в перспективе IBM Query Tuning, и выберите Window > Reset Perspective. В качестве альтернативы можно выбрать Window > Show View > Project Explorer.

О решениях Optim для настройки запросов

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

  • Базовая настройка и форматирование отдельных запросов доступны в автономной версии Data Studio 2.2.0.1. Этот продукт предоставляется бесплатно как для DB2 for z/OS, так и для DB2 for Linux®, UNIX® и Windows®. Обратите внимание, что хотя в этой серии статей интерпретация схем путей доступа рассматривается на примере Data Studio, в этом продукте доступны не все описываемые возможности.
  • Настройка и форматирование отдельных запросов, а также более широкий набор советчиков, доступны в Optim Query Tuner. Этот продукт предлагается как для DB2 for z/OS, так и для DB2 for Linux, UNIX и Windows.
  • Настройка распределения рабочей нагрузки, настройка отдельных запросов и полный набор советчиков предоставляются в Optim Query Workload Tuner. Этот продукт доступен только для DB2 for z/OS (на момент написания данной статьи).

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

Отметим, что данная статья в основном посвящена методологии настройки запросов; для иллюстрации рассматриваемых тем в ней используются снимки экранов программы Optim Query Tuner. В статье нет пошаговых инструкций по использованию Query Tuner. Ссылки на дополнительную информацию о возможностях продукта и подробные инструкции по использованию различных функций OQT приведены в разделе Ресурсы.

Обзор оптимизации запросов

Как показано на рисунке 1, оптимизатор DB2 выбирает наилучший путь доступа.

Рисунок 1. Обзор оптимизатора DB2
Рисунок 1. Обзор оптимизатора DB2

Оптимизатор сравнивает издержки каждого возможного пути доступа, основываясь на информации, получаемой из различных источников, таких как:

  • Статистика по каталогу
    Оптимизатор DB2 – это оптимизатор, принимающий решения на основе оценки издержек. Краеугольным камнем такой оптимизации является набор статистической информации, позволяющей точно оценить издержки всех возможных путей доступа и выделить самые эффективные из них. Для оценки издержек пути доступа используется статистическая информация из таблиц каталога DB2. Например, информация в таблицах каталога SYSTABLES и SYSTABLESPACE говорит, сколько строк и страниц данных в вашей таблице.
  • Физический дизайн базы данных
    Физический дизайн базы данных включает в себя дизайн таблиц, индексов, таблицы материализованных запросов и других физических объектов базы данных. Структура индексов оказывает основное влияние на выбор пути доступа. Как упоминалось в предыдущей статье, к одной таблице существует два вида доступа: сканирование табличной области (table space scan – TBSCAN) и сканирование индексов (index scan – IXSCAN). Обычно сканирование индексов – это самый эффективный способ доступа к данным, особенно когда таблица большая, а количество подходящих строк невелико.
  • SQL-выражение
    Само SQL-выражение также влияет на выбор пути доступа. Например, неправильно написанные предикаты могут не позволить оптимизатору использовать сканирование индекса, даже если этот индекс доступен. Кроме того, перед выбором пути доступа оптимизатор сначала выполняет ряд преобразований запроса, чтобы увеличить количество возможных путей доступа. Если SQL-выражение плохо написано, его преобразование оптимизатором затруднено, что уменьшает число доступных вариантов при выборе оптимального пути доступа.
  • Другие соображения по выбору пути доступа
    Кроме учета статистики каталога, физического дизайна базы данных и самого SQL-запроса, оптимизатор DB2 также учитывает модель центрального процессора, количество процессоров, размеры буферного пула, RID-пула и параметры других системных ресурсов. Например, даже при идентичной статистике каталога путь доступа может быть разным на разных системах, если они имеют разные размеры буферного пула.

Оптимизатор DB2 является комплексным и довольно мощным. Тогда зачем уделять внимание настройке запросов, если этим занимается оптимизатор? Тому есть две причины:

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

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

Советы разработчикам приложений:

  • Следуйте рекомендациям и стандартам написания SQL-выражений.
    При написании SQL-выражений следуйте рекомендациям и стандартам написания SQL-кода. Например, пишите индексируемые предикаты или предикаты stage 1 и избегайте написания запросов без предикатов соединения (т.н. декартово (Cartesian) соединение).
  • Корректно применяйте параметры связывания REOPT.
    Для SQL-выражений с переменными оптимизатор использует коэффициент фильтрации по умолчанию для определения наилучшего пути доступа во время связывания. В некоторых случаях такой путь доступа плохо работает во время исполнения, если выражение содержит хост-переменные, маркеры параметров или специальные регистры. Можно использовать параметры связывания REOPT для повторной оптимизации пути доступа либо во время связывания, либо во время исполнения.

Советы администраторам баз данных:

  • Собирайте достаточную и точную статистику.
    Недостаточная или неточная статистика приводит к неточным оценкам издержек возможных путей доступа и является самой частой причиной выбора неэффективных путей доступа. В то же время сбор и обновление всей статистики требуют слишком много ресурсов и являются нежелательными. Необходимо регулярно собирать надлежащую статистику при минимальном потреблении ресурсов, учитывая количество операций INSERT, UPDATE и DELETE, а также изменения в распределении данных.
  • Оптимизируйте дизайн индексов.
    Необходимо спроектировать индексы для поддержки эффективного доступа с использованием локальных предикатов и предикатов соединения. Также, возможно, потребуется спроектировать индексы для предотвращения сортировки данных и предоставления доступа только по индексу.
  • Настраивайте приложение в целом.
    Для обеспечения хорошей производительности приложения необходимо настроить его как целое. Настройка всего приложения путем анализа каждого отдельного оператора требует непомерных усилий. Кроме того, улучшение производительности одного оператора может привести к ухудшению производительности других операторов приложения. Поэтому важно выполнить настройку приложения в целом, называемую также настройкой рабочей нагрузки. В настоящей статье основное внимание уделяется настройке отдельных запросов; в следующей статье серии рассматриваемая здесь методология будет расширена, и вы более подробно познакомитесь с настройкой рабочей нагрузки.

В данной статье рассматривается методология выявления потенциальных проблем производительности запросов и пути их решения. Использование программы Optim Query Tuner упрощает этот процесс.

Методология настройки запросов

Обзор методологии настройки запросов

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

Взяв это положение за основу, необходимо из программы Query Tuner выполнить следующие действия для полной настройки запроса:

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

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

Листинг 1. Пример запроса, используемого в данной статье
SELECT CCUS.CUST_FIRST_NAME
     , CCUS.CUST_LAST_NAME
     , CINT.CUST_INTEREST_RANK
     , CILO.CUST_INTERST
FROM CUST_CUSTOMER AS CCUS
     , CUST_INTEREST_LOOKUP AS CILO
     , CUST_INTEREST AS CINT
WHERE ( CCUS.CUST_CITY = 'Singapore' 
        AND CCUS.CUST_PROV_STATE = 'Singapore'            
        AND CCUS.CUST_CODE IN ( 
                                SELECT COHE.CUST_CODE
                                FROM CUST_ORDER_HEADER AS COHE
                                     , CUST_ORDER_STATUS AS COST
                                WHERE ( COHE.CUST_ORDER_DATE 
                                            >='2009-01-01 00:00:00.001'            
                                        AND COST.CUST_ORDER_STATUS IN ( 'Shipped', 
                                            'Back-ordered', 'In-process' ) 
                                        AND COHE.CUST_ORDER_STATUS_CODE 
                                            = COST.CUST_ORDER_STATUS_CODE
                                      )
                              )
        AND CCUS.CUST_CODE = CINT.CUST_CODE
        AND CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE
      )
ORDER BY CCUS.CUST_LAST_NAME ASC 
         , CCUS.CUST_FIRST_NAME ASC 
         , CINT.CUST_INTEREST_RANK ASC

Форматирование проблемного запроса

До настройки проблемного запроса необходимо понять следующие его аспекты:

  • Семантика запроса. К каким таблицам обращается запрос? Какой тип предикатов используется для каждой таблицы? Какой тип предикатов используется для соединения таблиц?
  • Путь доступа. Как происходит обращение к таблицам? Сканируется ли вся таблица полностью или обращение происходит по индексу? Если по индексу, то какой или какие это индексы? Какова последовательность соединения и метод соединения?

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

Рисунок 2. Неформатированный запрос
Неформатированный запрос

Optim Query Tuner может отформатировать проблемный запрос, что обеспечит хорошую отправную точку для анализа. В отформатированном запросе ссылка на каждую таблицу, ссылка на каждый столбец в операторе SELECT и каждый предикат отображаются в отдельной строке. Отформатированный пример запроса показан на рисунке 3.

Рисунок 3. Отформатированный запрос
Отформатированный запрос

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

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

Если вернуться к отформатированному запросу, показанному на рисунке 3, можно увидеть следующее:

  • Для получения имени нужного клиента и области его интересов запрос обращается к трем таблицам: CUST_CUSTOMER, CUST_INTEREST и CUST_INTEREST_LOOKUP.
  • Соединение этих таблиц выполняется с использованием предикатов соединения equal (равенство).
  • Имеется три предиката для CUST_CUSTOMER. Первые два являются простыми предикатами равенства (CCUS.CUST_CITY = 'Singapore', CCUS.CUST_PROV_STATE = 'Singapore'). Третий предикат – это предикат IN-list, содержащий некоррелированный подзапрос:
    • Подзапрос обращается к таблицам CUST_ORDER_HEADER и CUST_ORDER_STATUS для получения кода нужного клиента, и две эти таблицы соединяются с использованием предиката равенства.
    • Имеется локальный предикат range для CUST_ORDER_HEADER и локальный предикат IN-list для CUST_ORDER_STATUS.
  • Для двух других таблиц (CUST_INTEREST и CUST_INTEREST_LOOKUP) локальных предикатов нет.
  • Результат сортируется по имени клиента и области интересов.

Query Tuner также облегчает определение места, где оптимизатор DB2 преобразовал запрос. Напомним, что преобразования являются настройками запроса, выполняемыми оптимизаторами DB2 для улучшения производительности запроса; например, он может добавить предикат транзитивного замыкания (transitive closure) для облегчения анализа последовательности соединений.

В качестве иллюстрации рассмотрим предикат A.CUSTNO BETWEEN ? AND ? AND C.CUSTNO = A.CUSTNO, на основании которого DB2 может сделать вывод, что предикат C.CUSTNO BETWEEN ? AND ? тоже должен быть верен, поэтому при преобразовании запроса DB2 может добавить этот предикат, что позволит учитывать еще один индекс.

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

Рисунок 4. Преобразованный запрос
Преобразованный запрос

Как можно заметить, оптимизатор создает виртуальную таблицу для обработки подзапроса IN-list. Кроме того, некоррелированный подзапрос преобразуется в коррелированный. Эта оптимизация, позволяющая DB2 оптимизировать запрос как единое целое, а не как несколько независимых блоков, появилась в DB2 for z/OS V9.1. При оптимизации запроса как единого целого DB2 может учитывать влияние одного блока на другой и может переставлять блоки запроса для определения оптимального пути запроса.

Аннотирование проблемного запроса

Рядом со ссылками на таблицы и предикатами форматированного SQL-запроса Query Tuner вставляет аннотации с релевантной статистикой каталога, такой как кардинальное число и прогнозируемое число подходящих записей, как показано на рисунке 5. Наличие этой информации помогает администраторам баз данных ускорить анализ и уменьшить время простоев в экстренных ситуациях.

Рисунок 5. Аннотированный проблемный запрос
Аннотированный проблемный запрос

Возле ссылки на каждую таблицу в запросе Query Tuner добавляет следующие аннотации:

  • CARDF (позиция 1 на рисунке 5): кардинальное число таблицы, указывающее общее количество строк в таблице. CUST_CUSTOMER – это самая большая таблица (31284), а CUST_INTEREST_LOOKUP – самая маленькая (338).
  • QUALIFIED_ROWS (позиция 2 на рисунке 5): число подходящих строк после применения локальных предикатов для таблицы. Хотя CUST_CUSTOMER и CUST_INTEREST имеют почти одинаковое кардинальное число, число подходящих строк для CUST_CUSTOMER лишь немного больше 1. Это означает, что для этой таблицы имеются очень селективные локальные предикаты. Противоположная ситуация для таблицы CUST_INTEREST, число подходящих строк в которой равно кардинальному числу (31255). Это указывает на то, что эта таблица не фильтруется. И это объяснимо, поскольку для таблицы CUST_CUSTOMER имеется два локальных предиката, а для таблицы CUST_INTEREST они отсутствуют.
  • NPAGESF (позиция 3 на рисунке 5): общее число страниц, на которых появляются строки данной таблицы.

Для каждого предиката в запросе Query Tuner добавляет аннотации со статистикой по столбцам и оценку издержек для данного предиката:

  • COLCARDF (позиция 4 на рисунке 5): кардинальное число столбца, указывающее оценочное число различных значений в столбце. Если предикат содержит более одного столбца, кардинальные числа разделяются обратной косой чертой (/) и идут в том же порядке, в котором столбцы появляются в предикате.
  • MAX_FREQ (позиция 5 на рисунке 5): максимальная частота всех возможных значений столбца. Частота для конкретного значения столбца является процентным соотношением строк, содержащих данное значение в столбце. Например, если для столбца имеется пять различных значений (COLCARDF=5) и данные распределяются равномерно, значение MAX_FREQ равно 20%, поскольку каждое из различных значений столбца заполняет 20% строк таблицы. Если кардинальное число столбца равно 5, а MAX_FREQ значительно больше 20%, это значит, что данные в таблице распределены по столбцу неравномерно. Другими словами, имеется "перекос" данных в столбце.
  • FF (позиция 6 на рисунке 5): коэффициент фильтрации для предиката. Коэффициент фильтрации – это число между 0 и 1, оценивающее долю строк в таблице, для которых предикат истинен. Коэффициент фильтрации указывает на селективность предиката. Чем больше его значение для предиката, тем раньше должен применяться предикат.

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

Query Tuner обеспечивает визуализацию процесса обработки запроса сервером данных. Эта визуализация называется схемой плана доступа. На этой схеме плана доступа можно увидеть выбранные оптимизатором варианты обработки запроса и обоснование такого выбора. Схема состоит из узлов, представляющих таблицы, индексы, операции и возвращаемые данные. Узлы упорядочиваются и соединяются связями, отображающими последовательность выполнения процесса. Схема читается слева направо, снизу вверх. Каждый узел аннотируется статистической информацией, оценкой издержек, информацией о селективности и всем прочим, что определяет поток плана доступа.

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

На рисунке 6 показана схема пути доступа, сгенерированная программой Query Tuner для нашего примера.

Рисунок 6. Схема плана доступа
Схема плана доступа

(Увеличенная версия рисунка 6.)

На схеме пути доступа, показанной на рисунке 6, можно увидеть следующее:

  • Запрос содержит два блока: QB1 (позиция 1 на рисунке 6) и QB2 (позиция 2 на рисунке 6). QB2 представляет подзапрос IN-list, а QB1 – основной подзапрос.
  • QB2 соединяется с таблицей CUST_CUSTOMER во внешнем блоке запроса QB1, т.е. подзапрос IN-list преобразуется в коррелированный подзапрос, хотя в исходном запросе использовался некоррелированный подзапрос.
  • План доступа для QB1 можно обобщить следующим образом: TBSCAN(CUST_CUSTOMER) NLJ ISCAN(CUST_INTEREST) NLJ ISCAN(CUST_INTEREST_LOOKUP).
    • 3 таблицы в QB1 соединяются с использованием соединения вложенными циклами (NLJ).
    • 3 таблицы в QB1 соединяются в следующей последовательности: CUST_CUSTOMER -> CUST_INTEREST -> CUST_INTEREST_LOOKUP.
    • Обращение к CUST_CUSTOMER выполняется через сканирование таблицы, тогда как обращение к CUST_INTEREST и CUST_INTEREST_LOOKUP выполняется через сканирование индекса.
  • План доступа для QB2 можно обобщить следующим образом: TBSCAN(CUST_ORDER_HEADER) NLJ ISCAN(CUST_ORDER_STATUS).
    • 2 таблицы в QB2 соединяются с использованием соединения вложенными циклами (NLJ).
    • 2 таблицы в QB2 соединяются в следующей последовательности: CUST_ORDER_HEADER -> CUST_ORDER_STATUS.
    • Обращение к CUST_ORDER_HEADER выполняется через сканирование таблицы, а к CUST_ORDER_STATUS – через сканирование индекса.

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

  • Доступ к внутренним таблицам CUST_INTEREST и CUST_INTEREST_LOOKUP осуществляется через сканирование индексов. Это довольно эффективный план доступа.
  • Доступ к ведущей таблице внешнего (CUST_CUSTOMER) и внутреннего (CUST_ORDER_HEADER) подзапроса осуществляется через сканирование таблиц, что может вызвать потенциальные проблемы:
    • Кардинальное число таблицы CUST_CUSTOMER примерно равно 30000. Однако, поскольку она является ведущей таблицей и доступ к ней будет осуществляться через сканирование таблицы только один раз, это может ухудшить производительность, хотя и не критично.
    • Внутренний подзапрос является коррелированным. В зависимости от числа подходящих записей, возвращаемых из внешней таблицы CUST_CUSTOMER, доступ к нему может осуществляться много раз. Аннотация (рисунок 5) или схема плана доступа (рисунок 6) показывают, что оценка числа подходящих строк для CUST_CUSTOMER равна 1; иными словами, оптимизатор считает, что CUST_ORDER_HEADER будет сканироваться только один раз. Учитывая, что кардинальное число таблицы равно примерно 50000, это не должно серьезно снизить производительность.

До сих пор мы были уверены, что CUST_CUSTOMER будет сканироваться только один раз, поскольку это ведущая таблица внешнего подзапроса. Однако мы не уверены, что таблица CUST_ORDER_HEADER будет сканироваться только один раз, поскольку число подходящих строк из CUST_CUSTOMER рассчитывается по существующим статистическим данным и предикатам:

  • Кардинальное число таблицы CUST_CUSTOMER равно 31284 (позиция 7 на рисунке 5).
  • Для таблицы используются два локальных предиката с очень высокой селективностью:
    • CCUS.CUST_CITY = 'Singapore', FF=0.00727 (позиция 8 на рисунке 5).
    • CCUS.CUST_PROV_STATE = 'Singapore', FF=0.004 (позиция 9 на рисунке 5).
  • Таким образом, число подходящих строк по оценке оптимизатора составляет 31284*0.00727*0.004 = 1.

А что если оценка числа подходящих строк из CUST_CUSTOMER некорректна? Например, что если два локальных предиката не настолько селективны, как спрогнозировал оптимизатор? Это может привести к серьезным проблемам с производительностью, поскольку доступ к CUST_ORDER_HEADER может осуществляться много раз.

Одним из способов проверки подозрительных узких мест производительности является анализ статистики времени исполнения запроса, которую можно получить, включив трассировку производительности по IFCID 318. Другим вариантом является использование Query Tuner для выборки выражений из кэш-памяти выражений и просмотра информации времени исполнения (см. рисунок 7).

Рисунок 7. Статистика времени исполнения
Статистика времени исполнения

(Увеличенная версия рисунка 7.)

Выделенная строка (заканчивающаяся "B" на рисунке 7) отображает информацию времени исполнения для нашего примера запроса. Как можно заметить, запрос выполнялся три раза, а среднее время выполнения составляет примерно 307 секунд, что очень много. Общее число сканирований таблицы STAT_RSCN равно 1764, т.е. более 580 (1764/3) сканирований таблицы за одно выполнение запроса. Это намного больше, чем оценка по схеме плана доступа, составляющая примерно 2 (один для CUST_CUSTOMER и один для CUST_ORDER_HEADER). Это еще больше убеждает нас в том, что прогнозируемое число подходящих строк из CUST_CUSTOMER далеко от реальности.

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

Листинг 2. Запрос для подсчета строк
SELECT COUNT(*)
FROM CUST_CUSTOMER AS CCUS
WHERE CCUS.CUST_CITY = 'Singapore' AND CCUS.CUST_PROV_STATE = 'Singapore'

Результат выполнения этого запроса показывает, что из таблицы CUST_CUSTOMER выбирается около 588 подходящих строк. Другими словами, оптимизатор переоценил селективность локальных предикатов таблицы. В следующих разделах данной статьи мы проанализируем проблемный запрос с точки зрения статистики, предикатов и индексов, а затем покажем, почему происходит переоценка и как ее не допустить.

Анализ статистики

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

С другой стороны, некоторая статистическая информация (например, частота столбца), никогда не собирается. Например, на рисунке 8 значение MAX_FREQ для столбца CUST_CITY в предикате CCUS.CUST_CITY = 'Singapore' отсутствует. Отсутствующая статистика может вызвать переоценку или недооценку оптимизатором селективности предиката, и, в конечном счете, привести к выбору неэффективного пути доступа.

Рисунок 8. Анализ статистики
Анализ статистики

Такая проблема может возникнуть и тогда, когда статистика собиралась ранее, но давно не обновлялась и устарела. Это особенно существенно в случае, когда после последнего сбора статистики данные существенно изменились. Для узлов table или index на схеме плана доступа имеется атрибут RUNSTATS TIMESTAMP, указывающий время последнего сбора статистики. Отметим, что в настройках Query Tuner можно задать пороговое значение, определяющее, насколько старой может быть статистическая информация до потери ею актуальности. По умолчанию статистика идентифицируется как устаревшая, если ее возраст превышает один год.

Если разная статистическая информация собирается в разное время, это может привести к несогласованности статистик в DB2 for z/OS. Например, если по определенной таблице собирается статистика, а затем в таблицу вставляется значительное количество строк, сбор статистики по индексам может привести к тому, что кардинальное число индексированного столбца будет больше кардинального числа таблицы. Несогласованная статистика может также привести к выбору оптимизатором плохого пути доступа.

Анализ предикатов

Между тем, используя аннотации (см. рисунок 9), мы также можем проанализировать предикаты и определить наличие потенциальных проблем.

Рисунок 9. Анализ предикатов
Анализ предикатов

(Увеличенная версия рисунка 9.)

Согласно информации в аннотациях, ссылка на таблицу CUST_CUSTOMER (CCUS) имеет два локальных предиката, которые являются очень селективными и имеют коэффициент фильтрации 0.00727 и 0.004, соответственно.

Поскольку статистика по частоте отсутствует (MAX_FREQ=(missing)), коэффициент фильтрации каждого предиката рассчитывается в предположении, что данные равномерно распределены по столбцам. Например, кардинальное число столбца CUST_CITY равно 1376, т.е. коэффициент фильтрации предиката CCUS.CUST_CITY='Singapore' составляет 1/1376=0.00727.

Насколько можно доверять оценке селективности предиката? Если в столбцах имеется перекос данных (например, в большинстве данных название города равно Singapore), текущая селективность может быть значительно переоцененной. Для получения более точной оценки необходимо собрать статистику по частоте для столбцов CUST_CITY и CUST_PROV_STATE таблицы CUST_CUSTOMER.

Еще одной потенциальной проблемой является локальный предикат в ссылке на таблицу CUST_ORDER_HEADER (COHE) во внутреннем подзапросе (см. рисунок 9). Поскольку это предикат диапазона, а статистика по частоте и гистограмме отсутствует, коэффициент фильтрации рассчитывается по базовой статистической информации HIGH2KEY и LOW2KEY. Оценка может быть некорректной, если имеется перекос данных в столбце. Для получения более точной оценки необходимо собрать статистику по гистограмме для столбца CUST_ORDER_DATE таблицы CUST_ORDER_STATUS_CODE.

Теперь, после анализа селективности локальных предикатов, рассмотрим селективность таблицы.

Для трех таблиц внешнего подзапроса отсутствует локальный предикат по CUST_INTEREST_LOOKUP (CILO) и CUST_INTEREST (CINT); следовательно, если какая-либо из этих таблиц обрабатывается в качестве ведущей, селективность таблицы равна нулю, что очень плохо.

Если CUST_CUSTOMER (CCUS) обрабатывается как ведущая таблица, при наличии двух локальных предикатов селективность таблицы CUST_CUSTOMER равна 0.00727*0.004, а число подходящих строк – примерно 1 (31284*0.00727*0.004). Такая селективность кажется хорошей, но данное значение вычисляется в предположении, что два столбца (CUST_CITY и CUST_PROV_STATE) не коррелируются между собой, что неверно. Поскольку Сингапур – это один из городов государства Сингапур, для получения более точной оценки селективности таблицы необходимо собрать статистику по кардинальному числу и частоте для группы столбцов (CUST_CITY, CUST_PROV_STATE).

После обработки CUST_CUSTOMER предикат соединения CCUS.CUST_CODE = CINT.CUST_CODE может рассматриваться как локальный предикат таблицы CUST_INTEREST (CINT). Учитывая, что кардинальное число таблицы CUST_INTEREST составляет примерно 31255, можно заметить, что для каждой подходящей строки из CUST_CUSTOMER имеется только одна совпадающая запись в CUST_INTEREST. Таким образом, выборка из таблицы CUST_INTEREST может быть очень селективной благодаря предикату соединения.

После обработки CUST_CUSTOMER и CUST_INTEREST предикат соединения CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE может рассматриваться как локальный предикат таблицы CUST_INTEREST_LOOKUP (CILO). Учитывая, что кардинальное число таблицы CUST_INTEREST_LOOKUP составляет примерно 338, можно заметить, что для каждой подходящей строки из CUST_INTEREST имеется приблизительно 13 (338/26) совпадающих записей в CUST_INTEREST_LOOKUP, что указывает на достаточно хороший уровень селективности.

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

Рисунок 10. Схема плана доступа после RUNSTATS
Схема плана доступа после RUNSTATS

В этой новой схеме плана доступа можно увидеть следующее:

  • Поскольку была собрана статистика по группе столбцов (CUST_CITY и CUST_PROV_STATE), оптимизатор DB2 теперь знает, что корректное число подходящих строк после локальной фильтрации равно 590, а не 1.
  • Последовательность соединения внутреннего подзапроса изменилась (оригинальная последовательность – CUST_ORDER_HEADER-> CUST_ORDER_STATUS, текущая последовательность – CUST_ORDER_STATUS -> CUST_ORDER_HEADER), поскольку была собрана статистика по гистограмме для столбца CUST_ORDER_DATE таблицы CUST_ORDER_HEADER.
  • С корректным числом подходящих строк из CUST_CUSTOMER внутренний подзапрос обрабатывается как некоррелированный подзапрос, который материализуется в рабочий файл, и затем доступ к нему осуществляется посредством сканирования индекса в памяти.

Строка, заканчивающаяся буквой "S" на рисунке 11, показывает информацию времени исполнения примера запроса после RUNSTATS. Среднее время выполнения – примерно 2.8 секунды; это существенное улучшение производительности по сравнению с предыдущим вариантом (до RUNSTATS – около 307 секунд).

Рисунок 11. Статистика времени исполнения после RUNSTATS
Статистика времени исполнения после RUNSTATS

(Увеличенная версия рисунка 11.)

Также на рисунке 11 показано, что при каждом выполнении запроса все еще выполняется два сканирования таблицы. На схеме плана доступа, изображенной на рисунке 10, можно легко обнаружить, что два сканирования таблицы выполняются из CUST_CUSTOMER и CUST_ORDER_HEADER соответственно. В следующем разделе мы расскажем, как это исправить посредством анализа индексов.

Анализ индексов

Программа Optim Query Tuner предоставляет отчеты (в текстовом и в HTML-формате), содержащие информацию о таблицах, индексах и предикатах, использующихся в конкретных SQL-выражениях. Отчет по индексам для нашего примера запроса показан на рисунке 12. В этом отчете описываются существующие индексы используемых таблиц.

Рисунок 12. Отчет по индексам
Отчет по индексам

Для удобства существующие индексы сведены в таблицу 1.

Таблица 1. Существующие индексы
Имя таблицыИмя индексаИндексный столбец
CUST_CUSTOMERBQT01_CUST_CUSTOMERCUST_AGE
BQT02_CUST_CUSTOMERCUST_PROV_STATE_CODE
BQT03_CUST_CUSTOMER

GENDER_CODE,

MARITAL_STATUS_CODE,

CUST_AGE

IDX_CUST_CUSTOMERCUST_CODE
CUST_INTERESTCUST_INTEREST_PK

CUST_CODE,

CUST_INTEREST_CODE

CUST_INTEREST_LOOKUPCUST_INT_LOOKUP_PK

CUST_INTEREST_CODE,

CUST_INTEREST_LANGUAGE

CUST_ORDER_HEADERBQT01_CUST_ORDER_HEADERCRDT_METHOD_CODE
CUST_ORDER_HEADER_PKCUST_ORDER_NUMBER
CUST_ORDER_STATUSBQT01_CUST_ORDER_STATUS

CUST_ORDER_STATUS,

CUST_ORDER_STATUS_LANGUAGE

CUST_ORDER_STAT_PK

CUST_ORDER_STATUS_CODE,

CUST_ORDER_STATUS_LANGUAGE

Рассмотрим предикаты для таблицы CUST_CUSTOMER, извлеченные из отформатированного запроса, показанного на рисунке 3:

  • CCUS.CUST_CITY = 'Singapore'
  • CCUS.CUST_PROV_STATE = 'Singapore'
  • CCUS.CUST_CODE = CINT.CUST_CODE

Рассматривая существующие индексы в таблице 1 и указанные выше предикаты, можно сделать вывод, что лишь предикат соединения (CCUST.CUST_CODE = CINT.CUST_CODE) может извлечь пользу из существующего индекса IDX_CUST_CUSTOMER. Ни один из существующих индексов не приносит пользы двум локальным предикатам.

Для поддержки обоих локальных предикатов и предиката соединения с использованием сканирования индексов необходимо создать индекс по столбцам CUST_CITY, CUST_PROV_STATE и CUST_CODE.

Таблицы CUST_INTEREST и CUST_INTEREST_LOOKUP имеют предикат соединения, и существующий индекс может поддерживать сканирование по индексу, как показано ниже.

  • CCUS.CUST_CODE = CINT.CUST_CODE
  • CINT.CUST_INTEREST_CODE = CILO.CUST_INTEREST_CODE

Для таблицы CUST_ORDER_HEADER оба существующих индекса не поддерживают локальные предикаты и предикат соединения, как показано ниже.

  • COHE.CUST_ORDER_DATE >= '2009-01-01 00:00:00.001'
  • COHE.CUST_ORDER_STATUS_CODE = COST.CUST_ORDER_STATUS_CODE

Чтобы использовать сканирование индекса как для локального предиката, так и для предиката соединения, необходимо создать индекс по столбцам CUST_ORDER_DATE и CUST_ORDER_STATUS_CODE. Также для улучшения производительности при помощи доступа только через индекс можно добавить CUST_CODE в качестве ключевого столбца. Если CUST_CODE является первым ключевым столбцом, оптимизатор DB2 может рассмотреть глобальную оптимизацию запроса для доступа к подзапросу как к коррелированному подзапросу, учитывая, что эта таблица является частью подзапроса IN-list.

Для таблицы CUST_ORDER_STATUS первый индекс BQT01_CUST_ORDER_STATUS поддерживает индексный доступ для локального предиката, но не может поддерживать предикат соединения. Второй индекс CUST_ORDER_STAT_PK может поддерживать индексный доступ для предиката соединения, но не может поддерживать для локального предиката:

  • COST.CUST_ORDER_STATUS IN ( 'Shipped', 'Back-ordered', 'In-process' )
  • COHE.CUST_ORDER_STATUS_CODE = COST.CUST_ORDER_STATUS_CODE

Для улучшения производительности таблицы CUST_ORDER_STATUS необходимо создать индекс по обоим столбцам CUST_ORDER_STATUS_CODE и CUST_ORDER_STATUS.

Основываясь на проведенном анализе индексов, для повышения производительности мы предлагаем три следующих индекса:

Таблица 2. Новые индексы
Имя таблицыИмя индексаИндексный столбец
CUST_CUSTOMERCUST_CUSTOMER_NEW_INDEX

CUST_CITY, CUST_PROV_STATE,

CUST_CODE

CUST_ORDER_HEADERCUST_ORDER_HEADER_NEW_IDX

CUST_CODE, CUST_ORDER_DATE,

CUST_ORDER_STATUS_CODE

CUST_ORDER_STATUSCUST_ORDER_STATUS_NEW_IDX

CUST_ORDER_STATUS_CODE,

CUST_ORDER_STATUS

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

Рисунок 13. Схема плана доступа после создания индексов
Схема плана доступа после создания индексов

(Увеличенная версия рисунка 13.)

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

Строка, заканчивающаяся буквой "I" на рисунке 14, отображает информацию времени исполнения примера запроса после создания новых индексов. Среднее время исполнения, составляющее примерно 0.246 секунды, вновь показывает значительное улучшение производительности по сравнению с производительностью после RUNSTATS (примерно 2.8 секунды).

Рисунок 14. Статистика времени исполнения после создания индексов
Статистика времени исполнения после создания индексов

(Увеличенная версия рисунка 14.)

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

Таблица 3. Сравнение производительности
Задача настройкиСчетчик выполненияSTAT_GPAGSTAT_ELAPSTAT_CPUAVG_STAT_ELAPAVG_STAT_CPU
До настройки323486129922.197815145.920307307.39927248.640102
Сбор статистики4837611.2001860.880022.8000470.220007
Создание индекса460840.9833570.0477040.2458390.011926

Ускорение настройки запросов при помощи Optim Query Tuner

До сих пор мы рассматривали выполнение анализа и настройки проблемного запроса с использованием возможностей Optim Query Tuner, таких как аннотация запросов, схема плана доступа и т.д. Хотя эта программа интуитивно понятна и удобна для пользователя, для реализации всех мощных возможностей, предлагаемых программой, все равно необходимы знания основ работы оптимизатора DB2 и путей доступа. Конечно, необходимо также потратить некоторое время на анализ.

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

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

Совет. Обычно следует запускать советчиков в следующем порядке:

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

В следующих разделах более подробно рассматривается работа советчиков.

Выполнение советчика по статистике для улучшения качества и полноты статистики.

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

В DB2 команда RUNSTATS TABLE ALL INDEX ALL собирает всю статистическую информацию, большая часть которой не является необходимой для повышения производительности запроса. В то же время утилита не собирает некоторую важную информацию, например, статистику по нескольким столбцам и распределенную статистику. Часто между столбцами существуют корреляции данных. Например, в нашем примере запроса имеется четкая корреляция между столбцами CUST_CITY и CUST_PROV_STATE. Сбора статистики по отдельным столбцам может оказаться недостаточно для получения необходимой информации, поэтому нужно собрать статистику по группе столбцов.

Советчик по статистике Query Tuner облегчает работу, предоставляя команде RUNSTATS рекомендации по сбору необходимой статистики путем идентификации следующих проблем состояния статистики:

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

Советчик по статистике оценивает также относительную важность совета и предоставляет два типа рекомендаций:

  • Repair (восстановить). Рекомендации этого типа указывают на отсутствие важной статистической информации или на ее противоречивость. Рекомендация включает в себя команду RUNSTATS, которую можно выполнить для восстановления релевантной статистики.
  • Complete (выполнить). Рекомендации этого типа охватывают тип Repair и другую статистическую информацию, которую можно обновить. Рекомендация предоставляет задание RUNSTATS, которое вы, возможно, захотите включить в периодический цикл обслуживания для поддержки целостности статистической информации.

При внимательном рассмотрении конкретной рекомендации (см. рисунок 15) вы увидите, что советчик по статистике генерирует команду RUNSTATS, которую можно использовать для сбора или восстановления статистики. Ее можно сохранить для последующего применения или запустить непосредственно из клиента Query Tuner при наличии соответствующих прав. Отметим, что для выполнения рекомендаций RUNSTATS из Query Tuner на стороне сервера должна работать и быть доступной хранимая процедура SYSPROC.DSNUTILU.

Вместе с рекомендацией предоставляется объяснение. На рисунке 15 видно, что советчик обнаружил несколько случаев противоречивой или отсутствующей статистики, а также предоставил объяснение, почему он считает ее противоречивой.

Рисунок 15. Рекомендации советчика по статистике
Рекомендации советчика по статистике

Вы уже увидели, что улучшение качества статистики означает предоставление оптимизатору DB2 точных данных, на которых он основывает решения по оптимизации плана доступа и, следовательно, по улучшению производительности. Однако есть и другое преимущество. Собирая только необходимую статистику, можно избежать накопления нерелевантной статистики, перегружающей CPU и создающей лишнюю рабочую нагрузку во время выполнения действий по обслуживанию. Другими словами, советчик по статистике может помочь улучшить качество статистики, а также повысить эффективность сбора статистических данных.

Выполнение советчика по запросам для улучшения дизайна запроса

Перед выбором пути доступа оптимизатор DB2 преобразовывает SQL-выражения в семантически идентичные формы, используя, например, проталкивание предикатов (predicate pushdown) или транзитивное замыкание предикатов. В результате это может улучшить возможные пути доступа. Query Tuner помогает тому, кто пишет или настраивает запросы, идентифицировать ошибки, давая советы по ограничению запроса, увеличению используемости индексов, уменьшению операций чтения данных. Советчик по запросам ищет в запросе возможность:

  • Минимизировать количество индексных страниц и строк данных, которые нужно прочитать. Например, свести к минимуму операции чтения строк при помощи предикатов в запросе, который может определить необходимые строки только из самого индекса.
  • Минимизировать операции сортировки. Например, определить, нужны ли в запросе операторы ORDER BY и GROUP BY или можно обойтись доступом к индексу.

Советчик по запросам проверяет:

  • Отсутствующие предикаты соединения, но только если определен внешний ключ (foreign key).
  • Предикаты Stage 2, которые могут улучшить производительность в случае преобразования их в Stage 1 или в индексируемые. Ссылки на более подробную информацию о предикатах Stage 1 и Stage 2 в DB2 for z/OS Information Center приведены в разделе Ресурсы.
  • Предикаты Stage 1, которые могут улучшить производительность в случае преобразования их в индексируемые.
  • Дополнительные локальные предикаты, не предоставляемые DB2 автоматически, которые могут обеспечить транзитивное замыкание предикатов.
  • Предикаты, не предоставляемые DB2 автоматически, которые проталкиваются в выражение вложенной таблицы или материализованное представление без изменения результата.
  • Дополнительные предикаты, добавляемые в комплексный оператор WHERE, содержащий OR, AND и круглые скобки (). Это может улучшить производительность без изменения результата.
  • Использование выражения SELECT *, которое можно заменить списком конкретных столбцов.

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

Рисунок 16. Рекомендации советчика по запросам
Рекомендации советчика по запросам

Выполнение советчика по индексам для улучшения эффективности запроса

Query Tuner предоставляет также советчика по индексам. Он анализирует запрос и рекомендует использование дополнительных индексов, которые могут быть полезными. Этот советчик может рекомендовать индексы по следующим причинам:

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

На рисунке 17 представлены рекомендации по индексам для нашего примера запроса вместе с прогнозом улучшения производительности и требованиями к пространству DASD. Советчик также генерирует DDL-выражения, необходимые для создания индексов, и предоставляет возможность сразу же запустить их при наличии соответствующих полномочий или сохранить для последующего использования.

Рисунок 17. Рекомендации советчика по индексам
Рисунок 17. Рекомендации советчика по индексам

Процесс создания DDL-выражений и рекомендаций можно настроить под свои требования. Например, можно указать максимальное число столбцов, которое может быть частью ключа индекса, и изменить идентификатор Creator ID, используемый при генерировании DDL для индекса. Также можно указать значения FREEPAGE, PCTFREE и CLUSTERRATIO для новых индексов и определить, может ли размер страницы-листа индекса превышать 4 КБ.

Перед развертыванием можно выполнить тестирование рекомендуемых индексов вместе с индексами, определяемыми при помощи анализа "что-если". Это позволяет применить различные ограничения для советчика по индексам и посмотреть, как эти ограничения меняют рекомендации. Анализ "что-если" использует виртуальный индекс для оценки эффективности индексов. Такая методика позволяет выбирать те индексы, которые обеспечат нужную производительность.

Заключение

В данной статье представлена методология настройки единичных запросов и приведен пример запроса для детального пошагового объяснения этой методологии. Это помогает лучше понять проблемный запрос при помощи визуальных средств, таких как форматирование, аннотирование, отчеты и схема плана доступа. Вы познакомились с анализом и настройкой проблемного запроса с точки зрения статистики, предикатов и индексов. Также вы узнали способы ускорения настройки запросов при помощи советчиков Query Tuner.

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


Загрузка

ОписаниеИмяРазмер
Файл примера проекта для данной статьиsamplequerytuningproject.zip338 КБ

Ресурсы

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=801642
ArticleTitle=Настройка SQL-запросов при помощи Optim Query Tuner: Часть 2. Настройка отдельных запросов
publish-date=03122012