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

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

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

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

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

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

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

Дополнительно к дереву исходных строк (или дереву потока данных в параллельных операциях) таблица плана содержит следующие данные: 

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

Результаты выполнения EXPLAIN PLAN позволяют определить, выбирает ли оптимизатор конкретный план выполнения, например, использование вложенных циклов.

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

Существует много способов получить планы исполнения для выражений в БД, наиболее популярные перечислены ниже:

• Команда 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. 

Преимущество использования пакета DBMS_XPLAIN заключается в предоставлении возможности просмотра форматированного плана выполнения  SQL выражения в не зависимости от источника. 

Команда 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

Запрос данных из 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) 

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

Вывод EXPLAIN PLAN является табличным представлением древовидной структуры плана исполнения. Каждый шаг (строчка в плане исполнения или узел в дереве) представляет собой Источник строки.
Порядок узлов под parrent показывает порядок выполнения узлов на этом уровне. Если два шага расположены на одном уровне, первый по порядку будет выполняться первым.
В формате дерева, листья слева на каждом уровне дерева  идентифицируют точку начала выполнения.
Шаги плана выполнения не выполняются в том порядке, В котором они пронумерованы. Есть отношения родитель-ребенок между шагами.
В PLAN_TABLE и V$SQL_PLAN важными элементами для получения древовидной структуры являются столбцы ID, PARRENT_ID и POSITION. В файле трассировки, эти столбцы соответствуют полям id, pid и pos соответственно.
Одним из путей чтения плана исполнения является преобразование его в график, имеющий  древовидную структуру. Вы можете начать сверху, запись с ID=1 является верхней точкой дерева. Это справедливо для операций, которые имеют значение parrent_id или pid равное 1.
Для представления плана в виде дерева сделайте следующее:

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: После того как ID=2 обработает все исходные строки для объединения, выполнится полное сканирование таблицы SALGRADE (ID=6) 
• 1: Данные, полученные после выполнения ID=6 будут использованы для фильтра строк из ID=2 и ID=6

Процессы потомки выполняются перед родительскими процессами, не смотря на то что  структуры соединений должны быть сформированы до выполнения процессов-потомков. Возможно, самый простой способ объяснить порядок выполнения - для выполнения операции соединения NESTED LOOPS с ID=2, два потомка {ID=3 и ID=4 (вместе с их потомками)} должны завершить свое выполнение перед тем как ID=2 будет выполнен.

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

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

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

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

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