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

Обновлено: 03.07.2024

Узнайте, как получить расчетный и фактический план выполнения SQL при использовании команд Oracle EXPLAIN PLAN ДЛЯ или GATHER_PLAN_STATISTICS.

Вступление

В этой статье мы рассмотрим, как мы можем получить план выполнения SQL в Oracle.

При настройке производительности план выполнения незаменим, так как он позволяет понять операции, выполняемые базой данных при выполнении определенного SQL-запроса.

Каков план выполнения SQL

Как я объяснил в этой статье , SQL описал Что а не то Как , и это одна из основных причин, по которой SQL был самым популярным способом запроса базы данных на протяжении более 40 лет.

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

Полученный план выполнения отправляется Исполнителю, который запускает его и отправляет полученные данные обратно клиенту базы данных.

Этапы обработки и выполнения SQL можно визуализировать на следующей диаграмме.

Предполагаемый план выполнения SQL

Предполагаемый план выполнения генерируется оптимизатором без выполнения SQL-запроса. Вы можете сгенерировать предполагаемый план выполнения из любого клиента SQL, используя ОБЪЯСНИТЬ ПЛАН ДЛЯ или вы можете использовать Oracle SQL Developer для этой задачи.

ОБЪЯСНИТЕ ПЛАН ДЛЯ

При использовании Oracle, если вы добавляете команду ОБЪЯСНИТЬ ПЛАН ДЛЯ к заданному SQL-запросу, база данных сохранит предполагаемый план выполнения в соответствующей таблице PLAN_TABLE :

Чтобы просмотреть предполагаемый план выполнения, вам необходимо использовать DBMS_XPLAN.DISPLAY , как показано в следующем примере:

Параметр ВСЕ +СХЕМА форматирование позволяет получить более подробную информацию о предполагаемом плане выполнения, чем при использовании параметра форматирования по умолчанию.

Предполагаемый план будет выглядеть примерно так:

Обратите внимание, что некоторые столбцы были удалены, чтобы

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

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

1.1 Настроить автотрассировку

Команда autotrace выглядит следующим образом

SET AUTOTRACE OFF

Это значение по умолчанию, что означает, что автотрассировка отключена.

SET AUTOTRACE ON EXPLAIN

Показать только план выполнения

SET AUTOTRACE ON STATISTICS

Показывать только статистику исполнения

SET AUTOTRACE ON

Содержит 2, 3 контента

SET AUTOTRACE TRACEONLY

Аналогично ON, но не отображает результат выполнения оператора.

clip_image001

1.2 Использование SQL

Добавьте EXPLAIN PLAN FOR перед выполненным sql

clip_image002

1.3 Использование PL / SQL Developer, Navicat, Toad и других клиентских инструментов

Общие клиентские инструменты, такие как PL / SQL Developer, Navicat, Toad, поддерживают просмотр планов объяснения.

Navicat

clip_image003

PL/SQL Developer

clip_image004

2. Как читать план выполнения

2.1 Принцип порядка исполнения

Принцип выполнения заказа: сверху вниз, справа налево.
Сверху вниз: обычно в плане выполнения есть несколько узлов. Для узлов одного уровня (или рядом) верхний будет выполняться первым, а нижний - позже.
Справа налево: под определенным узлом имеется несколько дочерних узлов, и выполнение начинается с дочернего узла справа.

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

clip_image005

На рисунке показан план выполнения, просматриваемый инструментом Toad. В Toad четко показан порядок выполнения.

В качестве примера возьмем следующий SQL (запрос на соединение с городом и страной в базе данных sakila)

clip_image006

2.2 Расшифровка полей в плане выполнения

clip_image007

ID: Порядковый номер, но не порядок выполнения. О порядке исполнения судят по отступам.

Operation: Содержание текущей операции.

Rows: Количество элементов текущей операции, Oracle оценивает набор возвращаемых результатов текущей операции.

Cost（CPU）：Значение (стоимость), рассчитанное Oracle для иллюстрации стоимости выполнения SQL.

Time: Oracle оценивает текущее время работы.

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

2.3 Описание предиката

clip_image008

Access :

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

Filter：

  • Осуществляется доступ ко всем данным, а затем ненужные данные отфильтровываются.Этот метод называется фильтром.
  • Значение условия предиката не влияет на путь доступа к данным, а играет только роль фильтрации.

Основное внимание при доступе с предикатом уделяется рассмотрению условий предиката и правильности используемого пути доступа.

2.4 Описание статистики (статистической информации)

clip_image009

Количество сгенерированных рекурсивных вызовов 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

Объем сортировки, выполненной в памяти

Объем выполненной сортировки на диске

Количество строк обработанных данных

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.

Oracle Net - это клей, который склеивает сети Oracle. Он отвечает за взаимодействие между клиентом и сервером и между сервером и клиентом,

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 Динамический анализ

Сбор динамической статистики - это функция оптимизатора Oracle CBO. План выполнения, созданный оптимизатором, рассчитывается по формуле стоимости.Если статистика не собирается в соответствующей таблице данных и используется механизм CBO, будет вызвана динамическая выборка.

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

Если в плане выполнения есть следующая подсказка:

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

Здесь будут две ситуации:

(1) Если таблица не была проанализирована, то CBO может получить данные анализа с помощью динамической выборки или правильно выполнить план.

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

три,JOINспособ

3.1 hash join

3.2 merge join

3.3 nested loop

Четыре, метод доступа к таблице

4.1 Режим доступа к таблице ----> Полное сканирование таблицы

4.2 Режим доступа к таблице ----> доступ к таблице по ROWID

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

Card - кардинальность, количество записей, которое будет сгенерировано каждой строкой плана.

Bytes - байт, общий объём данных, который будет сгенерирован каждой строкой плана.


План запроса (общая информация)


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

Посмотреть план:
sql> select * from table(DBMS_XPLAN.DISPLAY);


Так же можно посмотреть план из plan_table с помощью sql запроса.
Только необходимо задать STATEMENT_ID, что бы как то различать планы если их несколько в таблице plan_table:

-- посмотреть план:
SELECT LPAD(' ', 2 * (level - 1)) ||
DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
INITCAP(pt.operation) ||
DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = 'test_1'
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = 'test_1';



2й способ (в sqlplus)
Чтобы посмотреть план выполнения из sqlplus нужно установить autotrace

sql> set autotrace on
sql> set autotrace off

Ещё возможны опции установки:

SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.


SET AUTOTRACE ON STATISTICS
The AUTOTRACE report shows only the SQL statement execution statistics.


План запроса (дополнительно)


Планы запросов можно вытянуть из V$SQL_PLAN.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.


Вытянуть план запроса по его SQL_ID.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', 0 ));

-- например
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('ck4na28rqh00d', 0 ));

-- нарисует:
SQL_ID ck4na28rqh00d, child number 0
-------------------------------------
SELECT count(*) FROM TSS_TRANSACTION WHERE entry_dt < TO_DATE('20120830
07:57:08', 'YYYYMMDD hh24:mi:ss')

Plan hash value: 804789899


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

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

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

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

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

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

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

полученного в Toad

Из плана видно, что наибольшие значения Cost и Cardinality содержатся во 2-й строке, в которой и надо искать основные проблемы производительности запроса.
Вместе с тем, многолетний опыт оптимизации показывает, что качественный анализ эффективности запроса требует, помимо Cost и Cardinality, рассмотрения других дополнительных показателей:

  • CPU Cost — процессорная стоимость выполнения;
  • IO Cost — стоимость ввода-вывода;
  • Temp Space – показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

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

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

где SQL_ID – это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

Трассировочный файл — это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем M_2013, то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ M_2013.trc. Результат пишется в соответствующую директорию сервера, которая находиться из запроса

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

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Для рассматриваемого выше запроса блок PL/SQL примет вид:

Все строки (кроме текста запроса) являются стандартными.

Далее запуск запрос, который выдает на экран текст рекомендаций:

Для работы SQLTUNE необходимо как минимум из под SYSTEM выдать права на работу с SQLTUNE схеме, в которой запускается PL/SQL блок. Например, для выдачи прав на схему HIST выдается GRANT ADVISOR TO HIST;

В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.

Анализ плана выполнения запроса.

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса

  1. При анализе план просматриваетcя снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.
  2. Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.
  3. Помимо поиска больших Cost и CPU Cost в строках плана следует просматривать первый столбец Operation плана на предмет наличия в нем HASH JOIN. Соединение по HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, которые существенно затормозят работу запроса. В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт USE_NL, приводящий к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.
  4. Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL — для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN — для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.
  5. Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц.
    Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

Оптимизация запроса

После анализа плана выполнения запроса осуществляется его оптимизация.
Оптимизация запроса предполагает удаление причин неэффективности запроса, среди которых наиболее весомыми являются:

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

Неэффективная статистика.

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

  1. Устаревшая статистика. Время последнего сбора статистики определяется значением поля Last_Analyzed для таблиц и индексов, которое находиться из Oracle таблиц ALL_TABLES (DBA_TABLES) и ALL_INDEXES (DBA_INDEXES) соответственно. Oracle ежедневно в определенные часы в рабочие дни и в определенные часы в выходные сам собирает статистику по таблицам. Но для этого DML операции с таблицей должны привести к изменению не менее 10% строк таблицы. Однако, мне приходилось сталкиваться с ситуацией, когда в течение дня таблица неоднократно и существенно меняет число строк или таблица столь большая, что 10% изменений наступает через длительное время. В этом случае приходилось обновлять статистику, используя процедуры сбора статистики внутри пакетов, а ряде случае использовать JOB, запускающийся в определенные часы для анализа и обновления статистики.
    Статистика по таблице и индексу (на примере таблицы AGREEMENT и индекса X_AGREEMENT в схеме HIST) обновляется соответственно процедурами:
  2. для таблицы:
  3. для индекса:

где число 10 в процедуре указывает на процент сбора статистики. С учетом времени сбора статистики и числа строк в таблице (индексе) были выработаны рекомендации для таблиц (индексов) по проценту сбора статистики: если число строк более 100 млн. процент сбора устанавливать 2 -5, при числе строк с 10 млн. до 100 млн. процент сбора устанавливать 5-10, менее 10 млн. процент сбора устанавливать 20 -100. При этом, чем выше процент сбора, тем лучше, однако, при этом растет и может быть существенным время сбора статистики.

Для таблиц процент сбора статистики (на примере таблицы AGREEMENT в схеме HIST) вычисляется запросом:

Процент сбора статистики по индексу находиться по запросу

Необходимо пересобрать статистику по таблице или индексу с плохой статистикой.

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

Проблемы с индексами

Проблемы с индексами в плане выполнения проявляются при наличии в столбце Options значений FULL, FULL SCAN, FAST FULL SCAN и SKIP SCAN в силу следующих причин:

  1. Отсутствие нужного индекса. Требуемое действие — создать новый индекс;
  2. Индекс имеется, но он неэффективно построен. Причинами неэффективности индекса могут быть:
    — Малая селективность столбца, на котором построен индекс, т.е. в столбце много одинаковых значений, мало уникальных значений. Решение в данной ситуации — убрать индекс из таблицы или столбец, на основе которого построен индекс, ввести в составной индекс.
    — Столбец селективный, но он входит в составной индекс, в котором этом столбец не является первым (ведущим) в индексе. Решение – сделать этот столбец ведущим или создать новый индекс, где столбец будет ведущим;
  3. Построен эффективный индекс, но он работает не эффективно в силу следующих причин:
    — Индекс заблокирован от использования. Блокируют использование индекса следующие операции над столбцом, по которому используется индекс: SUBSTR, NVL, DECODE, TO_CHAR,TRUNC,TRIM, ||конкатенация, + цифра к цифровому полю и т.д.
    Решение – модифицировать запрос, освободиться от блокирующих операций или создать индекс по функции, блокирующей индекс.
    — Не собрана или неактуальная статистика по индексу. Решение – собрать статистику по индексу запуском процедуры, указанной выше.
    — Имеется хинт, блокирующий работу индекса, например NO_INDEX.
    — Неэффективно настроены параметры инициализации базы данных БД (особенно отвечающие за эффективную работу индексов, например, optimizer_index_caching и optimizer_index_cost_adj). По моему опыту использования Oracle 10g и 11g эффективность работы индексов повышалась, если optimizer_index_caching=95 и optimizer_index_cost_adj=1.
  4. Имеются сильные индексы, но они соперничают между собой.
    Это происходит тогда, когда в условии where имеется строка, в которой столбец одной таблицы равен столбцу другой таблицы. При этом на обоих столбцах построены сильные или уникальные индексы. Например, в условии Where имеется строка AND A.ISN=B.ISN. При этом оба столбца ISN разных таблиц имеют уникальные индексы. Однако, эффективно может работать индекс только одного столбца (левого или правого в равенстве). Индекс другого столбца, в лучшем случае, даст FAST FULL SCAN. В этой ситуации, чтобы эффективно заработали оба индекса, потребуется вести дополнительное условие для одного из столбцов.
  5. Индекс имеет большой фактор кластеризации CLUSTERING_FACTOR.
    По каждому индексу Oracle вычисляет фактор кластеризации (ФК), определяющий число перемещений от одного блока к другому в таблице при выборе индексом строк из таблицы. Минимальное значение ФК равно числу блоков таблицы, максимальное — числу строк в таблице. CLUSTERING_FACTOR определяется по запросу:

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

Проблемы с хинтами в запросе

Проблемы с хинтами могут быть следующие:

  1. Неэффективный хинт. Он может привести к существенному снижению производительности. Причины возникновения не эффективности хинтов:
    — хинт был написан, когда БД работала на 9-ом Oracle, при переходе на Oracle 10g и выше хинт стал тормозом (это могут быть хинты Rule, Leading и др.). Leading –мощный хинт, но при переходе на другую версию Oracle в некоторых случаях приводит в резкому снижению производительности и перед применение этих хинтов необходимо учитывать вероятность изменения со временем статистики системы и ее объектов (таблиц и индексов), используемых в запросе;
    — в хинте USE_NL содержится не полный перечень алиасов;
    — в составном хинте используется неправильный порядок следования хинтов, в результате чего хинты блокирую эффективную работу друг. Например, хинт Leading полностью игнорируются при использовании двух или более конфликтующих подсказок Leading или при указании в нем более одной таблицы.
    — хинт написан давно, после чего была модификация запроса (например, отсутствует или изменился индекс, указанный в хинте).
  2. В запросе отсутствует хинт, который бы повысил эффективность работы запроса. В ряде случаем наличие хинта повышает эффективность запроса и обеспечивает стабилизацию планов выполнения (например, при изменении статистики).
  3. При создании хинта в запросе есть ряд рекомендаций:
    — В хинте INDEX могут быть перечислены несколько индексов. Оптимизатор сам выберет соответствующий индекс. Можно поставить хинт NO_INDEX, если надо заблокировать использование какого-то индекса.
    — При наличии Distinct в запросе Distinct ставиться после хинта (т.е. хинт всегда идет после Select).
    — Наиболее эффективные и часто используемыми являются хинты: Ordered, Leading, Index , No_Index, Index_FFS, Index_Join, Use_NL, Use_Hash, Use_Merge, First_Rows(n), Parallel, Use_Concat, And_Equal, Hash_Aj и другие. При этом, например, индекс Index_FFS кроме быстрого полного сканирования индекса позволяет ему выполняться параллельно, в силу чего можно получить существенный выигрыш в производительности. Пример такого использования для секционированной таблицы где T-алиас таблицы.
    — Изменение параметров инициализации базы данных в пределах запроса позволяет сделать хинт /*+ opt_param('Параметр инициализацци' N) */ , например, /*+ opt_param('optimizer_index_caching' 10) */. Данный хинт используется для проверки производительности работы запроса в случае, когда запрос разрабатывается или тестируется на базе с одним значением параметров инициализации, а работает на базе с другими значениями.

Замечание. В некоторых случаях, когда хинт неэффективный, но заменить его оперативно в запросе не представляется возможным (например, чужая разработка), имеется возможность, не меняя рабочий запрос в программном модуле, заменить хинт (хинты) в запросе, а также в его подзапросах, на эффективный хинт (хинты). Это прием — подмена хинтов (который известен, как использование хранимых шаблонов Stored Outlines). Но такая подмена должна быть временным решением до момента корректировки запроса, поскольку постоянная подмена хинта может привести к некоторому снижению производительности запроса.

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