Запрос использует разные планы oracle. Понимание результатов Execute Explain Plan в Oracle SQL Developer. Обзор плана выполнения

5 ответов

Вывод EXPLAIN PLAN - это отладочный вывод оптимизатора запросов Oracle. COST - это конечный результат оптимизатора затрат (CBO), целью которого является выбор того, какой из множества возможных планов должен использоваться для запуска запроса. CBO рассчитывает относительную стоимость для каждого плана, затем выбирает план с самой низкой стоимостью.

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

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

Например, скажем, у вас есть следующий запрос:

SELECT emp_id FROM employees WHERE months_of_service = 6;

(Столбец months_of_service имеет ограничение NOT NULL на нем и обычный индекс на нем.)

Есть два основных плана, которые может выбрать оптимизатор здесь:

• План 1: Прочитайте все строки из таблицы "сотрудники", для каждого проверьте, является ли предикат истинным (months_of_service=6).
• План 2. Прочитайте индекс, где months_of_service=6 (это приводит к набору ROWID), затем получите доступ к таблице на основе возвращенных ROWID.

Предположим, что таблица "сотрудники" содержит 1 000 000 (1 миллион) строк. Пусть далее представьте, что значения для months_of_service варьируются от 1 до 12 и по какой-то причине довольно равномерно распределены.

Стоимость Plan 1 , которая включает в себя FULL SCAN, будет стоить чтение всех строк в таблице сотрудников, что примерно равно 1 000 000; но поскольку Oracle часто может считывать блоки с использованием многоблочных чтений, фактическая стоимость будет ниже (в зависимости от того, как настроена ваша база данных) - например, предположим, что количество отсчетов с несколькими блоками равно 10 - расчетная стоимость полного сканирования составит 1,000,000/10; Общая стоимость = 100 000.

Стоимость Плана 2 , которая включает в себя сканирование INDEX RANGE SCAN и поиск таблицы по ROWID, будет стоить сканирование индекса, а также затраты на доступ к таблице с помощью ROWID. Я не буду вдаваться в то, как сканирование индексов диапазона будет стоить, но пусть представьте, что стоимость сканирования индекса индекса составляет 1 на строку; мы ожидаем найти совпадение в 1 из 12 случаев, поэтому стоимость сканирования индекса составляет 1,000,000/12 = 83,333; плюс стоимость доступа к таблице (предположим, что 1 блок считывается за доступ, мы не можем использовать многоблочные чтения здесь) = 83,333; Общая стоимость = 166 666.

Как вы можете видеть, стоимость плана 1 (полное сканирование) меньше, чем стоимость плана 2 (индексная проверка + доступ по rowid) - это означает, что CBO будет выбирать ПОЛНОЕ сканирование.

Если предположения, сделанные здесь оптимизатором, верны, то на самом деле план 1 будет предпочтительным и гораздо более эффективным, чем План 2 - который опровергает миф о том, что ПОЛНЫЕ сканирования "всегда плохие".

Результаты были бы совершенно разными, если целью оптимизатора было FIRST_ROWS (n) вместо ALL_ROWS - в этом случае оптимизатор предпочтет план 2, потому что он будет часто возвращать первые несколько строк быстрее, ценой менее эффективной для весь запрос.

CBO строит дерево решений, оценивая затраты на каждый возможный путь выполнения, доступный для каждого запроса. Затраты устанавливаются параметром CPU_cost или I/O_cost, установленным в экземпляре. И CBO оценивает затраты, насколько это возможно, с существующей статистикой таблиц и индексов, которые будут использоваться в запросе. Вы не должны настраивать свой запрос, основываясь только на стоимости. Стоимость позволяет понять, ПОЧЕМУ оптимизатор делает то, что он делает. Без затрат вы могли бы понять, почему оптимизатор выбрал план, который он сделал. Более низкая стоимость не означает более быстрый запрос. Есть случаи, когда это верно, и будут случаи, когда это неправильно. Стоимость основана на вашей таблице статистики, и если они ошибаются, стоимость будет неправильной.

При настройке запроса вы должны взглянуть на мощность и количество строк каждого шага. Имеют ли они смысл? Оптимизатор считается корректным? Правильно ли возвращаются строки. Если информация присутствует неправильно, то, скорее всего, оптимизатор не имеет надлежащей информации, необходимой для принятия правильного решения. Это может быть связано с устаревшими или отсутствующими статистическими данными в таблице и индексе, а также cpu-stats. Лучше всего обновлять статистику при настройке запроса, чтобы максимально использовать возможности оптимизатора. Знание вашей схемы также очень помогает при настройке. Зная, когда оптимизатор выбрал действительно плохое решение и указал его на правильный путь с помощью небольшого подсказки, можно сэкономить время.

Ваше упоминание "FULL" указывает мне, что запрос выполняет полноэкранное сканирование, чтобы найти ваши данные. В некоторых случаях это нормально, в противном случае это показатель плохой записи индексирования/запроса.

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

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

Итак, если чтение одного блока занимает 2 мс, а стоимость выражается как "250", запрос может потребоваться для завершения 500 мс.

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

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

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

Эти цифры могут сильно различаться в зависимости от операционной среды системы, и различные статистические данные могут храниться для операций "дневного OLTP" и операций "ночной периодической отчетности", а для "отчетов о конце месяца", если вы хотите.

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

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

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

В старых версиях Oracle стоимость операций с ЦП была проигнорирована, а относительная стоимость одно- и многоблочных чтений была эффективно исправлена ​​в соответствии с параметрами init.

Вы также можете запросить v $sql и v $session для получения статистики о операторах SQL, и это будет иметь подробные показатели для всех видов ресурсов, таймингов и исполнений.

Сразу уточню, что описывать буду на примере использования фри утилиты OraDeveloper Studio. Почему? Потому что обычными запросами этого сделать не удалось, а времени и желания разбираться не было, раз уж есть способ проще. 😉

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

Задача — грузить в базу десятки тысяч строк данных. Для каждой строки необходимо предварительно по базе найти дополнительные данные одним довольно громоздким запросом (4 таблицы через джойны).
Проблема — загрузка 15 тысяч строк занимает 8-9 часов. Так как по условиям задачи загружать надо часто, а не один раз в пятилетку… В общем, надо довести время до приемлемого.

Что я сделал?
1. Выяснил, что тормозит именно селект (данные вставляются и обновляются в таблицах, где куча строк и часть из таблиц не имеет ни индексов, ни ключей — отсюда и сомнения в вине селекта).
2. Проверил наличие индексов на используемых запросом полях. Добавил отсутствующие.
3. Спросил помощи у знающих. 🙂

Знающие посоветовали проанализировать план выполнения запроса и объяснили, как это сделать в OraDev.
Создаём новое окно запроса (Ctrl+N). Копируем в него наш запрос. Жмём Alt+G. Выбираем уже существующую либо создаём новую таблицу плана.
После выполнения появится дерево плана выполнения. Самостоятельно и без поллитры в нём разобраться не так просто. 😉

Что же нас интересует в этом дереве? Нас интересуют узлы (шаги), для которых указан большой Cost шага. Цену шага вы можете увидеть в свойствах шага (у меня окошко свойств постоянно открыто и потому мне надо лишь выбрать нужный шаг; вам же может потребоваться выбирать свойства по правому клику на шаге). Отыскиваем медленный шаг (самый верхний узел, корень дерева плана, в расчёт особенно не берём — там будет указана общая цена запроса, а мы итак уже знаем, что проблема именно в этом запросе). Нашли? Теперь смотрим, с какой таблицей, какими её полями и с каким количеством строк работает шаг — это есть в свойствах и имени шага. Смотрим и думаем, почему у нас так медленно?
У меня, например, один из шагов работал с 4000 записей вместо одной-трёх записей (не тысяч). Такого быть не должно было в принципе — я же ограничиваю выборку именно для того, чтобы выбирать из нужного диапазона, а не из кучи лишнего барахла. Внимательно присмотревшись к условию джойна, я заметил, что упустил одно из полей. Добавил поле в запрос и всё встало на свои места. Цена запроса (полная) уменьшилась с 531 до 6. 🙂

Спасибо камрадам nest и detect за помощь.

P.S. Извините, что не приводу скриншоты. С ними было бы намного нагляднее, но… Из-за конфиденциальности некоторой информации пришлось бы замазывать 80% и тогда опять вышло бы малопонятно.
P.P.S. Общее время загрузки существенно сократилось. На загрузку в базу 17.5 тысяч строк данных ушло 12 минут. В сравнении с 8-9 часами… Ну, вы и сами всё уже поняли. 😉

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 Включает автоматическую трассировку и подавляет вывод 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

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 . По умолчанию этот параметр не задан, в этом случае отображается план, хранящийся в последнем выполнявшемся курсоре.

• 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. Перемещайтесь вверх по дереву до тех пор, пока все операции не будут просмотрены.

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

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 поскольку сканирование выполняется по индексу первичного ключа.

Сначала система хэширует таблицу T3 в память (ID=3)

Затем система хэширует таблицу T1 в память (ID=5)

Затем начинается сканирование таблицы T2 (ID-6)

Система берет строку из T2 и исследует T1 (T1.i=T2.i)

Если строка выжила, система ищет её в T3 (T1.i=T3.i)

Если строка выжила, система передает её следующей операции.

Система выдает максимальное значение из предыдущего набора результатов.

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

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

Это как гвоздь в подошве любимого ботинка. Ходить можно, но все чаще ловишь себя на желании остаться на месте или перепоручить дело другим. Мелкие неудобства не только замедляют нашу работу, но и снижают мотивацию, вносят помехи в процесс, снижают качество результата. И если нашелся друг, который научил вас взять молоток и забить этот гвоздь, вы не только будете благодарны ему за помощь, но и сами поможете другим, избавив их от мелкой, но очень раздражающей помехи. Для этого и нужно общаться, делиться не только глубокими и сокровенными знаниями в форумах и на сайтах вроде Хабра, но и своими простыми трюками и «маленькими хитростями»

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

Недавно мне случилось общаться с одним из ведущих профессионалов СУБД Oracle. Он рассказал много интересного про работу с планами выполнения запросов в различных версиях этой СУБД и не постеснялся рассказать всем об используемых им инструментах, приемах и дать немного полезных мелких советов. Я сделал перевод одной из статей в его блоге и хотел бы предложить его вниманию Хабравчан. Несмотря на то, что описанный прием применялся для работы с Oracle, я теперь с успехом применяю тот же подход для MS SQL и Sybase.

Запустите запрос на выполнение, и появится закладка Query Plan, заполненная планом выполнения.


Поместите курсор мыши на любой из узлов на диаграмме и появится дополнительная полезная информация, относящаяся к этому шагу выполнения из плана запроса!
По умолчанию, Rapid SQL показывает план выполнения в графическом виде. Я вышел из старого мира оптимизации…. Предпочитаю текстовую версию, поэтому нажимаю правую кнопку мыши в окне с планом и выбираю “View as Text”.
Предпочитаю видеть текст запроса и план одновременно.


Это легко сделать. Видите закладки окон ISQL внизу главного окна? Для начала мы должны настроить Rapid SQL, чтобы он выдавал план в отдельном окне.


Нажмите кнопку Options (левый красный кружок) и затем установите опцию ‘Unattached’ для Result window. Это приведет к созданию двух отдельных закладок внизу Rapid SQL, после запуска запроса на выполнение. Просто протащите немного это окно за закладку и появится прямоугольник, куда можно переместить это окно.
Или можно воспользоваться пунктом Tile windows из главного меню программы

И еще: все это так же работает и в DBArtisan - решении для администраторов баз данных.