Как посмотреть план запроса oracle
Как получить план выполнения SQL в Oracle
Узнайте, как получить расчетный и фактический план выполнения SQL при использовании команд Oracle EXPLAIN PLAN ДЛЯ или GATHER_PLAN_STATISTICS.
Вступление
В этой статье мы рассмотрим, как мы можем получить план выполнения SQL в Oracle.
При настройке производительности план выполнения незаменим, так как он позволяет понять операции, выполняемые базой данных при выполнении определенного SQL-запроса.
Каков план выполнения SQL
При выполнении SQL-запроса база данных сначала проанализирует его и сгенерирует AST (Абстрактное синтаксическое дерево) для входящего запроса. Анализатор также проверяет синтаксис SQL-запроса и проверяет, существуют ли ресурсы базы данных, на которые ссылаются. Анализатор может переписать входящий запрос, чтобы оптимизировать его выполнение, не влияя на логику выборки результирующего набора.
Сгенерированное дерево запросов отправляется оптимизатору, которому необходимо определить наилучший способ извлечения данных, необходимых для текущего SQL-запроса. Таким образом, оптимизатор создает план выполнения, представляющий собой список операций, которые необходимо выполнить для удовлетворения требований SQL – запроса.
Полученный план выполнения отправляется Исполнителю, который запускает его и отправляет полученные данные обратно клиенту базы данных.
Этапы обработки и выполнения SQL можно визуализировать на следующей диаграмме.
Предполагаемый план выполнения SQL
Предполагаемый план выполнения генерируется оптимизатором без выполнения SQL-запроса. Вы можете сгенерировать предполагаемый план выполнения из любого клиента SQL, используя ОБЪЯСНИТЬ ПЛАН ДЛЯ или вы можете использовать Oracle SQL Developer для этой задачи.
ОБЪЯСНИТЕ ПЛАН ДЛЯ
При использовании Oracle, если вы добавляете команду ОБЪЯСНИТЬ ПЛАН ДЛЯ к заданному SQL-запросу, база данных сохранит предполагаемый план выполнения в соответствующей таблице PLAN_TABLE :
Параметр ВСЕ +СХЕМА форматирование позволяет получить более подробную информацию о предполагаемом плане выполнения, чем при использовании параметра форматирования по умолчанию.
Предполагаемый план будет выглядеть примерно так:
Обратите внимание, что некоторые столбцы были удалены, чтобы
Разработчик Oracle SQL
Если у вас установлен SQL Developer, вы можете легко получить расчетный план выполнения для любого SQL-запроса без необходимости добавлять ПЛАН ОБЪЯСНЕНИЯ ДЛЯ команды :
Фактический план выполнения SQL
Фактический план выполнения SQL генерируется оптимизатором при выполнении SQL-запроса. Таким образом, в отличие от предполагаемого плана выполнения, вам необходимо выполнить SQL-запрос, чтобы получить его фактический план выполнения.
Фактический план не должен существенно отличаться от расчетного, если табличная статистика была должным образом собрана базовой реляционной базой данных.
Подсказка запроса GATHER_PLAN_STATISTICS
Чтобы указать Oracle хранить фактический план выполнения для данного SQL-запроса, вы можете использовать подсказку GATHER_PLAN_STATISTICS запрос:
Для визуализации фактического плана выполнения вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR To visualize the actual execution plan, you can use DBMS_XPLAN.DISPLAY_CURSOR
И фактический план будет выглядеть так:
Вы также можете использовать подсказку GATHER_PLAN_STATISTICS запроса для проверки плана выполнения SQL, связанного с данным запросом JPQL или API критериев.
Включите СТАТИСТИКУ на уровне подключения к БД
Если вы хотите получить планы выполнения для всех запросов, созданных в рамках данного сеанса, вы можете установить параметр STATISTICS_LEVEL сеанса в ВСЕ :
Это будет иметь тот же эффект, что и установка подсказки GATHER_PLAN_STATISTICS запроса для каждого запроса на выполнение. Таким образом, как и в случае с подсказкой GATHER_PLAN_STATISTICS запроса, вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR для просмотра фактического плана выполнения.
Вам следует сбросить параметр STATISTICS_LEVEL в режим по умолчанию, как только вы закончите сбор интересующих вас планов выполнения. Это очень важно, особенно если вы используете пул соединений, а в соединениях с базой данных отказано.
Вывод
Знание разницы между расчетным и фактическим планом выполнения SQL очень важно при использовании системы реляционных баз данных.
Если вы хотите ускорить определенный SQL-запрос, вам необходимо проверить план выполнения, чтобы определить, что именно вызывает медленное выполнение запроса. Без плана выполнения SQL невозможно определить, что база данных может делать под капотом при выполнении данного SQL-запроса.
Приемы работы с планами выполнения запросов в Oracle
Это как гвоздь в подошве любимого ботинка. Ходить можно, но все чаще ловишь себя на желании остаться на месте или перепоручить дело другим. Мелкие неудобства не только замедляют нашу работу, но и снижают мотивацию, вносят помехи в процесс, снижают качество результата. И если нашелся друг, который научил вас взять молоток и забить этот гвоздь, вы не только будете благодарны ему за помощь, но и сами поможете другим, избавив их от мелкой, но очень раздражающей помехи. Для этого и нужно общаться, делиться не только глубокими и сокровенными знаниями в форумах и на сайтах вроде Хабра, но и своими простыми трюками и «маленькими хитростями»
Как и любой текст, запросы и программы на SQL можно создавать в любом текстовом редакторе. Но если вы профессионал, вы очень много и часто работаете с SQL, то вам уже не будет достаточно наличия подсветки синтаксиса и автоматического переформатирования кода, особенно, если вам приходится переключаться между различными версиями одной СУБД или разными платформами СУБД.
Недавно мне случилось общаться с одним из ведущих профессионалов СУБД Oracle. Он рассказал много интересного про работу с планами выполнения запросов в различных версиях этой СУБД и не постеснялся рассказать всем об используемых им инструментах, приемах и дать немного полезных мелких советов. Я сделал перевод одной из статей в его блоге и хотел бы предложить его вниманию Хабравчан. Несмотря на то, что описанный прием применялся для работы с Oracle, я теперь с успехом применяю тот же подход для MS SQL и Sybase.
Меня зовут Дан Хотка (Dan Hotka). Я директор Oracle ACE. Одной из моих привилегий в этой группе является помощь в распространении информации и полезных технических знаний, связанных с СУБД Oracle. Меня хорошо знают после моих 12 (скоро 14) опубликованных книг и буквально сотен статей. Я регулярно пишу в блоге и собираюсь делать это в дальнейшем. Мы даже могли встречаться на одном из событий или встреч группы пользователей. Я регулярно выступаю на эти темы по всему миру.
Я собираюсь поделиться с вами как техническими знаниями про Oracle, так и тем, как эти знания применяются в решениях Embarcadero.
Я скачал себе «большую тройку» продуктов Embarcadero: Rapid Sql, DBArtisan, DB PowerStudio. Сейчас я хотел бы рассказать о первом впечатлении и некоторых приемах работы с планами выполнения запросов в RapidSQL. (Я установил версию 8.6.1)
Я покажу пару приемчиков для планов выполнения запросов в и вокруг Rapid SQL.
Мне нравится инструмент. Конечно, это прекрасный инструмент, если у вас есть разные типы СУБД различных производителей, поскольку этот инструмент поддерживает около дюжины разных СУБД. Единый интерфейс для освоения всех БД! Мои приемчики относятся к Oracle. Но приемы для инструментов Embarcadero должны сработать вне зависимости от того, к какой СУБД вы подключились.
При просмотре планов выполнения я люблю видеть план выполнения и сам запрос одновременно.
Этого легко достигнуть.
Для начала, загрузите свой SQL запрос в окно редактора ISQL (используя кнопку Open), затем включите кнопку Explain Plan (отмечена в красном круге). Кнопка останется активированной. 
Запустите запрос на выполнение, и появится закладка Query Plan, заполненная планом выполнения.
Поместите курсор мыши на любой из узлов на диаграмме и появится дополнительная полезная информация, относящаяся к этому шагу выполнения из плана запроса!
По умолчанию, Rapid SQL показывает план выполнения в графическом виде. Я вышел из старого мира оптимизации…. Предпочитаю текстовую версию, поэтому нажимаю правую кнопку мыши в окне с планом и выбираю “View as Text”.
Предпочитаю видеть текст запроса и план одновременно.
Это легко сделать. Видите закладки окон ISQL внизу главного окна? Для начала мы должны настроить Rapid SQL, чтобы он выдавал план в отдельном окне.
Нажмите кнопку Options (левый красный кружок) и затем установите опцию ‘Unattached’ для Result window. Это приведет к созданию двух отдельных закладок внизу Rapid SQL, после запуска запроса на выполнение. Просто протащите немного это окно за закладку и появится прямоугольник, куда можно переместить это окно.
Или можно воспользоваться пунктом Tile windows из главного меню программы
И еще: все это так же работает и в DBArtisan — решении для администраторов баз данных.
Как я могу увидеть план выполнения SQL в Oracle?
Я сейчас изучаю индексы баз данных и пытаюсь понять эффективность их использования.
Как мне увидеть план выполнения и где я могу найти в нем информацию о том, использовался ли мой индекс или нет?
5 ответов
Попробуйте использовать этот код, чтобы сначала объяснить, а затем посмотреть план:
Изменить: удалены скобки
План выполнения будет упоминать индекс всякий раз, когда он используется. Просто прочтите план выполнения.
Взгляните на Объясните план. EXPLAIN работает со многими типами баз данных.
Предполагаемый план выполнения SQL
ОБЪЯСНИТЕ ПЛАН
При использовании Oracle, если вы добавляете команду EXPLAIN PLAN FOR к заданному SQL-запросу, база данных сохранит предполагаемый план выполнения в связанном PLAN_TABLE :
Параметр форматирования ALL + OUTLINE позволяет получить более подробную информацию о предполагаемом плане выполнения, чем при использовании параметра форматирования по умолчанию.
Oracle SQL Developer
Если вы установили SQL Developer, вы можете легко получить предполагаемый план выполнения для любого SQL-запроса, не добавляя перед командой EXPLAIN PLAN FOR:
## Фактический план выполнения SQL
Фактический план выполнения SQL создается оптимизатором при выполнении запроса SQL. Итак, в отличие от предполагаемого плана выполнения, вам необходимо выполнить запрос SQL, чтобы получить его фактический план выполнения.
Фактический план не должен существенно отличаться от предполагаемого, если статистика таблицы была правильно собрана базовой реляционной базой данных.
Подсказка запроса GATHER_PLAN_STATISTICS
Чтобы указать Oracle хранить фактический план выполнения для данного SQL-запроса, вы можете использовать подсказку запроса GATHER_PLAN_STATISTICS :
Чтобы визуализировать фактический план выполнения, вы можете использовать DBMS_XPLAN.DISPLAY_CURSOR :
Включить СТАТИСТИКУ для всех запросов
Если вы хотите получить планы выполнения для всех запросов, сгенерированных в данном сеансе, вы можете установить для конфигурации сеанса STATISTICS_LEVEL значение ВСЕ:
Вам следует сбросить настройку STATISTICS_LEVEL на режим по умолчанию, как только вы закончите сбор интересующих вас планов выполнения. Это очень важно, особенно если вы используете пул соединений и соединения с базой данных используются повторно.
Русские Блоги
план выполнения oracle view
План выполнения: описание процесса выполнения или пути доступа оператора запроса в ORACLE. То есть для задачи запроса составьте подробный план того, как выполнить задачу.
Если вы хотите проанализировать проблемы с производительностью определенного SQL, обычно нам нужно сначала посмотреть на план выполнения SQL, чтобы увидеть, есть ли проблемы на каждом этапе выполнения SQL. Понимание плана выполнения стало предпосылкой для оптимизации SQL. Выявляйте проблемы с производительностью с помощью плана выполнения и решайте проблемы, индексируя и изменяя SQL после размещения.
1. Просмотрите план выполнения.
1.1 Настроить автотрассировку
Команда autotrace выглядит следующим образом
Это значение по умолчанию, что означает, что автотрассировка отключена.
SET AUTOTRACE ON EXPLAIN
Показать только план выполнения
SET AUTOTRACE ON STATISTICS
Показывать только статистику исполнения
Содержит 2, 3 контента
SET AUTOTRACE TRACEONLY
Аналогично ON, но не отображает результат выполнения оператора.
1.2 Использование SQL
Добавьте EXPLAIN PLAN FOR перед выполненным sql
1.3 Использование PL / SQL Developer, Navicat, Toad и других клиентских инструментов
Общие клиентские инструменты, такие как PL / SQL Developer, Navicat, Toad, поддерживают просмотр планов объяснения.
Navicat
PL/SQL Developer
2. Как читать план выполнения
2.1 Принцип порядка исполнения
Обычно, судя по длине отступа, сначала выполняется наибольший отступ, если есть2Отступ строки такой же, затем сначала выполните описанное выше.
На рисунке показан план выполнения, просматриваемый инструментом Toad. В Toad четко показан порядок выполнения.
В качестве примера возьмем следующий SQL (запрос на соединение с городом и страной в базе данных sakila)
2.2 Расшифровка полей в плане выполнения
ID: Порядковый номер, но не порядок выполнения. О порядке исполнения судят по отступам.
Operation: Содержание текущей операции.
Rows: Количество элементов текущей операции, Oracle оценивает набор возвращаемых результатов текущей операции.
Cost（CPU）：Значение (стоимость), рассчитанное Oracle для иллюстрации стоимости выполнения SQL.
Time: Oracle оценивает текущее время работы.
При просмотре плана выполнения, помимо самого плана выполнения, вам также необходимо посмотреть на предикаты и статистику. Оценивайте эффективность SQL по общей информации.
2.3 Описание предиката
Access :
Filter：
Основное внимание при доступе с предикатом уделяется рассмотрению условий предиката и правильности используемого пути доступа.
2.4 Описание статистики (статистической информации)
Количество сгенерированных рекурсивных вызовов sql.
Количество блоков, прочитанных из буферного кеша
Количество блоков данных отмены, прочитанных из буферного кеша
Количество блоков, прочитанных с диска
Размер повтора, созданного DML
bytes sent via SQL*Net to client
Количество байтов результатов запроса, отправленных сервером базы данных клиенту запросов через SQL * Net.
bytes received via SQL*Net from client
Количество байтов данных, полученных от клиента через SQL * Net
SQL*Net roundtrips to/from client
Количество сообщений Oracle Net, которые сервер и клиент передают туда и обратно.
Объем сортировки, выполненной в памяти
Объем выполненной сортировки на диске
Количество строк обработанных данных
Recursive Calls：Number of recursive calls generated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。
При выполнении оператора SQL генерируются вызовы других операторов SQL. Эти дополнительные операторы называются «рекурсивными вызовами» или «рекурсивными операторами SQL». Когда мы выполняем вставку, не хватает места для сохранения Для записей строк Oracle динамически выделяет пространство с помощью рекурсивного вызова.
DB Block Gets：Number of times a CURRENT block was requested.
Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the «right now» information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.
DB Block Gets: количество запрошенных блоков данных, которые могут быть удовлетворены в буфере.
Consistent Gets： Number of times a consistent read was requested for a block.
This is how many blocks you processed in «consistent read» mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.
Концепция здесь заключается в том, сколько блоков необходимо обработать в согласованном состоянии чтения при обработке вашей операции. Основная причина этих блоков заключается в том, что в процессе вашего запроса из-за других сеансов, работающих с блоком данных, и Запрашиваемый блок был изменен, но поскольку наш запрос вызывается перед этими изменениями, нам необходимо запросить предварительное изображение блока данных в сегменте отката, чтобы обеспечить согласованность данных. Это обеспечивает последовательные чтения.
Physical Reads：
Total number of data blocks read from disk. This number equals the value of «physical reads direct» plus all reads into buffer cache.
(Физические чтения: после запуска экземпляра количество блоков данных, прочитанных с диска в буферный кэш)
Это количество блоков данных, прочитанных с диска. Основные причины:
(1) Эти блоки не существуют в кеше базы данных
(2) Полное сканирование таблицы
(3) Сортировка дисков
Отношения между этими тремя можно грубо резюмировать следующим образом:
Под логическим чтением понимается количество блоков данных, которые Oracle читает из памяти. Вообще говоря, это «согласованные получает» + «получает блок db». Когда требуемый блок данных не может быть найден в памяти, его необходимо получить с диска, поэтому генерируются «физические чтения».
Обычно мы больше всего заботимся о физических чтениях. Если это значение очень велико, это означает, что необходимо запросить большой объем данных с диска в буферный кэш, что обычно означает, что в системе имеется большое количество операторов SQL для полного сканирования таблицы, что влияет на производительность базы данных. Поэтому старайтесь избегать выполнения оператора полного сканирования таблицы.Для оператора SQL полного сканирования таблицы рекомендуется добавить связанные индексы и оптимизировать оператор SQL для решения этой проблемы.
Что касается физических чтений, существует формула преобразования между тремя параметрами получения блока db и согласованного получения:
Коэффициент использования буфера данных = 1- (физические чтения / (получение блока db + согласованное получение)).
Используйте следующий оператор, чтобы просмотреть частоту попаданий в буфер данных:
Частота совпадений результатов запроса Buffer Cache должна быть выше 90%, в противном случае необходимо увеличить размер буфера данных.
bytes sent via SQL*Net to client:
Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client:
Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips to/from client:
Total number of Oracle Net messages sent to and received from the client.
sorts (memory): сортировка в памяти.
Number of sort operations that were performed completely in memory and did not require any disk writes
You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.
Сортировка (диск): Сортировка по диску.
Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.
Все сортировки сначала выполняются в памяти. Когда слишком много контента для сортировки и не помещается в области сортировки, требуется временное табличное пространство, что приводит к сортировке (диск)
The number of rows processed
Дополнительные сведения см. В интерактивной документации Oracle:Statistics Descriptions
2.5 Динамический анализ
Динамическая выборка (динамическая выборка) предназначена для генерации плана выполнения, а теперь сбора статистики с небольшой скоростью принятия. Из-за низкой частоты дискретизации процесс выборки выполняется быстро, но не точно, и результаты выборки не попадают в словарь данных.
Если в плане выполнения есть следующая подсказка:
Это напоминает пользователю, что текущая технология, используемая CBO, требует от пользователя учитывать эти факторы при анализе плана. Появление этого запроса означает, что в текущей таблице используется динамическая выборка. Таким образом, мы делаем вывод, что эта таблица не анализировалась.
Здесь будут две ситуации:
(1) Если таблица не была проанализирована, то CBO может получить данные анализа с помощью динамической выборки или правильно выполнить план.
(2) Если таблица была проанализирована, но аналитическая информация слишком старая, то CBO не будет использовать динамическую выборку, а будет использовать эти старые данные анализа, что может привести к неправильному плану выполнения.
три,JOINспособ
3.1 hash join
3.2 merge join
3.3 nested loop
Четыре, метод доступа к таблице
4.3 Индексное сканирование