Как работает оптимизатор oracle

Обновлено: 02.07.2024

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

На сегодня существует 3 режима оптимизации (по большому счету 2, поскольку 3-ий выбирает из этих двух):
1) продукционный оптимизатор Rule: использует продукционную оптимизацию, формально говоря продукционный оптимизатор, был разработан первым, появился он в Oracle, до сих пор он работает там и модернизируется. Суть продукционного оптимизатора: формально говоря, различные виды выборки имеют разные веса (например: доступ к одной строке – вес (ранг) r=1, доступ к одиночной строке по кластерному соединению (когда формально выбирается одна, но соединение с другой) r=2, к одиночной строке по ключу с уникальным или первичным ключом r=3).

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

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

3) Выборочный (алгоритм) оптимизатор Chose: фактически выбирает между стоимостным и продукционным. Правила здесь просты: всегда использовать стоимостной режим, если есть статистика (в некоторых базах данных типа InterBase нет продукционного, есть только стоимостной); использовать продукционный режим, если нет статистики или других вспомогательных данных для работы в стоимостном режиме. Оптимизатор в зависимости от того стоимостной или продукционный режим перестраивает запрос немножко по-разному, там правила будут, разними.

Большинство СУБД позволят просматривать план выполнения запроса в интерактивном режиме. (после того как Вы сделали запрос можно нажать соответствующую кнопочку и увидеть план выполнения запроса на экране).

В любом SQL Server-е, начиная с InterBase-а, это возможность предоставляется.

Все большие базы данных предоставляют эту возможность. Например, в InterBase

Есть специальные утилиты Explain, который позволяет всё это дело пояснять.

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

В стоимостном режиме оптимизатор Oracle часто вместо индексов использует полный просмотр таблиц. Для этого есть специальные встроенные команды или ключевые слова. Этих слов-подсказок порядка 26 в Oracle. Их можно вставлять внутрь команды Select /*…*/-в общем случае они интерпретируются как комментарии, в СУБД Oracle, если в нужном месте эти вставки вставить, то можно перестраивать оптимизатор по-разному.

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

Так или иначе, большие СУБД вставляют в общепринятую команду Select свои подсказки, свои дополнительные слова, чтобы иметь возможность влиять на работы оптимизатора.

Работа оптимизатора видна при большом числе записей (миллионы записей).

При работе с 5-10 работа оптимизатора не видна, оптимизировать нечего. (оптимизатор сработает, но этого не видно).

Все SQL-запросы , поступающие в СУБД , обрабатываются примерно по одной схеме.

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

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

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

На четвертом этапе по внутреннему представлению наиболее оптимального плана выполнения запроса формируется выполняемое представление плана.

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

Оптимизатор. Его назначение. Этапы работы оптимизатора

Одним из основных преимуществ реляционных СУБД является механизм запросов на основе декларативного языка запросов SQL . При формулировании запроса пользователь указывает ЧТО он хочет получить а за то КАК это получить , отвечает СУБД . Поскольку существует потенциально очень большое множество способов выполнить конкретный запрос (комбинация способов и порядка соединения таблиц, путей доступа к данным и т.д.), появляется задача выбрать из всего множества способов выполнения запроса оптимальный . За эту задачу отвечает оптимизатор запросов .

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

Вычисление выражений и операций
Преобразование SQL операторов
Выбор способа оптимизации - по стоимости или по правилам
Выбор путей доступа
Выбор порядка соединений таблиц
Выбор метода соединений таблиц
Определение наиболее эффективного плана выполнения

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

Оптимизация по правилам ( RULE BASED ). Подход, при котором учитываются только способы доступа к данным , с зафиксированными приоритетами по эффективности доступа. Данный подход использовался в ранних версиях ORACLE и обладает существенным недостатком - он не учитывает реального распределения данных.
Оптимизация по стоимости (COST BASED ). Помимо эффективности различных путей доступа к данным , учитывается так же статистика по распределению данных и ресурсов операционной системы .

Режимы работы оптимизатора по стоимости. Установка режимов. Параметры, влияющие на работу оптимизатора

CBO управляется статистикой . Причем, когда он вычисляет стоимость , он учитывает количество блоков в таблице, количество строк, количество различных значений индексов и так далее. Чтобы он корректно работал, статистику надо регулярно собирать. Работа оптимизатора управляется параметром optimizer_mode , который может указываться на уровне сессии или на уровне экземпляра. Он может иметь следующие значения.

optimizer_mode = rule - RBO (был заморожен в версии 7), RBO, например, не умеет пользоваться Bitmap индексами.

optimizer_mode = all_rows - CBO , выбирает план выполнения с оптимальной стоимостью, режим работы оптимизатора по умолчанию.

optimizer_mode = first_rows - CBO , вычисляется стоимость разных планов выполнения , выбирает несколько планов с оптимальной стоимостью, по разным эвристическим соображениям пытается выбрать план, который наиболее быстро возвращает первые строки.

optimizer_mode = choose - Oracle сам выбирает, какой режим оптимизатора выбрать. Самый плохой случай: установлен данный режим и по каким-то таблицам есть статистика , а по каким-то нет. Пример, если хотя бы по одной из таблиц в запросе статистика есть, то в большинстве случаев будет использоваться all_rows , а если ни по одной из таблиц нет, то - rule . Если RBO не поддерживает интерфейс запроса (например, Bitmap -индексы), то используется CBO . CBO может оптимизировать запросы по таблицам, по которым не собрана статистика , используя умолчания для таблиц.

optimizer_mode = first_rows_1, first_rows_10, first_rows_1000 - при использовании first_rows Oracle вычисляет стоимость выполнения всего оператора , потом выбирает оптимальный план, при использовании first_rows_n вычисляет стоимость получения первых n строк, а стоимость выполнения всего оператора не вычисляется (данные режимы оптимальны для форм и первых операторов ).

Статистика. Назначение, способы формирования

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

Оптимизатор в базе данных Oracle также называется Оптимизатором запросов (Query Optimizer). Это инструмент оптимизации для анализа и выполнения SQL, он отвечает за создание и формулирование планов выполнения SQL. Есть два оптимизатора Oracle, оптимизатор на основе правил (RBO) и оптимизатор на основе затрат (CBO).

RBO: оптимизация на основе правил Оптимизация на основе правил

CBO: Оптимизация на основе затрат Оптимизация на основе затрат

RBO был принят с ORACLE 6 и использовался до ORACLE 9i. Начиная с ORACLE 10g, ORACLE полностью отказался от RBO. Он имеет строгие Правила использования, если вы пишете операторы SQL в соответствии с ними, независимо от содержимого таблицы данных, это не повлияет на ваш «план выполнения », то есть RBO обрабатывает данные Не "чувствительный"; он выбирает план выполнения для указанной таблицы в соответствии с правилами приоритета, указанными ORACLE. Например, в правилах приоритет индекса выше, чем сканирование всей таблицы ; RBO выбирает план выполнения в соответствии с доступным путем доступа и уровнем пути доступа. В RBO способ написания SQL часто влияет План выполнения, он требует от разработчиков понимания деталей RBO Тогда производительность сценариев SQL, написанных новичками, может быть очень низкой. С отказом от RBO о нем постепенно стало неизвестно. Возможно, только старшее поколение администраторов баз данных понимает это глубже. Что касается пути доступа к RBO, официальный документ дал подробное введение:

CBO - более разумный и надежный оптимизатор, чем RBO. Он был представлен в ORACLE 8, но постепенно получил развитие в ORACLE 9i. В ORACLE Полностью заменить RBO в 10g, CBO предназначен для расчета «стоимости» различных возможных «планов выполнения», а именно COST, из которых план выполнения с наименьшей COST выбирается как фактический план работы. . Он полагается на статистическую информацию об объектах базы данных. Точность статистической информации повлияет на CBO, чтобы он сделал лучший выбор. Если обнаружено, что задействованные объекты (таблицы, индексы и т. Д.) Не были проанализированы и подсчитаны при однократном выполнении SQL, ORACLE будет использовать технологию , называемую динамической выборкой, для динамического сбора таблиц и индексов. Некоторая информация о файлах.

Что касается RBO и CBO, у меня есть яркая аналогия: до эпохи больших данных бизнес мог принимать правильные решения на основе многолетнего накопленного опыта (RBO). Изменения на рынке. Однако в случае с большими данными, если бизнес по-прежнему основан на предыдущем опыте для принятия решений, а не полагается на большие данные, анализ данных и интеллектуальный анализ данных для принятия решений, тогда можно принимать неправильные решения. . Вот почему все больше и больше компаний уделяют все больше и больше внимания бизнес-аналитике и интеллектуальному анализу данных . Такие отрасли, как электронная коммерция, игры, телекоммуникации и т. Д., Уже нашли широкое применение. Отдел базы данных игровой компании проводит анализ бизнес-аналитики и выявляет потенциальных пользователей-потребителей. Изображение пока достаточно глубокое.

CBOПреимущества и недостатки RBO

CBO лучше, чем RBO, потому что RBO - жесткий и устаревший оптимизатор, который распознает только правила и не чувствителен к данным. В конце концов, правила мертвы, а данные меняются. Сгенерированный таким образом план выполнения часто бывает ненадежным и неоптимальным. CBO может быть отражен разными способами благодаря RBO. Давайте посмотрим на пример. Этот кейс взят из статьи «Сделайте Oracle работать быстрее».

Из вышесказанного видно, что распределение данных в тестовой таблице крайне неравномерно: есть только одна запись с и 50314 записей с Давайте сначала посмотрим на план выполнения двух SQL в RBO.

Из плана выполнения видно, что план выполнения RBO немного разочаровывает. Для почти все данные соответствуют условиям предиката, а индексирование может только увеличить дополнительные накладные расходы (потому что ORACLE сначала необходимо посетить , чтобы запросить блок данных индекса, найти соответствующее значение ключа в индексе, а затем получить доступ к соответствующим данным в таблице в соответствии с ROWID в значении ключа), поскольку мы почти хотим получить доступ Для всех данных в таблице полное сканирование таблицы, естественно, является наиболее оптимальным выбором для . RBO выбрал неправильный план выполнения. Вы можете сравнить план выполнения SQL в CBO. Очевидно, что он чувствителен к данным. План выполнения корректируется во времени в соответствии с объемом данных. Когда условие запроса равно 1 , выполняется сканирование всей таблицы; Когда условие запроса равно 100, требуется сканирование индекса с интервалом. Следующим образом:

Уже одно это позволяет понять, почему ORACLE настоятельно рекомендует использовать CBO, поскольку ORACLE 10g не поддерживает RBO. Говорят, что обратные волны реки Янцзы толкают передние волны, а передние волны умирают на пляже.

CBOРезюме точек знаний

Оптимизатор CBO создает набор планов выполнения, которые могут использоваться в соответствии с оператором SQL, оценивает стоимость каждого плана выполнения и вызывает генератор планов (Plan code> Generator) Сгенерируйте план выполнения, сравните стоимость плана выполнения и, наконец, выберите план выполнения с наименьшими затратами. Оптимизатор запросов состоит из конвертера запросов (Query Transform), оценщика затрат (Оценщик) и генератора планов (Генератор планов).

CBOКомпонент оптимизатора

CBO состоит из следующих компонентов:

· Преобразователь запросов

Роль конвертера запросов заключается в эквивалентном изменении формы оператора запроса для создания лучшего плана выполнения. Он решает, следует ли переписывать запрос пользователя (включая объединение представлений, продвижение предикатов, удаление вложенных подзапросов / подзапросов, переписывание материализованных представлений) для создания лучших планов запросов.

· Оценщик затрат (Оценщик)

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

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

· Генератор планов (Plan Generator)

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

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

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

Просмотреть оптимизатор ORACLE

Изменить оптимизатор ORACLE

Оптимизатор ORACLE 10g может изменять режим оптимизатора тремя способами: на системном уровне, на уровне сеанса и на уровне операторов, что очень удобно и гибко.

Значения, которые можно выбрать для optimizer_mode, следующие: first_rows_n, all_rows. Среди них first_rows_n имеет first_rows_1000, first_rows_100, first_rows_10, first_rows_1

В Oracle 9i режим оптимизатора может выбирать first_rows_n, all_rows, choose, rule Режим ISO:

Rule： Подход, основанный на правилах.

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

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

All Rows: Значение по умолчанию в 10g, которое мы называем методом стоимости, когда таблица имеет статистику, она вернет все строки таблицы самым быстрым способом, что улучшает общую пропускную способность запроса.

Хотя RBO больше не поддерживается в Oracle 10g, официальный документ Oracle 10g содержит только first_rows и all_rows. Однако вы все равно можете установить для optimizer_mode значение rule или выбрать, предполагается, что ORACLE рассматривается для перехода или обратной совместимости. Следующим образом.

Системный уровень

Уровень сеанса

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

В этой статье изложен многолетний опыт оптимизации SQL-запросов в процессе работы с базами данных Oracle 9i, 10g и 11g. В качестве рабочего инструмента для получения планов запросов мною используется всем известные программные продукты Toad и PLSQL Developer.

Нередко возникают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Назовем такие запросы неэффективными или ресурсоемкими.
Причины ресурсоемкости запроса могут быть следующие:

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

Программные средства, позволяющие получить планы выполнения запросов, можно разделить на 2 группы:

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

К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Это важный момент, поскольку надо учитывать, что реальный план выполнения может отличаться от того, что показывают эти программные средства. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются:

Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже приводиться пример плана выполнения запроса:

полученного в Toad

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

CPU Cost — процессорная стоимость выполнения;
IO Cost — стоимость ввода-вывода;
Temp Space – показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса

Трассировочный файл для удобства чтения расшифровывается утилитой Tkprof (при определенном навыке анализировать можно без расшифровки, в этом случае имеем более детальную информацию).

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:

Все строки (кроме текста запроса) являются стандартными.

Далее запуск запрос, который выдает на экран текст рекомендаций:

Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;

В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.

Анализ плана выполнения запроса.

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса

При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

Оптимизация запроса

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

плохая статистика таблиц и индексов, участвующих в запросе (наиболее важный фактор, на который в первую очередь надо обратить внимание);
проблемы с индексами: отсутствие нужных индексов, неэффективно построенные индексы, неэффективно используемые индексы, большое значение фактора кластеризации;
проблемы с хинтами: отсутствие хинтов или они неэффективны;
неэффективная структура запроса (запрос построен не корректно).

Неэффективная статистика.

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

Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами:
для таблицы:
для индекса:

где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.

Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:

Процент сбора статистики по индексу находиться по запросу

Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.

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

Проблемы с индексами

Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
— Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
— Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
— Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
— Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
— Имеется хинт, блокирующий работу индекса, например NO_INDEX.
— Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1.
Имеются сильные индексы, но они соперничают между собой.
Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:

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

Проблемы с хинтами в запросе

Проблемы с хинтами могут быть следующие:

Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
— хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
— в хинте USE_NL содержится не полный перечень алиасов;
— в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
— хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
При создании хинта в запросе есть ряд рекомендаций:
— В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
— При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
— Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы где T-алиас таблицы.
— Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param('Параметр инициализацци' N) */ , например, /*+ opt_param('optimizer_index_caching' 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.

Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.

Читайте также: