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

1.

CУБД Oracle
План выполнения запроса

2.

План выполнения запроса
Позволяет понять:
• каким образом выполняется запрос;
• какие запрос использует индексы и использует ли их
вообще;
• какие методы доступа применяет оптимизатор Oracle
при выполнении SQL запроса.
2

3.

Зачем нужен
план выполнения запроса
1. Позволяет проверить насколько оптимально будет
выполняться SQL запрос.
2. Понять, какие необходимо принять меры для его
оптимизации:
• изменить текста SQL запроса;
• создать дополнительные индексы;
• выполнить иные действия по настройке SQL запроса
или базы данных.
3

4.

Команда EXPLAIN PLAN FOR
Проанализировать SQL запросы можно при помощи
команды explain plan for.
EXPLAIN PLAN FOR
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_TYPE='TABLE';
4

5.

Команда EXPLAIN PLAN FOR
просмотр результатов
Для просмотра плана выполнения запроса существует
системный PL/SQL пакет Oracle dbms_xplan
SELECT * FROM TABLE(dbms_xplan.display);
5

6.

Команда EXPLAIN PLAN FOR
просмотр результатов
6

7.

Команда EXPLAIN PLAN FOR
просмотр результатов
Также существует множество инструментов от сторонних
производителей для анализа этой информации, такие
инструменты встроены в такие продукты как SQL
Navigator, Toad, PL/SQL Developer и др.,
7

8.

Методы доступа
к данным таблиц Oracle
Full Table Scan (FTS)
Полное сканирование таблицы при поиске.
Часто это указание на проблему отсутствующего индекса
таблицы или его не использования по каким либо
причинам, например, не собрана статистика для
оптимизатора Oracle или собрана давно и не соответствует
текущему распределению данных в таблице.
8

9.

Методы доступа
к данным таблиц Oracle
Full Table Scan (FTS)
Не всегда Full Table Scan это проблема. Например, для
таблиц с небольшим количеством строк Full Table
Scan может быть гораздо быстрее, чем индексный поиск.
Все зависит от данных таблицы.
9

10.

Методы доступа
к данным таблиц Oracle
INDEX LOOKUP
Поиск значения в таблице по существующему индексу.
Существует несколько разновидностей индексного поиска,
которые будут рассмотрены ниже:
- index unique scan
- index range scan
- index full scan
- index fast full scan
- index skip scan
10

11.

Методы доступа
к данным таблиц Oracle
INDEX UNIQUE SCAN
• Метод поиска единственного значения через
уникальный индекс.
• Всегда возвращается одно значение.
11

12.

Методы доступа
к данным таблиц Oracle
INDEX RANGE SCAN
• Метод применяется для поиска множества значений при
помощи индекса.
• Используется при поиске диапазонов значений при
помощи SQL операторов between, >, <, <>, >=, <=.
• Для неуникального индекса может выдавать множество
значений и для условия равенства, например
COL_NAME=3.
12

13.

Методы доступа
к данным таблиц Oracle
INDEX FULL SCAN
• Метод выполняет полное сканирование индекса.
• Применяется в том случае, если все необходимые данные
могут быть считаны из индекса без обращения к данным
таблицы.
• Возвращает считанные данные в отсортированном виде.
Может быть неэффективен, так как всегда применяет
одиночное чтение блоков индекса для обеспечения
сортировки.
• Может применяться только тогда, когда все столбцы,
участвующие в запросе, присутствуют в индексе и имеют
ограничение NOT NULL.
13

14.

Методы доступа
к данным таблиц Oracle
INDEX FAST FULL SCAN
• Выполняется полное сканирование индекса методом
быстрого множественного чтения листовых блоков
индекса в сегменте данных.
• Такое быстрое чтение может выполняться сразу
несколькими параллельными процессами.
• Применяется в том случае, если все необходимые данные
могут быть считаны из индекса без обращения к данным
таблицы.
• Не осуществляет сортировку считанных данных.
• Может применяться только тогда, когда все столбцы,
участвующие в запросе, присутствуют в индексе и имеют
ограничение NOT NULL.
14

15.

Методы доступа
к данным таблиц Oracle
INDEX SKIP SCAN
Метод доступа к составному индексу. Появился в Oracle
начиная с девятой версии. В более ранних версиях Oracle
не использовал составной индекс, если в условиях запроса
не был в обязательном порядке указан лидирующий
столбец составного индекса, что приводило к
рекомендации использовать в составном индексе первым
наиболее часто используемый столбец в SQL запросах.
Метод доступа Index Skip Scan снял эти ограничения,
теперь составной индекс может быть использован и в
случае отсутствия в условиях запроса лидирующего
столбца составного индекса.
15

16.

Методы доступа
к данным таблиц Oracle
ROWID
• Самый быстрый метод доступа к данным таблицы по
внутреннему системному идентификатору записи
таблицы (фактически это адрес строки).
• Обычно применяется после индексного поиска или при
явном указании в SQL запросе значения ROWID строки
данных таблицы.
16

17.

Методы доступа
к данным таблиц Oracle
JOINS
Join или Соединение это предикат, который объединяет
данные из нескольких источников данных (таблиц и
представлений).
Существует 3 вида соединений:
- Sort Merge Join (SMJ)
- Nested Loops (NL)
- Hash Join (HJ)
17

18.

Методы доступа
к данным таблиц Oracle
SORT MERGE JOIN (SMJ)
• Выполняется соединение данных нескольких источников
при помощи предварительной сортировки данных
исходных таблиц.
• Часто очень неэффективный алгоритм из-за
потенциально больших накладных расходов на
сортировку данных перед выполнением соединения.
18

19.

Методы доступа
к данным таблиц Oracle
NESTED LOOPS (NL)
• Метод соединения данных вложенными циклами.
• Наиболее общий алгоритм соединения данных из
нескольких внешних источников.
• Сначала сканируется источник 1, затем для каждой
найденной записи источника 1 в цикле ищется
удовлетворяющий условию список строк из источника 2,
затем организуется цикл по источнику 3 и т. д.
• В случае наличия индексов осуществляется индексный
поиск данных в источниках по заданным условиям,
иначе выполняется полное сканирование таблицисточников.
• В случае отсутствия индексов время выполнения
соединения может оказаться неприемлемо высоким. 19

20.

Методы доступа
к данным таблиц Oracle
HASH JOIN (HJ)
• При выполнении соединения предварительно строится
хэш-массив согласно условиям поиска.
• Hash Join является самым быстрым алгоритмом
соединения больших таблиц, появился начиная с версии
Oracle 7.3, однако не является универсальным, т.е. не
может быть использован для любых условий
соединений.
20

21.

Методы доступа
к данным таблиц Oracle
CARTESIAN PRODUCT (декартово произведение)
• Возникает обычно в том случае, если для нескольких
источников не указаны никакие условия соединения.
• Каждая строка из источника 1 соединяется со всеми
строками источника 2, затем то же самое для следующей
строки и так далее.
• Декартово произведение способно генерировать
огромное количество строк и как правило является
ошибкой кодирования. Однако иногда декартово
произведение может применяться намеренно.
21

22.

Методы доступа
к данным таблиц Oracle
SORT
Показываются операции сортировки данных, которые
предполагается выполнить.
Сортировка выполняется в следующих случаях:
- order by
- group by
- sort merge join
22

23.

Методы доступа
к данным таблиц Oracle
SORT
В случаях, если сортировка выполнена в результате
особенностей доступа к данным, то в плане выполнения
указывается, что сортировка не потребовалась.
Сортировки очень дорогие операции по используемым
ресурсам. Если данные для сортировки не умещаются в
оперативной памяти буферного кэша, то они помещаются
на диск.
23

24.

Методы доступа
к данным таблиц Oracle
FILTER
Показывает наложение фильтра на полученную выборку
по некоторым условиям запроса.
24

25.

Методы доступа
к данным таблиц Oracle
VIEW
Показывает использование в SQL запросе представления
Oracle.
25

26.

Реальный план запроса
Оценка плана запроса не всегда совпадает с реальным при
его исполнении.
Чтобы посмотреть реальный план запроса надо:
• выполнить запрос;
• найти системный идентификатор этого запроса;
• по системному идентификатору найти реальный план
запроса при его выполнении.
26

27.

Реальный план запроса
Пример:
select *
from employees
where rownum < 10
SELECT sql_id, sql_text, hash_value, plan_hash_value
FROM v$sql
WHERE lower(sql_text) LIKE lower('%where rownum < 10%')
AND lower(sql_text) NOT LIKE '%v$sql%';
27

28.

Реальный план запроса
Пример (продолжение):
SELECT * FROM TABLE(dbms_xplan.display_cursor(
'd4rsc7q1zmct1',
0,'basic allstats +cost +predicate +note'));
28

29.

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

30.

Режимы работы оптимизатора
Rule-based optimizer - учитывает только
фиксированный ранг ( приоритет) способов доступа
к данным. Обладает существенными недостатками за основу берется предположение о статичности
базы данных, не умеет пользоваться Bitmap
индексами. Использовался в ранних версиях
ORACLE.
Cost-based optimizer — оптимизатор, основанный
на анализе затрат системы. Для этого
оптимизатора при выборе метода доступа также
учитывается ранг различных путей доступа к
данным. Однако в первую очередь он основан на
30
хранимой внутренней статистике.

31.

Управление работой оптимизатора
Оптимизатор управляется параметром optimizer_mode,
который можно задавать на уровне сессии или на уровне
экземпляра. Он может принимать следующие значения:
RULE - RBO, используется оптимизация, основанная на
анализе правил.
ALL_ROWS - CBO, используется оптимизация, основанная на
анализе затрат для минимизации общего количества строк,
обрабатываемых системой за единицу времени.
FIRST_ROWS - CBO, используется оптимизация, при которой
из всех возможных оптимальных по стоимости планов
выбирается тот, который наиболее быстро возвращает первые
строки.
31

32.

Управление работой оптимизатора
(продолжение)
CHOOSE - при указании этого значения будет выбрана
стоимостная оптимизация (CBO), основанная на анализе
затрат.
FIRST_ROWS _1, FIRST_ROWS _10, FIRST_ROWS _1000 - при
использовании first_rows Oracle вычисляет стоимость
выполнения всего оператора, потом выбирает оптимальный
план, при использовании first_rows_n вычисляет стоимость
получения первых n строк, а стоимость выполнения всего
оператора не вычисляется.
32

33.

Успехов в освоении курса!
Савченко Наталья Александровна
[email protected]
www.edu.gubkin.ru