Как читать план запроса oracle

Об Oracle на русском

Oracle Database 11g Настройка производительности.

Как читать планы выполнения запросов

Что такое план выполнения ?

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

Выражение EXPLAIN PLAN захватывает план выполнения выбранный оптимизатором для выполнения выражений типа SELECT, UPDATE, DELETE и INSERT. Шаги плана выполнения не выполняются в том порядке, в котором они указаны в плане. Между шагами существуют отношения типа родитель-потомок. дерево исходных строк — основа плана исполнения. Оно содержит следующую информацию:

• Сортировка таблиц, на которые ссылается оператор
• Метод доступа для каждой таблицы, указанной в операторе
• Метод соединения для таблиц, применяемый операторами соединения в выражении
• Операции с данными, такие как фильтр, сортировка или агрегирование

• Данные оптимизации, такие как стоимость и кардинальность каждой операции
• Данные секционирования, такие как набор партиций к которым выполнялся доступ
• Данные параллельного выполнения, такие как метод распределения операций соединения

Где найти планы выполнения?

• Команда EXPLAIN PLAN позволяет вам просматривать план исполнения, который оптимизатор может использовать для выполнения выражения. Эта команда очень полезна, поскольку она строит план выполнения и записывает его в таблицу, называемую PLAN_TABLE не сохраняя при этом SQL выражение.
• V$SQL_PLAN предоставляет возможность просмотреть планы выполнения для курсоров, которые были недавно выполнены. Информация хранящаяся в V$SQL_LAN очень похожа на информацию, которую выдает команда EXPLAIN PLAN. Однако Explain Plan показывает потенциальный план выполнения, а V$SQL_PLAN хранит планы уже выполнявшихся запросов.
• V$SQL_PLAN_MONITOR содержит статистику мониторинга на уровне планов для каждого SQL выражения, найденного в V$SQL_MONITOR. Каждая строка содержащаяся в V$SQL_PLAN_MONITOR соответствует определенной операции плана исполнения.
• Инфраструктура AWR и Statspack хранят планы выполнения наиболее часто вызываемых SQL. Планы помещаются в представление dBA_HIST_SQL_PLAN или STATS$SQL_PLAN.
• Планы выполнения и источники строк записываются также в фалы трассировки, генерируемые DBMS_MONITOR.
• SQL Management Base — это часть словаря данных, хранящегося в табличном пространстве SYSAUX. Здесь хранится журнальная информация об операциях, история планов выполнения и опорные линии так же как и профили для SQL выражений.
• Событие диагностики 10053 используемое для записи вычислений оптимизатора стоимости также может генерировать планы выполнения запросов.
• Начиная с версии 10.2 когда вы получаете dump состояния процесса, план выполнения также включается в сгенерированый файл трассировки.

Просмотр планов выполнения

Если вы выполняете команду EXPLAIN PLAN в SQL*Plus, вы можете затем выбрать данные из таблицы PLAN_TABLE и просмотреть сгенерированный планы выполнения. Наиболее простым способом просмотреть план выполнения является использование пакета DBMS_XPLAIN. Пакет DBMS_XPLAIN содержит пять доступных функций:

• DISPLAY: Используется для форматированного вывода плана выполнения.
• DISPLAY_AWR: Используется для форматированного вывода плана выполнения SQL выражений, хранящихся в репозитории AWR.
• DISPLAY_CURSOR: Используется для форматированного вывода плана выполнения из любого загруженного курсора
• DISPLAY_SQL_PLAN_BASELINE: Используется для форматированного вывода одного и более планов выполнения SQL выражений идентифицируемых по заголовкам.
• DISPLAY_SQLSET: Используется для форматированного вывода плана выполнения хранящегося в SQL Tuning set.

Команда EXPLAIN PLAN

• Команда EXPLAIN PLAN используется для генерации плана выполнения запроса.
• После того как план сгенерирован, его можно посмотреть, запросив информацию из таблицы PLAN_TABLE

PLAN TABLE создается автоматически как глобальная временная таблица, используемая в последующем всеми пользователями для хранения планов выполнения. Вы можете создать собственную PLAN TABLE при помощи скрипта $ORACLE_HOME/rdbms/admin/utlxplan.sql в случае необходимости длительного хранения планов выполнения.

Структура команды EXPLAIN PLAN

Команда EXPLAIN PLAN вставляет строку в PLAN TABLE для каждого шага плана выполнения.
Пример выполнения команды EXPLAIN PLAN

Данная команда вставляет план выполнения для выражения в PLAN TABLE и добавляет тэг demo01 для последующего обращения.

Существует множество способов получения плана выполнения. выше рассматривается метод с использованием команды EXPLAIN PLAN. Данная команда генерирует план выполнения SQL выражения не выполняя его при этом, и помещает результат в PLAN TABLE. PLAN TABLE представляет древовидную структуру при помощи которой можно вернуть план выполнения для выражения используя колонки ID и PARENT_ID и конструкцию CONNECT BY в выражении SELECT .

Вывод содержимого PLAN TABLE

В примере приведенном выше используется ключ ALL для функции DBMS_XPLAIN.DISPLAY, который позволяет просмотреть всю доступную информацию о плане выполнения, хранимую в PLAN TABLE. Данный вывод помимо стандартной информации, содержит дополнительную информацию такую как PROJECTION, ALIAS и информацию о REMOTE SQL, если операция распределенная.

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

• ROWS если это уместно, показывает количество строк, предположительно посчитанное оптимизатором.
• ROWS если это уместно, показывает количество байт, предположительно посчитанное оптимизатором.
• COST если это уместно, показывает стоимость, предположительно посчитанную оптимизатором
• PARTITION если это уместно, показывает отброс патриций оптимизатором
• PARALLEL или это уместно, показывает информацию PX (метод распределения информации и информацию о очередях доступа к таблице )
• PREDICATE или это уместно, показывает информацию о предикате
• PROJECTION или это уместно, показывает секцию проекции

Использование Explain Plan в SQL Developer

AUTOTRACE

Когда выражение выполняется в SQL*Plus или SQL Developer вы можете автоматически получать план выполнения и статистику выполнения выражения. Отчет генерируетсся автоматически после выполнения любых видов операций таких как SELECT, INSERT, UPDATE и DELETE. Данную информацию можно использовать для диагностики и настройки производительности SQL выражений.

Для использования AUTOTRACE в БД должна быть создана PLAN TABLE и пользователю, который выполняет AUTOTRACE должна быть выдана роль PLUSTRACE. Роль PLUSTRACE создается и выдается роли DBA при помощи скрипта $ORACLE_HOME/sqlplus/admin/plustrce.sql

Синтаксис AUTOTRACE

Вы можете выполнять Autotrace используя синтаксис указанный на рисунке выше. Доступны для использования также следующие опции:

• OFF Отключает использование трассировки
• ON Включает использование автоматической трассировки
• TRACE [ONLY] Включает автоматическую трассировку и подавляет вывод SQL
• EXPLAIN Показывает план выполнения но не показывает сатистику
• STATISTICS Показывает статистику без плана выполнения

Примеры использования AUTOTRACE

AUTOTRACE: STATISTICS

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

• recursive calls — количество рекурсивных вызовов, сгенерированных на клиентской и серверной стороне.Oracle Database поддерживает таблицы, используемые для внутренней обработки. Когда Oracle Database необходимо внести изменения в этих таблицах, она генерирует внутренний оператор SQL, который, в свою очередь, порождает рекурсивный вызов.
• db block gets — количество раз, которое запрошен блок CURRENT
• consistent gets — количество раз, которое запрошена операция целостного чтения блоков данных.
• physical reads — количество блоков данных, прочитанных с диска. Это число представляет сумму значений physical reads direct и всех чтений из буферного кэша.
• redo size — общее количество генерированного redo в блоках
• bytes sent via SQL*Net to client — общее количество байт переданных клиенту от фонового процесса.
• bytes received via SQL*Net from client — общее количество байт, полученных от клиента Oracle*Net
• SQL*Net roundtrips to/from client — Общее количество сообщений Oracle NET отправленных и полученных от клиента.
• sort (memory) — количество операций сортировки, успешно выполненных в памяти и не потребовали записи на диск.
• sort (disk) — количество операций сортировки которое потребовало выполнения как минимум одной дисковой операции.
• row processed — количество строк, обработанных в процессе выполнения операции.

db_block_gets показывает операции чтения текущего блока из базы данных. consistent gets — это операции чтения блоков которые должны удовлетворять конкретный номер SCN. physical reads показывает чтение блоков с диска. db_block_gets и consistent gets — показатели статистики, которые постоянно мониторятся. Они должны быть низкими по сравнению с количеством извлекаемых строк. Сортировка выполняться в памяти, а не на диске.

AUTO TRACE с использованием SQL*Developer
Представление V$SQL_PLAN

Данное представление показывает планы выполнения для курсоров, которые все еще находятся в библиотечном кэше. Информация, хранящаяся в данном представлении во многом похожа на информацию из PLAN TABLE. Однако V$SQL_PLAN содержит планы исполнения для выражений, которые уже были выполнены. План выполнения, полученный в процессе выполнения EXPLAIN PLAN может отличаться от фактического плана выполнения, хранящегося в курсоре. Так происходит потому, что параметры сессии и значения BIND переменных могут отличаться от текущих.

Другое полезное представление: V$SQL_PLAN_STATISTICS в котором представлена статистика выполнения для каждой операции в плане выполнения каждого кешированного курсора. Еще одно полезное представление V$SQL_PLAN_STATISTIC_ALL объединяет в себе информацию выполнению из V$SQL_PLAN_STATISTICS и V$SQL_WORKAREA c планом выполнения, хранящимся V$SQL_PLAN.

Описание основных столбцов представления V$SQL_PLAN

Представление содержит те же столбцы, что и PLAN TABLE и несколько дополнительных столбцов. Столбцы, представленные в PLAN TABLE и имеющие одинаковые значения:

• ADDRESS
• HASH_VALUE

Столбцы ADDRESS и HASH_VALUE могут быть использованы для связки с V$SQLAREA для добавления специфической информации по курсору.

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

Представление V$SQL_PLAN_STATISTICS

Представление V$SQL_PLAN_STATISTICS предоставляет актуальную статистику по выполнению для каждой операции в плане выполнения, такие как количество обработанных строк или время выполнения. Вся статистика, за исключением количества строк накопленная. Например статистика по объединениям таблиц может включать в себя 3 операции объединения таблиц. Статистика, хранящаяся в V$SQL_PLAN_STATISTICS доступна для курсоров, которые были скомпилированы с параметром инициализации STATISTICS_LEVEL = ALL или с использованием подсказки оптимизатору GATHER_PLAN_STATISTICS.

Представление V$SQL_STATISTICS_ALL содержит статистику использования памяти для всех исходных строк, использовавших память SQL (сортировка или HASH join) Данное представление объединяет информацию, хранимую в представлении V$SQL_PLAN с статистикой выполнения из представлений V$SQL_PLAN_STATISTICS и V$SQL_WORKAREA.

Связи между важными динамическими представлениями производительности

V$SQLAREA показывает статистику для разделяемых SQL областей и содержит одну строку для каждой строки SQL выражения. Это представление предоставляет статистику по SQL выражениям, которые уже разобраны, находятся в памяти и готовы для выполнения:

• SQL_ID — идентификатор SQL родительского курсора в библиотечном кэше
• VERSION_COUNT количество дочерних курсоров которые представлены в кэше для заданного родительского курсора

V$SQL хранит статистику по разделяемым областям SQL и содержит одну строку для каждого SQL выражения потомка происходящего от родительского SQL выражения:

• ADDRESS представляет адрес заголовка родительского курсора для данного курсора
• HASH_VALUE -значение родительского выражения в библиотечном кэше
• SQL_ID — SQL идентификатор родительского курсора в библиотечном кэше
• PLAN_HASH_VALUE — числовое представление SQL плана для данного курсора
• CHILD_NUMBER — номер дочернего курсора

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

V$SQL_PLAN содержит информацию о плане выполнения для каждого дочернего курсора, загруженного в библиотечный кэш. Столбцы ADDRESS, HASH_VALUE и CHILD_NUMBER могут быть использованы для соединения с V$SQL для последующего определения дочерних курсоров.

V$SQL_PLAN _STATISTICS предоставляет статистику исполнения на уровне исходных строк для каждого дочернего курсора. Столбцы ADDRESS, HASH_VALUE могут быть использованы для объединения с представлением V$SQLAREA для определения родительского курсора. Столбцы ADDRESS , HASH_VALUE и CHILD_NUMBER могут быть использованы для соединения с V$SQL для определения дочерних курсоров.

V$SQL_PLAN _STATISTICS_ALL содержит статистику использования памяти для всех исходных строк, использовавших память SQL (сортировка или HASH join) Данное представление объединяет информацию, хранимую в представлении V$SQL_PLAN с статистикой выполнения из представлений V$SQL_PLAN_STATISTICS и V$SQL_WORKAREA.

V$SQL_WORKAREA предоставляет статистику по рабочим областям задействованным в процессе работы SQL выражения. Каждое выражение SQL хранимое в разделяемом пуле один или более дочерних курсоров, информация о которых хранится в V$SQL. V$SQL_WORKAREA содержит информацию о всех рабочих областях, необходимых этим дочерним курсорам.

V$SQL_WORKAREA может соединяться с V$SQLAREA (ADDRESS, HASH_VALUE) и с V$SQL ( ADDRESS, HASH_VALUE, CHILD_NUMBER).

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

• Топ-10 рабочих областей, которые требуют наибольшее количество памяти для кэша
• Для рабочих областей, работающих в режиме AUTO, какой процент рабочих областей выполняется с использованием максимального количества памяти?

V$SQLSTATS отображает основную статистику производительности для курсоров SQL, с каждой строкой, представляющей данные сочетающие текст SQL выражения и план выполнения SQL (сочетание SQL_ID и PLAN_HASH_VALUE). Столбцы в V$SQLSTATS идентичны , V$SQL и V$SQLAREA. Тем не менее, представление V$SQLSTATS отличается от V$SQL и V$SQLAREA скоростью обработки, масштабируемостью, большим сроком хранения данных (статистические данные могут храниться в представлении, даже после того, как курсор был вытеснен из разделяемого пула).

Пример запроса данных из представления V$SQL_PLAN

Вы можете запросить данные из представления V$SQL_PLAN используя функцию DBMS_XPLAIN.DISPLAY_CURSOR() для отображения текущего или последнего выполненного выражения (как показано на примере). Вы можете передать значение SQL_ID в качестве параметра для получения плана выполнения для данного выражения. SQL_ID — SQL_ID выражения, хранящийся в кэше курсоров. Вы можете получить соответствующее значение запросив информацию из столбца SQL_ID в V$SQL и V$SQLAREA. Альтернативно, вы можете выбрать PREV_SQL_ID для определенной сессии из V$SESSION. По умолчанию этот параметр не задан, в этом случае отображается план, хранящийся в последнем выполнявшемся курсоре.

CHILD_NUMBER это номер дочернего курсора. Если этот параметр не указан, будут показаны планы исполнения всех дочерних курсоров для SQL_ID.

Параметр FORMAT контролирует уровень детализации плана. В дополнение к стандартным значениям (BASIC, TYPICAL, SERIAL, ALL и ADVANCED ) поддерживаются дополнительные значения для отображения статистики выполнения курсора:

• IOSTATS: Предполагая что в процессе выполнения SQL собирается базовая статистика для планов исполнения параметр STATISTICS_LEVEL установлен в ALL или используется HINT GATHER_PLAN_STATISTICS) этот формат отображает статистику ввода/вывода для всех при указании ALL ( или только для последнего при указании LAST) выполнений курсора.
• MEMSTATS: Предполагая что используется автоматическое управление PGA (параметр pga_aggregate_target установлен в не нулевое значение) этот формат позволяет показать статистику использования памяти данный вид статистики применим только к операциям, интенсивно использующим память, таким как например HASH Join, сортировка или некоторые из bitmap операторов.
• ALLSTATS: Синоним для ‘IOSTATS MEMSTATS’
• LAST: По умолчанию, статистика планов выполнения показывается для всех выполнений курсора. Используя ключевое слово LAST можно просмотреть статистику плана, генерированную после его последнего выполнения.

Важные представления AWR

Вы можете просмотреть данные AWR в Enterprise Manager или сформировав отчет AWR, дополнительно вы можете обратиться к следующим динамическим представлениям производительности, хранящим данные AWR:

• V$ACTIVE_SESSION_HISTORY — данное представление показывает информацию о последней активности сессий, пополняемую каждую секунду.
• Представления V$ metric представляют данные метрик для отслеживания производительности системы. Список представлений метрик можно просмотреть обратившись к представлению V$METRICGROUP.
• Представления DBA_HIST содержат исторические данные, хранящиеся в базе данных. Эта группа представлений включает в себя:

1. DBA_HIST_ACTIVE_SESS_HISTORY содержит содержимое отобранной из памяти истории активного сеанса по недавней активности системы
2. DBA_HIST_BASELINE содержит информацию о опорных линиях, хранящуюся в базе данных.
3. DBA_HIST_DATABASE_INSTANCE содержит информацию об окружении БД
4. DBA_HIST_SNAPSHOT содержит информацию о снэпшотах хранимых в системе
5. DBA_HIST_SQL_PLAN содержит информацию о планах выполнения
6. DBA_HIST_WR_CONTROL содержит информацию о настройках AWR

Генерация отчетов по определенному SQL из репозитория AWR

SQL мониторинг

Инструмент SQL Monitoring доступен по умолчанию, когда параметр STATISTICS_LEVEL установлен в значение TYPICAL или ALL. Для использования данного инструмента необходимо также установить значение параметра CONTROL_MANAGEMENT_PACK_ACCESS в значение DIAGNOSTIC+TUNING для включения пакета диагностики и настройки.

По умолчанию мониторинг SQL включается автоматически когда SQL выражение выполняется в параллельном режиме или когда оно потребляет более пяти секунд процессорного времени или времени I/O в процессе одного выполнения.

Существует две подсказки оптимизатору для явного включения или выключения SQL мониторинга для выражения — MONITOR и NO_MONITOR.

Вы можете отслеживать статистику выполнения SQL выражения используя представления V$SQL_MONITOR и V$SQL_PLAN_MONITOR.

После активации мониторинга SQL выражения в динамическое представление производительности V$SQL_MONITOR добавляется информация, необходимая для отслеживания ключевых показателей производительности таких как время выполнения, CPU time, количество операций чтения и записи, время ожидания I/O, и другие метрики ожидания. Данная статистика обновляется в режиме реального времени в процессе выполнения SQL, по умолчанию — каждую секунду. После окончания выполнения информация о выполнении хранится в представлении V$SQL_MONITOR еще минуту, после чего она удаляется.

Пример отчета SQL Monitoring

В данном примере предполагается, что вы выбираете данные из таблицы SALES и в другой сессии запускаете SQL Monitoring.

Функция DBMS_SQLTUNE.REPORT_SQL_MONITOR может принимать несколько параметров, ограничивающих или расширяющих уровень детализации отчета, используя дополнительны параметры можно также указать выходной формат отчета (TEXT, HTML или XML), по умолчанию отчет формируется в текстовом формате.

Для уникальной идентификации двух выполнений одного SQL выражения, генерируется составной ключ, называемый ключ выполнения. Данный ключ состоит из трех атрибутов, каждый из которых относится к колонке в V$SQL_MONITOR:

• SQL_ID
• Внутренне генерированный идентификатор для того чтобы убедиться, что данный первичный ключ на самом деле уникален (SQL_EXEC_ID)
• Временная метка начала исполнения выражения (SQL_EXEC_START)

Интерпретация плана выполнения

1. Возьмите ID с самым низким значением и поместите его вверху дерева.
2. Определите строки, имеющие PID (parrent id) равные этому значению.
3. Поместите их в дерево ниже родительской записи в соответствии с их POS значениями от меньшего к большему слева на право.
4. После того как все ID родителя будут найдены, переместитесь на уровень вниз к следующему ID и повторите процесс, находя новые строки с одним PID.

1. Начните сверху
2. Переместитесь вниз по операциям, пока не дойдете до той, которая производит данные, но при этом не ничего потребляет. Это начало операции.
3. Посмотрите на дочерние операции которые есть у этого родителя. Дочерние операции будут выполняться следующими
4. После того как выполнятся потомки, выполнение родительской операции продолжится
5. Теперь, после того как эта операция и все её потомки выполнены переместитесь вверх по дереву, и смотрите на потомков исходного ряда операций и его родителей. Выполняется, по тому же принципу.
6. Перемещайтесь вверх по дереву до тех пор, пока все операции не будут просмотрены.

Стандартный метод интерпретации дерева плана исполнения:

1. Начните сверху
2. Переместитесь вниз и влево по дереву пока не достигните левого узла он выполняется первым
3. Посмотрите на потомков этого узла. эти потомки будут выполняться далее.
4. После того как выполнятся потомки, выполнение родительской операции продолжится
5. Теперь, после того как эта операция и все её потомки выполнены переместитесь вверх по дереву, и смотрите на потомков исходного ряда операций и его родителей. Выполняется, по тому же принципу.
6. Перемещайтесь вверх по дереву до тех пор, пока все операции не будут просмотрены

Интерпретация плана выполнения: Пример 1

На рисунке выше представлена интерпретация плана выполнения для выражения. Запрос указанный на рисунке пытается найти сотрудников, чья зарплата выбивается и сетки зарплат. Запрос выбирает данные из двух таблиц и включает в себя подзапрос основаный на выборке из другой таблицы для проверки размеров зарплат.
Посмотрим порядок выполнения для этого запроса. Исходя из данного и предыдущего рисунка порядок выполнения будет следующий: 3-5-4-2-6-1:

• 3: выполнение плана начнется с полного сканирования таблицы EMP (ID=3)
• 5: строки передаются шагу, контролирующему объединение nested loop (ID=2), который использует их, чтобы выполнить поиск строк в индексе PK_DEPT (ID=5)
• 4: ROWID строк, полученные после сканирования PK_DEPT используются для получения остальной информации из таблицы DEPT (ID=4)
• 2: ID=2, процесс объединения nested loop будет продолжен до его выполнения
• 6: После того как обработает все исходные строки для объединения, выполнится полное сканирование таблицы SALGRADE (ID=6)
• 1: Данные, полученные после выполнения будут использованы для фильтра строк из и потомки выполняются перед родительскими процессами, не смотря на то что структуры соединений должны быть сформированы до выполнения процессов-потомков. Возможно, самый простой способ объяснить порядок выполнения — для выполнения операции соединения NESTED LOOPS с два потомка должны завершить свое выполнение перед тем как будет выполнен.

Интерпретация плана выполнения: Пример 2

Этот запрос возвращает имена, имена департаментов и адреса сотрудников, чьи департаменты расположены в Сиэтле и у которых есть менеджер.

Для удобства форматирования на рисунок добавлена еще одна нумерация. Столбец слева представляет ID столбец справа — PID. План выполнения запроса показывает две операции NESTED LOOP JOIN. Интерпретируем план выполнения согласно метода представленного выше:

1. Начинаем сверху. вниз по операциям, пока не дойдем до той, которая производит данные, но ничего не потребляет. В данном случае, ID 0,1,2, и 3 потребляют данные. — первая сверху операция, которая не потребляет ресурсов, но производит данные. Это первый источник данных. INDEX RANGE SCAN вернет ROWID строк, которые будут использованы для возврата данных из таблицы LOCATIONS (ID=3)
2. Посмотрим на братьев и сестер этого источника строк, которые находятся с ним на одном уровне в дереве. Братья и сестры, находящиеся на том же уровне имеют и У есть потомок — который будет выполнена перед ним. Это операция сканирования INDEX RANGE SCAN по другому индексу, возвращающая ROWID, которые в последующем будут использованы для получения данных из таблицы DEPARTMENTS в процессе выполнения выполнения операции-потомка управление передается его предку. Следующей будет выполняться операция NESTED LOOPS с для объединения полученных ранее данных.
3. Теперь, когда родительская операция все её потомки выполнены, поднимаемся по дереву и смотрим есть ли у вышестоящей операции братья и сестры, находящиеся с ней на одном уровне. находится на одном уровне с операцией у которой есть потомок Этот потомок будет выполнен первым. INDEX UNIQUE SCAN будет выполнен для получения ROWID строк, которые затем будут использованы для получения данных из таблицы EMPLOYEES в операции на уровень выше после того как будут обработаны все операции на текущем уровне и их потомки. Последней будет выполняться операция объединения NESTED LOOPS с после выполнения которой результат будет передан выполнения операции следующий: 4-3-6-5-2-8-7-1-0

Ниже представлено подробное описание плана выполнения:

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

Результат объединяется с таблицей DEPARTMENTS используя индекс на столбце LOCATION_ID для соединения. Результат первой операции объединения является ведущим источником данных для второго вложенного цикла.

Вторая операция объединения исследует индекс на столбце EMPLOYEE_ID таблицы EMPLOYEES. Эта операция может выполниться, поскольку система знает (из первой операции объединения) ID всех менеджеров департаментов Сиэтла. В данном случае выполняется UNIQUE SCAN поскольку сканирование выполняется по индексу первичного ключа.

В конечном итоге выполняется запрос данных из таблицы EMPLOYEES для возврата фамилий сотрудников.
Интерпретация плана выполнения: Пример 3

Посмотрим план выполнения, указанный на рисунке выше. Попробуйте определить порядок в котором план будет выполняться и сделать вывод о порядке соединения таблиц. Как и в предыдущем примере, ID — первый столбец, PID — второй.Позицию можно определить по отступам. Важно понять в каком порядке соединяются таблицы, чтобы затем иметь возможность найти ваш план в файле трассировки для события 10053

Ниже представлена интерпретация плана:

1. Сначала система хэширует таблицу T3 в память (ID=3)
2. Затем система хэширует таблицу T1 в память (ID=5)
3. Затем начинается сканирование таблицы T2 (ID-6)
4. Система берет строку из T2 и исследует T1 (T1.i=T2.i)
5. Если строка выжила, система ищет её в T3 (T1.i=T3.i)
6. Если строка выжила, система передает её следующей операции.
7. Система выдает максимальное значение из предыдущего набора результатов.

Порядок выполнения следующий: 3-5-6-4-2-1
Порядок объединения: T1-T2-T3
Чтение более комплексных планов выполнения

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

Вы можете сократить план, для того чтобы сделать его более читабельным. Справа на рисунке показан тот же план выполнения, только уже сокращенный. Как показано на рисунке, это легко сделать при помощи Enterprise manager или SQL*Developer. Как видно на рисунке план включает в себя операцию объединения двух ветвей. Знания о словаре данных позволяют понять что две ветки соответствуют dictionary-managed и localy-managed табличным пространствам. Знания о базе данных позволяют понять, что в базе данных нет dictionary-managed табличных пространств. Таким образом, если есть проблема, она находится во второй ветке. Для получения подтверждения своих предположений, нужно посмотреть на информацию о плане и статистику выполнения каждого источника строк для того чтобы определить часть плана, потребляющую больше всего ресурсов. Затем нужно развернуть ту ветку, в которой обнаружены проблемы. Для использования данного метода вы должны дополнительно использовать статистику исполнения, которая может быть найдена в представлении V$SQL_PLAN_STATISTICS или в отчете tkprof, генерированном из файла трассировки. Например, tkprof суммирует время для каждой родительской операции, которое она затрачивает на выполнение плюс время выполнения всех операций-потомков.

Обзор плана выполнения

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

• План построен так, что наилучший фильтр применен к ведущей таблице
• Порядок соединения построен так, чтобы следующему шагу передавалось наименьшее количество строк (то есть порядок соединения должен идти к лучшим пока еще не использованным фильтрам)
• Метод join соответствует количеству строк, которые ему передаются для объединения. Например NESTED LOOP соединение с использованием индекса может быть не оптимальным когда возвращается много строк.
• Представления используются эффективно. Посмотрите на SELECT список, чтобы определить в каких местах необходимо обращение к представлению.
• Отсутствуют операции декартова произведения таблиц (даже с маленькими таблицами).
• Каждая таблица читается эффективно: рассматривайте предикаты SQL выражений по отношению к количеству строк в таблице. Детально посмотрите подозрительные операции, например full table scan для таблиц с большим количеством строк, которые присутствуют в предикате.

Полное сканирование таблиц может эффективно применяться для небольших таблиц, или для применения конкретного типа соединения (например HASH JOIN) для возвращаемых строк.

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

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

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

Описание операций плана выполнения в Oracle

Читает индекс целиком (все строки) в порядке, представленном индексом. В зависимости от различной системной статистики СУБД может выполнять эту операцию, если нужны все строки в порядке индекса, например, из-за соответствующего предложения ORDER BY. Вместо этого оптимизатор может также использовать операцию Index Fast Full Scan и выполнить дополнительную операцию сортировки.

Index Fast Full Scan

Читает индекс целиком (все строки) в порядке, хранящемся на диске. Эта операция обычно выполняется вместо полного сканирования таблицы, если в индексе доступны все необходимые столбцы. Подобно операции TABLE ACCESS FULL, INDEX FAST FULL SCAN может извлечь выгоду из многоблочных операций чтения.

Table Access By Index ROWID

Извлекает строку из таблицы, используя ROWID, полученный из предыдущего поиска по индексу.

Table Access Full

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

Merge Join

Соединение слиянием объединяет два отсортированных списка. Обе стороны объединения должны быть предварительно отсортированы.

Nested Loops

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

Hash Join

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

Sort Unique

Сортирует строки и устраняет дупликаты.

Hash Unique

Более эффективная реализация алгоритма сортировки и устранения дупликатов с использованием хэш-таблицы. Заменяет операцию Sort Unique в определенных обстоятельствах.

Sort Aggregate

Вычисляет суммарные итоги с использованием агрегатных функций SUM, COUNT, MIN, MAX, AVG и пр.

Sort Order By

Сортирует результат в соответствии с предложением ORDER BY. Эта операция требует больших объемов памяти для материализации промежуточного результата.

Sort Group By

Сортирует набор записей по столбцам GROUP BY и агрегирует отсортированный результат на втором этапе. Эта операция требует больших объемов памяти для материализации промежуточного результата.

Sort Group By Nosort

Агрегирует предварительно отсортированный набор записей в соответствии с предложением GROUP BY. Эта операция не буферизует промежуточный результат.

Hash Group By

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

Filter

Применяет фильтр к набору строк.

View

Создает промежуточное представление данных.

Count Stopkey

Прерывает выполение операций, когда было выбрано нужное количество строк.

Sort Join

Сортирует набор записей в столбце соединения. Используется в сочетании с операцией Merge Join для выполнения сортировки соединением слияния.

Intersection

Выполняет операцию пересечения между двумя источниками.

Union-All

Выполняет операцию объединения всех записей между двумя таблицами. Дублирующиеся строки не удаляются.

Load As Select

Прямая загрузка с использованием оператора SELECT в качестве источника.

Temp Table Generation/Transformation

Создает/преобразует временную таблицу. Используется в специфичных для Oracle преобразованиях типа Star.

Анализ запроса SQL

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

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

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

Для получения помощи по этой функции обратитесь к менеджеру учетной записи Responsys.

• About Oracle
• Legal Notices
• Terms of Use
• Your Privacy Rights

Опыт и рекомендации по оптимизации SQL-запросов

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

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

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

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

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

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

SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN AND S.DEPTISN =C.GET('prolonggroup') AND SB.RANK >70 AND ROWNUM 

Текст выполняемого запроса; select * from table(dbms_xplan.display_cursor()); 

Select * from v$sql_plan where sql_id='SQL_ID'; 

Select * from v$sql_plan where sql_id='SQL_ID';

Select sql_id from v$sql where sql_fulltext like '%уникальный фрагмент текста запроса%'; 

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER,LEVEL 12' TRACEFILE_IDENTIFIER='M_2013' TIMED_STATISTICS=TRUE SQL_TRACE=TRUE; ---Исследуемый запрос, например, Select * from AGREEMENT where ISN=138518816; ALTER SESSION SET SQL_TRACE=FALSE;

Select value from v$parameter p where p.name= 'user_dump_dest';

DECLARE my_task_name varchar2(30);my_sqltext clob;rep_tuning clob; BEGIN Begin DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task'); exception when others then NULL; end; MY_SQLTEXT:= ' --текст запроса (без точки с запятой в конце запроса) ' ; MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext, TIME_LIMIT => 60, --задается время выполнения в секундах TASK_NAME =>'my_sql_tuning_task', DESCRIPTION=> my_task_name , SCOPE => DBMS_SQLTUNE.scope_comprehensive); begin DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task'); exception when others then null;end; END;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') FROM DUAL;

SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN AND S.DEPTISN =C.GET('prolonggroup') AND SB.RANK >70 AND ROWNUM 

  
При анализе план просматривает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) обновляется соответственно процедурами: 
   для таблицы: 

execute DBMS_STATS.GATHER_TABLE_STATS ('HIST','AGREEMENT',NULL,10,NULL,'FOR ALL INDEXED COLUMNS SIZE AUTO',4);
execute DBMS_STATS.GATHER_INDEX_STATS('HIST', 'X_AGREEMENT',null,10,null,null,4);
где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.
Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:
SELECT owner, table_name, round(d.sample_size/decode(d.num_rows,0,100000000000,d.num_rows)*100,2) proch,d.last_analyzed FROM ALL_TABLES d WHERE owner='HIST' and table_name = 'AGREEMENT';
Процент сбора статистики по индексу находиться по запросу
SELECT owner,table_name, index_name, round(sample_size*100/nvl(decode(num_rows,0,100000,num_rows),1000000),2) proch,last_analyzed FROM ALL_IND_STATISTICS D Where owner='HIST' and table_name = 'AGREEMENT';
Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.
Блокировка статистики execute dbms_stats.lock_table_stats('имя схемы','имя таблицы'); Разблокировка execute dbms_stats.unlock_table_stats('имя схемы','имя таблицы'); Просмотр наличия блокировки статистики  SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STATTYPE_LOCKED FROM ALL_tab_STATISTICS D WHERE OWNER='ИМЯ СХЕМЫ' AND TABLE_NAME='ИМЯ ТАБЛИЦЫ';
Замечание. При хорошем значении статистики по таблице может быть неблагополучная статистика по какому-то индексу таблицы, в силу чего целесообразно отслеживать статистику не только таблицы, но и индексов таблицы.
Проблемы с индексами
Проблемы с индексами в плане выполнения проявляются при наличии в столбце 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 определяется по запросу: 


Select I.OWNER,T.TABLE_NAME, I.INDEX_NAME, T.BLOCKS, I.CLUSTERING_FACTOR, I.NUM_ROWS from ALL_INDEXES I, ALL_TABLES T where I.table_name=T.table_name and I.owner=T.owner and I.owner='имя схемы' and I.index_name='имя индекса';
Фактор кластеризации для индекса считает во время сбора статистики. Он используется оптимизатором при расчете стоимости индексного доступа к данным таблицы. Большой ФК (особенно близкий к числу строк в таблице) говорит о неэффективном индексе. Таким образом, ФК является характеристикой индекса, а не таблицы. Первое решение при большом ФК является убрать существующий индекс как не эффективный. Второе решение, если данный индекс наиболее часто применяется в запросах и он нужен, то перестроить структуру таблицы таким образом, чтобы строки в блоках таблицы были упорядочены в том же порядке, в котором расположена информация по данным строкам в индексе, т.е. сделать кластерными блоки таблицы, уменьшив таким образом число перемещений от одного блока к другому при работе индекса.
Проблемы с хинтами в запросе
Проблемы с хинтами могут быть следующие:

Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов: 
— хинт был написан, когда БД работала на 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 кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы


Select /*+ parallel(32) Index_FFS (T имя_индекса) */ count(*) From имя_таблицы Partition (имя_партиции) T; 
Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.
Неэффективно написанный запрос.
Причин неэффективности запроса несколько:

неэффективное соединение таблиц;
использование NOT и NOT IN в условии where;
блокировка индекса в силу использования неправильных функций к столбцу, по которому построен индекс;
большая вложенность запроса или большая его длина;
большой объем выбираемых данных, требующих подключения в работу дисков, в том числе для выполнения агрегированных функций (order by, group by и т.д.);

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


Среди причин неэффективности особое внимание следует уделить неэффективному соединению таблиц (наличие HASH или MERGE соединений там, где предпочтительнее NESTED LOOP — о чем сказано выше). Кроме того эффективность соединения может зависеть от порядка таблиц во фразе FROM. Чтобы оптимизатор работал с таблицами в том порядке, в каком они находятся во фразе From используется хинт Ordered.
Эффективность соединения зависит от полноты связи во фразе WHERE между таблицами. При недостаточной связке в плане выполнения появляется MERGE JOIN CAPTESIAN (о чем было сказано выше). Особое внимание при модификации запроса следует уделить фразе NOT IN в условии where. Как вариант освобождения от NOT IN можно использовать прием, при котором пишется первый запрос без NOT IN, а за ним после MINUS пишется тот же запрос с IN (вычитание из полного числа строк строки, получаемые после использования условия IN, который работает быстрее, чем NOT IN).
В целях ускорения работы запроса использовать (там, где это можно) вместо UNION фразу UNION ALL (UNION операция более медленная, т.к. осуществляется путем сортировки).
Рекомендуется уменьшать число таблиц во фразе FROM. Это позволит сделать план выполнения прозрачным для оптимизатора и его анализа. В первую очередь убрать из FROM таблицы, столбцы которых не используются после фразы Select. В этом случае можно использовать подзапросы с этими таблицами после Select или во фразе where. Задание диапазона дат, начиная с 01.01.0001, приводит к неэффективному плану выполнения. Надо сделать минимальную границу даты, т.е. как можно ближе к реальной дате.
В целях повышения производительности запроса не делать длинные запросы, т.к. длинный запрос увеличивает время разбора запроса оптимизатором, время передачи по каналам и занимает избыточную память.
В целях повышения производительности работы запроса шире использовать кэширование всех видов: последовательностей, таблиц, результатов выполнения запросов. Кэширование результатов выполнения запросов появилось в Oracle 11g и позволяет извлекать результат первого выполнения запроса из оперативной памяти. Это особенно эффективно при большом числе выполнения запроса и отсутствие в момент многократного выполнения запроса операций DML над таблицей.



Похожие публикации:

Как посмотреть рекламу в покет антс
Как установить openoffice на linux
Кто отдел
Что такое mud