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

Стоимостные оптимизаторы обычно предполагают, что данные распределены единообразно. Например, если статистика оптимизатора показывает таблицу с 1 ООО ООО строк и 50 ООО различных значений для некоторого индексированного внешнего ключа, база данных будет проводить оптимизацию, предполагая, что каждое значение ключа будет встречаться в точности в 20 строках. Для большинства индексированных столбцов, таких как внешние ключи, такое предположение о единообразном распределении данных работает хорошо. Однако в некоторых столбцах распределение данных достаточно несимметрично, например если в них хранится состояние, код или тип, или же внешние ключи для статуса или типа таблиц. Например, рассмотрим такой запрос: SELECT ... FROM Orders WHERE Status Code = OP

В 1 ООО ООО-строчной таблице Orders может быть лишь три или четыре разных значения Status Code, но если DP означает открытый заказ, еще не выполненный или отмененный, то это условие становится намного более селективным, чем оптимизатор мог бы предположить, основываясь только на количестве различных значений. Если для этого столбца существует индекс, оптимизатор может никогда не задействовать его, поскольку будет знать лишь о небольшом количестве различных индексированных значений. Однако в некоторых базах данных можно сгенерировать дополнительную статистику, которая позволит базе данных узнать не только количество различных значений, но также их распределение. Генерация подобной статистики является необходимым шагом, когда в таблице присутствуют такие асимметричные распределения данных.

Обман стоимостного оптимизатора плохими данными

Последняя техника опасна, и я рекомендую использовать ее только в качестве последнего средства. Иногда вам нужно имитировать большую базу данных на небольшой, тестовой базе данных. Если вы можете экстраполировать (или, лучше, измерить в реальной базе данных) статистику скорости вьшолнения запросов для большой базы данных, то сумеете вручную изменить таблицы словаря данных, в которых хранится статистика для оптимизатора, чтобы обмануть оптимизатор и заставить его думать, что он работает с крупной базой данных. Статистика небольшой базы данных будет убеждать сервер, что он работает с большими таблицами, содержащими большое количество различных значений для многих индексов. Это удобный способ проверки планов вьтолнения, которые будут применяться к промышленным объемам данных, когда у вас есть лишь тестовая база данных с игрушечными объемами. Для таких игрушечных баз данных в подобном подходе нет никакого риска. В промышленных базах данных оптимизатор иногда будет делать лучший выбор именно на фальшивых данных; обычно, если данные преувеличи-

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

Попробуйте мысленно перевернуть логику оптимизатора. Спросите: Что я должен знать о таблицах и индексах этого запроса, чтобы посчитать альтернативный план (альтернативу, которую хотите получить вы, человеческий оптимизатор) намного более привлекательным? Если предоставить оптимизатору неверную статистику, то нетрудно будет обмануть его и заставить сделать то, что нужно вам, а не то, что он выбрал бы самостоятельно. Но в промышленных системах это опасно по нескольким причинам.

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

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

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

Мне никогда не требовалось применять этот способ для получения адекватного оптимизированного плана в Oracle, SQL Server или DB2, и я рекомендую вам также избегать его.

Управление планами в Oracle

в текущее время Oracle предлагает два совершенно разных оптимизатора: синтаксический (rule-based optimizer, RBO) и стоимостный (cost-based optimizer, СВО), с различными методами настройки.

RBO - это исходный автоматизированный оптимизатор Oracle, существовавший еще в Oracle Version 6 и более ранних версиях. Под определением <синтпакси-ческит Oracle подразумевает, что оптимизатор использует только фиксированные свойства таблиц, индексов и SQL для определения оптимального плана вьшолнения при помощи набора простых правил большого пальца (или эвристических), встроенных в автоматизированный оптимизатор. RBO не учитывает сведения о размерах таблиц и индексов или о распределении данных в этих объектах. Он использует данные о фиксированных свойствах индексов - уникальны ли они, какие столбцы они охватывают, в каком порядке и насколько хорошо они соответствуют выглядящим наиболее селективными условиям фильтров и соединениям в SQL. По мере того как таблицы растут и распределение данных изменяется, RBO все также выдает один и тот же план, если только вы не изменяете индексы (например, из уникальных делаете неуникальные) или структуру таблиц (например, из обычной таблицы делаете таблицу с разбиениями). Однако в будущем (возможно, даже в Oracle Database lOg) Oracle прекратит поддерживать синтаксический оптимизатор и стоимостный оптимизатор станет вашим единственным выбором.

Начиная с Oracle?, RBO стал еще более стабильным, чем раньше, так как Oracle решила заморозить код RBO, за исключением редких и небольших изменений, необходимых для получения функционально верных (в противоположность обязательно оптимальным) результатов. Таким образом, план выполнения, являющийся правильным в RBO сегодня, скорее всего, останется верным, пока Oracle полностью не прекратит поддержку RBO. Это привлекательно с точки зрения стабильности, хотя оборотной стороной этой стабильности является то, что планы вьшолнения также никогда не станут лучше.

Планы вьшолнения в RBO никогда не изменяются, подстраиваясь к изменениям в распределении данных, и зачастую это становится важным аргументом в решении перейти к СВО. Однако мой опыт говорит о том, что изменения в распределении данных - это самый слабый из аргументов за СВО. Я занимаюсь этим вопросом уже более 10 лет, и мне еще предстоит найти такой случай, в котором было бы важно использовать различные планы вьшолнения для различных распределений реальных данных с одним и тем же SQL-запросом.

ПРИМЕЧАНИЕ -

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

Еще ОДИН аргумент в пользу СВО - то, что он может выдавать параллельные планы вьшолнения, планы, которые могут заставить несколько процессоров одновременно обрабатывать один SQL-запрос. Я не считаю этот аргумент столь уж серьезным, поскольку в реальной работе мне еще не встречался случай, когда для достижения адекватной производительности оптимальному SQL-запросу с правильным дизайном базы данных требовалось параллельное выполнение. Можно предположить, что такие запросы могут выполняться в хранилищах данных, с которыми я работал не слишком много. Однако практически во всех случаях, в которых, казалось бы, параллельные планы вьшолнения демонстрируют блистательную скорость, на самом деле скрывается ошибка в дизайне базы данных, индексах или дизайне приложения, последствия которой компенсируются аппаратной мощностью. Само по себе это не так уж и страшно - дополнительная аппаратная мощность может обойтись дешевле, чем исправление приложения. Однако параллельные планы обычно используются для больших пакетных процессов, которые отнимают ресурсы у оперативных процессов, более важных для конечных пользователей. То есть параллельные планы вьшолнения часто крадут необходимые ресурсы у других, более важных приложений.

Самые сильные аргументы против использования RBO.

Он станет недоступным в какой-либо очередной версии, возможно даже в Oracle Database lOg, и вы никогда не сможете использовать более старую версию сервера

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

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

RBO не может использовать преимущества функций, созданных после появления СВО в Oracle?, и в большинстве случаев RBO просто перебрасывает за-