Подготовка к оптимизации в DB2

в очередной раз доказывая, что недостаток знания опасен, стоимостные оптимизаторы часто выдают ужасные результаты, если им не предоставлена статистика по всем таблицам и индексам, участвующим в запросе. Таким образом, вам обязательно нужно поддерживать достоверную статистику по таблицам и индексам и, помимо этого, заново генерировать статистику в случае, если объем таблицы существенно изменяется или перестраиваются таблицы или индексы. Самый безопасный способ - периодически заново генерировать статистику, используя для этого отрезки времени, когда нагрузка невелика, например, это может быть ночь или выходные. Из строки приглашения Unix отредактируйте файл runstatsjchema. sql и введите следующие команды, заменяя параметр <Имя схемы> именем схемы, содержащей объекты, для которых вы хотите собрать статистику: -- File called runstats schema.sql

SELECT RUNSTATS ON TABLE <Ит схемы>. TABNAME AND INDEXES ALL;

FROM SYSCAT.TABLES

WHERE TABSCHEMA = <Имя схемы>;

Чтобы выполнить этот сценарий, зарегистрируйтесь в утилите db2, выйдите в командную строку оболочки командой quit; и выполните две команды:

db2 +р -t < runstats schema.sql > tmp runstats.sql

grep RUNSTATS tmp runstats.sql db2 +p -t > tmp anal.out

Можно настроить расписание, чтобы эти две команды выполнялись автоматически. Проверяйте содержимое файла tmp anal. out на тот случай, если при проведении анализа произойдет ошибка.

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

Например, в таблице Orders может быть столбец Status Code с тремя возможными значениями: CL для закрытых (например, выполненных) заказов, СА для отмененных заказов и ОР для открытых заказов. Таким образом, большинство заказов будет выполнено. Поэтому, если приложение проработало несколько месяцев, вы ожидаете, что CL встречается в большой и постоянно увеличивающейся части заказов. Значительная часть заказов будет в итоге отменена, поэтому значение СА также будет встречаться в большом списке заказов. Однако если бизнес идет хорошо и заказы продолжают поступать, то количество открытых заказов будет оставаться умеренным и стабильным, даже если данные будут накапливаться годами. Достаточно скоро условие Status Code= ОР станет достаточно селективным, чтобы оправдать индексированный доступ, если, конечно, у вас есть индекс с таким ведущим столбцом. Важно, чтобы оптимизатор мог понять этот факт, причем лучше всего, если для этого не потребуется ручная настройка. Чтобы СВО мог понять, когда столбец становится селективным, необходимо выполнение двух условий.

В SQL должно быть указано определенное селективное значение, а не параметр. Использовать параметры удобно, так как SQL-код становится более аб-

Уровни 4,6 и 8 недоступны - возможно, по каким-либо историческим причинам, хотя я никогда не встречал упоминания об этом в документации.

страктным и его можно разделять между процессами. Однако необходимость явно указывать особенно селективные значения - исключение из этого правила. Если вы укажете Status Code=? вместо Status Code=OP, то во время разбора кода СВО не сможет узнать о селективности условия, поскольку еще неизвестно, будет ли параметру ? присвоено распространенное или редко встречающееся значение StatusCode. К счастью, в таких случаях обычная причина использования параметров не имеет значения. Так как у этих специальных кодов есть особенный, важный для бизнеса смысл, маловероятно, что когда-либо потребуется заменить указанное селективное значение каким-нибудь другим.

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

Если вы запрашиваете специальную статистику по распределению, DB2 также сохраняет ее. Например, чтобы создать статистику по распределению данных, имея индекс с именем Order Stts Code и схему, владельцем которой является Appl Prod, используйте следующую команду:

RUNSTATS ON TABLE ApplProd.Orders WITH DISTRIBUTION FOR INDEX ApplProd.Order Stts Ccde:

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

Выбор уровня оптимизации

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

В DB2 предусмотрено семь уровней оптимизации: 0,1,2,3,5,7 и 9. Обычно по умолчанию устанавливается уровень 5, хотя администратор базы данных может переопределить это значение. Мне никогда не требовался уровень оптимизации

выше 5; уровни 7 и 9 предназначены скорее для экзотических преобразований запросов, необходимость в которых возникает редко. Однако я часто получал отличные результаты на низшем уровне оптимизации, уровне О, в то время как уровень 5 выдавал плохой план. Перед выполнением запроса (или проверкой плана выполнения) установите уровень О при помоши следующего оператора SQL: SET CURRENT QUERY OPTIMIZATION 0:

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

Изменение запроса

в основном при ручной настройке в DB2 применяются изменения в SQL, описанные ранее в разделе Универсальные техники управления планами . Однако одна техника заслуживает отдельного упоминания, поскольку ее использование в DB2 намного эффективнее, чем в Oracle или SQL Server. DB2 хранит записи индекса даже для значений null индексированных столбцов и обрабатывает null так же, как и любое другое индексированное значение.

Если в DB2 не хватает специальной статистики по распределению (см. Подготовка к оптимизации в DB2 ), она считает, что селективность условия Индексиро-ванный столбец IS NULL в точности равна селективности Индексированный столбец = 198487573 или для любого другого не равного null значения. Поэтому старые версии DB2 часто работают на выглядящем селективным условии IS NULL по индексированным столбцам. Иногда все получается прекрасно. Но по моему опыту, селективность условия IS NULL очень редко приближается к селективности среднего не равного нулю значения, и индексированный доступ по условиям IS NULL - это практически всегда ошибка.

Таким образом, если в запросе DB2 вы находите условие IS NULL для индексированного столбца, нужно предотвратить использование индекса. Простейшее эквивалентное условие, запрещающее использовать индекс, - это COALESCE( Indexed Col umn. Indexed Column) IS NULL. Этот вариант полностью эквивалентен исходному условию Indexed Col umn IS NULL, но функция COALESCEC) не позволяет использовать индекс.

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

Установка внутренних соединений на первое место в разделе FROM

Эта техника заключается в простом перечислении внутренних соединений на первых местах в разделе FROM. Он никогда не повредит запросу, и в старых версиях DB2 при помощи такой техники я получал существенно улучшенные планы вьшолнения.

Предотвращение одновременного разбора слишком большого количества внешних соединений

в старых версиях DB2 разбор запросов, содержащих более 12 внешних соединений, может занять минуты, и даже после может быть выдана ошибка. К счастью,