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

Обновлено: 21.11.2024

Вопрос: мне нужно несколько советов, чтобы понять план объяснения Oracle. Мне сказали, что чтение плана объяснения Oracle поможет мне определить возможности настройки, но план объяснения и то, как его использовать, для меня загадка.

Ответ. Говоря простым языком, план объяснения Oracle — это инструмент, с помощью которого Oracle сообщает вам, как он планирует выполнять запрос. Очевидно, что это делает план объяснения ценным инструментом настройки.

После того, как план объяснения Oracle покажет, как он планирует выполнять рассматриваемый запрос, можно настроить среду для более быстрого выполнения запроса.

План объяснения Oracle — это оператор, который возвращает планы выполнения для запрошенных операторов SELECT, UPDATE, INSERT и DELETE. План выполнения для любого данного оператора показывает последовательность операций, используемых Oracle для выполнения этого оператора.

Информация, возвращаемая планом объяснения Oracle, включает:

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

В дополнение к информации, перечисленной выше, таблица плана объяснения Oracle также будет содержать информацию о:

  • оптимизация
  • разметка
  • параллельное выполнение

Информация плана объяснения записывается в таблицу плана объяснения. Важно отметить, что таблица плана объяснения должна быть создана до запуска оператора плана объяснения.

После создания таблицы плана объяснения Oracle подозрительные операторы SQL можно проверить и соответствующим образом настроить.

SQL> SET AUTOTRACE ON
SQL> SELECT *
2 FROM emp e, dept d
3 WHERE e.deptno = d.deptno
4 AND e.ename = 'СМИТ';

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

Предпочтительнее использовать метод объяснения Oracle.

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

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

SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = ' СМИТ';

Если к одной и той же таблице плана объяснения Oracle обращаются несколько человек или необходимо сохранить историю планов выполнения Oracle, следует использовать предложение statement_id оператора плана объяснения Oracle. Это связывает указанный пользователем идентификатор с каждым планом объяснения, который можно использовать при извлечении данных.

В следующем примере показано, как statement_id задается с помощью оператора объяснения плана Oracle.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 И e.ename = 'СМИТ'; Объяснение.

Цели настройки операторов SQL, возвращаемых планом объяснения Oracle, довольно просты:

  • Устранение неоптимальных полных сканирований больших таблиц. Убедитесь, что выбран самый быстрый путь доступа к данным. План выполнения "ПОЛНЫЙ" является ключевым.
  • Использовать самый быстрый метод соединения таблиц: оптимизатор должен разумно выбирать между соединениями с вложенными циклами, хэш-соединениями и методами соединения с преобразованием звезд, и они отображаются в плане выполнения.
  • Обеспечить оптимальный порядок объединения таблиц: SQL будет работать быстрее, когда первое объединение таблиц предоставит наименьший результирующий набор.

С Oracle 11g управление планами SQL развивается более полно.

Получите полную
информацию о настройке Oracle SQL

Знаменитая книга "Расширенная настройка Oracle SQL. Полное руководство" содержит ценную информацию о настройке Oracle SQL. Эта книга включает сценарии и инструменты для повышения производительности Oracle 11g, и вы можете купить ее со скидкой 30 % непосредственно у издателя.

Бурлесон — американская команда

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

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

Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:

и укажите URL-адрес страницы.


Burleson Consulting

Оракул поддержки баз данных

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

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

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

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

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

Несмотря на то, что несколько различных инструментов отобразят для вас план выполнения Oracle, на самом деле существует только два способа его создания. Вы можете использовать команду «Объяснить план» или просмотреть план выполнения инструкции SQL, находящейся в данный момент в кэше курсоров, используя представление словаря V$SQL_Plan. На этом занятии рассматриваются оба метода и вы узнаете, какую дополнительную информацию вы можете получить от оптимизатора, чтобы поделиться с вами при создании плана. Это также объясняет, почему вы не всегда получаете один и тот же план для каждого подхода, как я уже говорил в предыдущем посте.

Как использовать DBMS_XPLAN для ФОРМАТИРОВАНИЯ плана выполнения

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

Часть 2 этой серии посвящена оценкам количества элементов и тому, как их можно улучшить!

Часть 3 этой серии посвящена методам доступа и тому, что вы можете сделать, если не получите ожидаемый метод доступа.

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

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

Не знаете, как понять план выполнения запросов Oracle? Этот 10-минутный пошаговый учебник – первая из двух частей, в которых вы узнаете все, что вам нужно знать о плане запросов.

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

Когда вы отправляете SQL-запрос к Oracle, база данных Oracle внутри создает план выполнения запроса, чтобы получить нужные данные из физических таблиц. План выполнения запроса — это не что иное, как набор методов того, как база данных будет получать доступ к данным из таблиц. Этот план выполнения запроса имеет решающее значение, поскольку разные планы выполнения требуют разных затрат и времени для выполнения запроса.

То, как создается план выполнения, на самом деле зависит от того, какой тип оптимизатора запросов используется в вашей базе данных Oracle. Существует два разных варианта оптимизатора — Оптимизатор на основе правил (RBO) и Оптимизатор на основе затрат (CBO). Для Oracle 10g оптимизатором по умолчанию является CBO. Оптимизатор на основе затрат заставляет Oracle генерировать план оптимизации, принимая во внимание всю связанную статистику таблиц. С другой стороны, RBO использует фиксированный набор предопределенных правил для создания плана запроса.Очевидно, что такой фиксированный набор правил не всегда может создать наиболее эффективный план. Это связано с тем, что эффективный план будет сильно зависеть от характера и объема данных таблиц. По этой причине CBO предпочтительнее RBO.

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

Но эта статья не для сравнения RBO и CBO (на самом деле сравнивать эти два нет особого смысла). Эта статья кратко поможет вам понять,

  1. Как мы можем увидеть план выполнения запроса
  2. Как мы понимаем (точнее, интерпретируем) план выполнения.

Итак, начнем. Я буду использовать сервер Oracle 10g и клиент SQL *Plus, чтобы продемонстрировать все детали.

Полное сканирование таблицы Oracle (FTS)

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

Далее я вставлю 15 000 записей в эту вновь созданную таблицу (данные взяты из одной из моих существующих таблиц продуктов из одной из производственных сред моего клиента).

Помните, что в настоящее время в таблице нет индекса.

Итак, мы начинаем наше путешествие с написания простого оператора выбора в этой таблице, как показано ниже,

Обратите внимание, что оптимизатор решил использовать RBO вместо CBO, поскольку у Oracle нет статистики для этой таблицы. Давайте теперь создадим некоторую статистику для этой таблицы, выполнив следующую команду,

Теперь давайте проведем тот же эксперимент еще раз,

Вы можете легко увидеть, что на этот раз оптимизатор использовал оптимизатор на основе затрат (CBO), а также предоставил дополнительную информацию (например, строки и т. д.)

Следует отметить, что Oracle читает всю таблицу (обозначается как TABLE ACCESS FULL), что совершенно очевидно, поскольку запускаемый оператор select * пытается прочитать все. Так что пока ничего интересного.

Индексировать уникальное сканирование

Теперь добавим в запрос предложение WHERE, а также создадим дополнительные индексы для таблицы.

Таким образом, приведенное выше утверждение указывает на то, что CBO выполняет индексное уникальное сканирование. Это означает, что для получения запрошенного значения идентификатора Oracle фактически читает только индекс, а не всю таблицу. Конечно, это будет быстрее, чем операция ПОЛНЫЙ ДОСТУП К ТАБЛИЦЕ, показанная ранее.

Доступ к таблице по индексу RowID

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

ДОСТУП К ТАБЛИЦАМ ПО INDEX ROWID — интересная часть для проверки. С этого момента мы указали select * для того, чтобы Oracle сначала использовал индекс для получения идентификатора строки записи. Затем он выбирает все столбцы по идентификатору строки.

Сканирование диапазона индексов

Но что, если мы укажем > или между критериями в предложении WERE вместо условия равенства? Как показано ниже,

Итак, на этот раз CBO применяет сканирование диапазона индексов вместо уникального сканирования индекса. То же самое обычно происходит, если мы также используем предложение between.

Быстрое полное сканирование индекса

Теперь давайте рассмотрим еще один интересный аспект сканирования ИНДЕКС, просто изменив «10». Прежде чем мы увидим результат, просто напомните себе, что существует более 15000 продуктов с идентификаторами от 1 до 15000+. Поэтому, если мы напишем «10», мы, вероятно, получим взамен почти 14990+ записей. Так делает ли Oracle в этом случае INDEX RANGE SCAN? Посмотрим,

Итак, Oracle фактически использует INDEX FAST FULL SCAN для «быстрого» сканирования индекса и возврата записей из таблицы. Это сканирование является «быстрым», потому что в отличие от полного сканирования индекса или уникального сканирования индекса, ИНДЕКСНОЕ БЫСТРОЕ ПОЛНОЕ СКАНИРОВАНИЕ использует многоблочный ввод-вывод (ввод-вывод), тогда как первые используют одноблочный ввод-вывод.

Примечание о ПЛАНЕ ЗАПРОСА

FTS или полное сканирование таблицы

  • Вся таблица прочитана до максимальной отметки
  • Использует многоблочный ввод/вывод
  • Буфер из операции FTS хранится в конце буферного кэша LRU

Индексировать уникальное сканирование

Быстрое полное сканирование индекса

  • Возможен многоблочный ввод/вывод
  • Возвращенные строки могут быть не отсортированы

Полное сканирование индекса

  • Одноблочный ввод/вывод
  • Возвращенные строки, как правило, будут отсортированы

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

План объяснения — это представление пути доступа, используемого при выполнении запроса в Oracle. Обработку запроса можно разделить на 7 этапов:

  1. Синтаксический – проверяет синтаксис запроса
  2. Семантика – проверяет существование и доступность всех объектов
  3. Слияние представлений: запрос переписывается как объединение базовых таблиц вместо использования представлений
  4. Преобразование оператора — переписывает запрос, преобразуя некоторые сложные конструкции в более простые, где это уместно (например, извлечение подзапроса, преобразование в/или преобразование). Для одних преобразований используются правила, а для других стоимость рассчитывается на основе статистики.
  5. Оптимизация – определяет оптимальный путь доступа для выполнения запроса. Оптимизатор затрат (CBO) использует статистику для анализа относительной стоимости доступа к объектам.
  6. Создание плана оценки запроса (QEP)
  7. Выполнение QEP

Шаги [1]–[6] иногда группируются под термином «Синтаксический анализ».
Шаг [7] — это выполнение оператора.

План объяснения — это представление пути доступа, созданного на шаге 6.

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

Терминология

Источник строк. Источник строк — это программная функция, реализующая определенные операции (например, сканирование таблицы или хэш-соединение) и возвращающая набор строк.
Предикаты – предложение where запроса.
Кортежи – строки.
Управляющая таблица – это источник строки, который мы используем для заполнения запроса. Если будет возвращено много строк, это может отрицательно сказаться на всех последующих операциях.
Probed Table – это объект, в котором мы просматриваем данные после того, как извлекли из него соответствующие ключевые данные. водительский стол.

Как Oracle получает доступ к данным?

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

Если рассмотреть следующий простой план объяснения:

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

В этом столбце самая правая (т.е. самая верхняя) операция выполняется первой. Другими словами, просматривайте столбец Operation вниз, начиная сверху, пока не найдете операцию с наибольшим отступом. Это выполняется в первую очередь. В данном примере это будет операция с (ДОСТУП К ТАБЛИЦЕ ПОЛНЫЙ).

В этом примере TABLE ACCESS FULL EMP – это первая операция, которая будет выполнена. Это утверждение означает, что мы выполняем полное сканирование таблицы EMP. Когда эта операция завершается, результирующий источник строк передается на следующий уровень запроса для обработки. В данном случае это SELECT STATEMENT, который является вершиной запроса.

Другие столбцы плана объяснения содержат различную полезную информацию для определения причин выбора плана:

  • Строки. Сообщает нам предполагаемое количество строк, которое оптимизатор ожидает вернуть в этой строке плана выполнения
  • Байты. Сообщает нам предполагаемое количество байтов, которое оптимизатор ожидает вернуть в этой строке плана выполнения
  • Стоимость (% ЦП). Это оценка оптимизатором «стоимости» и % ЦП запроса. Стоимость позволяет оптимизатору сравнивать предполагаемую эффективность различных планов друг с другом.
  • Время. Это оценка оптимизатором продолжительности каждого шага запроса.

Пример 1

Далее следует пошаговое руководство по приведенному выше плану:

Выполнение начинается с: и доходит до него следующим образом:
Начиная с >

ID=0 не содержит операций над ним, поэтому у него нет родителя, но есть 1 дочерний элемент.
ID=0 является родителем и зависит от него для строк. Вы можете сказать, что это родитель, потому что дочерний элемент имеет отступ.
So должен быть выполнен до >

Как и раньше, является дочерним элементом /> От отступа и имеет отступ на том же уровне ниже Таким образом, является родителем и и зависит от них для строк. Так и должно быть выполнено до >

ID=2 — это первый дочерний элемент /> Из отступа, является родителем и зависит от него для строк. Поэтому необходимо выполнить перед переходом к >

ID=3 является (единственным) дочерним элементом /> ID=3 не имеет дочерних операций. Это означает, что это первый шаг, который выполняется запросом. Строки предоставляются с этого шага.

ID = 1, а также зависят от После того, как были созданы строки, они передаются, и этот шаг использует их для любой операции, которую он выполняет. Затем он предоставляет обработанные строки своему родителю и так далее по дереву. Это означает, что выполняется второй шаг. не выполняется следующим, потому что он имеет 2 входа. Прежде чем он сможет начать работать, необходимо получить доступ к обоим из них, поэтому теперь давайте посмотрим на второй дочерний элемент >

.

ID=4 является вторым потомком /> ID=4 является родителем и зависит от него для строк. должен быть выполнен до Это означает, что выполняется третий шаг, за которым следует >

После получения входных данных от обоих дочерних элементов он может выполняться. обрабатывает строки, которые он получает от своих зависимых шагов (ID=2 & и возвращает их своему родителю /> ID=0 возвращает строки пользователю.

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