Особенности параллельного выполнения sql в Oracle
Этот перевод делал в основном для себя, не утруждаясь коррекцией и стилем, за сим не обессудьте. Выражения с большой буквы (как то Parallel Slave Set или Data Flow Operation) считал за термины и не переводил либо давал перевод рядом в скобках, дабы избежать путаницы и искажения. Форматирование автора по возможности сохранено.
Этот перевод делал в основном для себя, не утруждаясь коррекцией и стилем, за сим не обессудьте. Выражения с большой буквы (как то Parallel Slave Set или Data Flow Operation) считал за термины и не переводил либо давал перевод рядом в скобках, дабы избежать путаницы и искажения. Форматирование автора по возможности сохранено.
Оригинал тут:
Для параллельного
выполнения, требования должны быть
выполнены, по порядку:
1. Эффективный
Parallel Execution план
2. Достаточные
ресурсы доступны, в процессе выполнения
3. Отсутствие
существенных проблем распределения
Parallel Slaves (параллельных исполнителей).
Рассмотрим
по-порядку:
1.
Эффективный Parallel Execution план
Зачастую,
Parallel Execution считается "серебряной
пулей", способной решить проблемы
медленных запросов, выполняющихся в
один поток. Но, если последовательный
план не эффективен (например, неверный
порядок соединений или неверны сами
соединения или способы доступа), Parallel
Execution план будет также
неэффективен. Прежде всего, важно,
понимать каким должен быть эффективный
план, после этого может быть использован
Parallel Execution, если он применим для запроса.
Аспекты, что
применимы к последовательному выполнению,
также применимы и к Parallel Execution, но имеется
ряд особенностей:
Parallel Execution
Forced to
Serial
В некоторых
случаях Оракл в процессе разбора
оператора определяет, что он не может
использовать Parallel
Executions, хотя уже начал
строить Parallel Execution
план. Одна из основных причин -
использование пользовательских PL/SQL
функций, для которых не задействовано
параллельное выполнение, хотя я
встречается и в других ситуациях. Такое
поведение легко определить по плану –
он содержит одну или несколько операций
PX COORDINATOR FORCED
SERIAL:
------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR FORCED SERIAL| | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH |
|* 6 | HASH JOIN BUFFERED | | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | |
| 11 | PX RECEIVE | | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | T2 | Q1,01 | PCWP | |
------------------------------------------------------------------------------
Будьте
внимательней, если вы видите операцию
PX
COORDINATOR
FORCED
SERIAL -
хоть план
выглядит как параллельный, оракл выполнит
его последовательно. Основная проблема
здесь, в том, что оптимизатор учитывает
сокращение стоимости актуальной для
параллельного выполнения. Например,
стоимость full
table
scan
при параллельности выглядит дешевле
доступа по индексу, но учитывая, что
запрос выполнится последовательно,
очень возможно, что это будет не лучший
выбор.
По
крайней мере, если выполнение пошло
последовательно, потенциальный всплеск
дополнительных операций блокировки
(на примере выше - это HASH
JOIN BUFFERED)
удастся избежать.
Объясню
природу дополнительных операций
блокировки ниже.
Additional Blocking Operations (Дополнительные операции блокировки)
При
последовательном плане выполнения,
ровно 1 процесс работает над его
исполнением и рекурсивно вызывает
функции, соответствующие тем или иным
операциям execution
plan.
Tanel Poder наглядно раскрыл сей
вопрос в своем блоге
(http://blog.tanelpoder.com/2008/06/15/advanced-oracle-troubleshooting-guide-part-6-understanding-oracle-execution-plans-with-os_explain/).
В случае
параллельного выполнения, вещи выглядят
совсем по-другому. Оракл использует
модель Consumer/Producer
для не тривиальных Parallel
Executions планов, таким
образом, 2 сета Parallel Slaves
будут работать в одно и тоже время на
различных связанных операций единой
«Data Flow
Operation» (DFO
будет описана позже). Вследствие модели
Consumer/Producer
происходит, что оба Parallel
Slave Sets заняты
(один извлекает(produce),
другой использует(consume)
данные), но данные, соответственно, плану
выполнения, будут использованы следующей
операцией. Если следующая операция, по
плану, должна выполняться
отдельным Parallel Slave
Set (это можно увидеть из
TQ колонки плана), и нет
свободного слейв сета/процесса, который
может обработать данные, в таких случаях
ораклу требуется выполнить sync
points(точки синхронизации,
или операции блокировки), где данные
должны быть «запаркованы», до того
времени, как один из слейв сетов обводиться
для дальнейшей обработки данных.
В
текущих релизах Oracle,
можно просто определить «дополнительные
операции блокировки» в плане выполнения.
Это отдельные операции (BUFFER
SORT
не путайте с обычным BUFFER
SORT,
который также встречается в последовательном
плане) или одну их существующих операций,
дополненную опцией BUFFERED,
например HASH
JOIN
BUFFERED.
Во-первых:
иногда, причина добавления «дополнительных
блокирующих операций» не всегда
однозначна ясна. Простой пример, когда
очевидно, что буферизация данных перед
возвратом на клиент является излишней,
но Оракл все равно HASH
JOIN
превращает в HASH
JOIN
BUFFERED.
-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | |
-------------------------------------------------------------------------
Рассмотрим шаг 3 – это
последняя операция, перед тем как данные
будут отправлены Coordinator
процессу для возвращения результирующего
набора клиенту. Почему выбрано
буферизировать весь результирующий
набор, возможно записав его в TEMP
и перечитав перед возвращением клиенту
?
Похоже, что в общей
реализации создается параллельный план
выполнения, с допущением, что для
последнего шага потенциальный отдельный
Parallel Slave
Set, который будет
задействован, потребует собрать данные
для перераспределения. К сожалению,
такое поведение нередко вызывает лишние
операции блокировки, как показано выше.
Обратите внимание, что
вы можете повлиять на это, изменив способ
распределения Parallel
соединения. Вернемся к этому позже.
Во-вторых: если Вы
знаете, как определяется порядок операций
при последовательном плане выполнения,
вы будете удивлены, что при Consumer / Producer
модели, наряду с операциями буферизации,
могут быть сценарии при которых обычный
порядок выполнения не работает. Взгляните
на следующий план. Согласно правилам
выполнения, будет эффективно начать с
ID=2 (HASH JOIN,
соответствующий ID=1 ,будет
строить hash таблицу на
данных T3).
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL | T3 |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL| T1 |
| 5 | TABLE ACCESS FULL| T1 |
------------------------------------
Но если смотреть на
соответствующий параллельный план,
выполнение не стартует на ID=7
(который соответствует шагу 2 в
последовательном плане ),а на шаге 14
(который соответствует 4 последовательного
плана), потому что Оракл не может
использовать больше чем 2 активных
Parallel Slave
Set для Data
Flow Operation.
Это операция может быть начата, когда
одна из 2-х других закончится. Это можно
пронаблюдать используя Real
Time SQL
Monitoring из SQL
Tuning pack.---------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10004 | Q1,04 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | Q1,04 | PCWP | | | 4 | PX RECEIVE | | Q1,04 | PCWP | | | 5 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | Q1,02 | PCWC | | | 7 | TABLE ACCESS FULL | T3 | Q1,02 | PCWP | | | 8 | PX RECEIVE | | Q1,04 | PCWP | | | 9 | PX SEND HASH | :TQ10003 | Q1,03 | P->P | HASH | |* 10 | HASH JOIN BUFFERED | | Q1,03 | PCWP | | | 11 | PX RECEIVE | | Q1,03 | PCWP | | | 12 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | | 13 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 14 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | | 15 | PX RECEIVE | | Q1,03 | PCWP | | | 16 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | | 17 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 18 | TABLE ACCESS FULL| T1 | Q1,01 | PCWP | | ----------------------------------------------------------------------------
Последнее и наиболее
важное: поскольку Ораклу потребуется
буферизировать или «парковать»
обработанные данные, это может оказать
серьезное влияние на быстродействие
выполнения. Если кол-во данных достаточно
велико, и оракл не может их обработать
в памяти, он будет писать во временное
табличное пространство, только чтобы
перечитать и отправить Parallel
Slave, который дальше будет
обрабатывать данные. Также если операция
может быть проведена в памяти, потребление
PGA может быть значительным.
Таким образом, в плохом
раскладе, Parallel Execution
план может потратить огромное кол-во
времени на запись и чтение данных с
диска без видимой причины.
Parallel Distribution Methods (Методы Параллельного Распределения )
Если вы определили, что
параллельный план выполнения тратит
значительное количество времени в
буферных операциях записи и чтения
данных из и во временное пространство
на диске, то вы можете на это повлиять,
изменив метод параллельного распределения
операций соединения. Изменяя способ
соединения данных, Вы контролируете,
как Oracle комбинирует операции, которые
будут выполняться в том же или в другом
Parallel Slave
сете (присоединения плюс доступ к данным)
потенциально избегая излишнюю
необходимость перераспределения данных.
Если соединение и одна или несколько
операций доступа к данным будут выполнятся
одним и тем же Parallel Slave
сетом, в зависимости от плана выполнения,
при этом возможно не потребуются
блокировать или «парковать» данные.
В принципе есть 3
распространенных способа, в которых
Oracle может обрабатывать
параллельно HASH соединения (hash, broadcast, partition-wise):
· hash: HASH
JOIN сам выполняется
отдельным Parallel Slave
Set и оба столбца соединения
распределены по HASH на
ключах соединения. В этом случае оба
столбца соединения должны быть
перераспределены на основе ключей
соединения и это выполняется отдельным
слейв сетом (за исключением случая,
когда ряд соединения уже перераспределен
по нужному критерию дочерней операцией,
что бывает при соединении нескольких
таблиц по одному и тому же ключу).
Поскольку это операция требует два
Parallel slave
сета ( один выполняет соединение, другой
дочернюю операцию), обычно используется
операция блокировки (BUFFERED
JOIN/BUFFER
SORT), в зависимости от
оставшихся шагов плана выполнения.
Следует отметить, если присутствует
родительская блокирующая операция как
HASH GROUP BY или SORT AGGREGATE, которая может быть
выполнена этим же Parallel
Slave Set, то
искусственная операция блокировки не
требуется.
Обе фазы (фаза построения
хэш таблицы и фаза поиска соответствующих
строк второй колонки) фазы соединения
похожи на рис.1.
Пока оператор выполняется это можно посмотреть в (G)V$PX_SESSION, например, когда для каждой DFO представленном отдельным SERVER_GROUP (1 или 2 нашем случае), разный REQ_DEGREE будет, показана с соответствующим отличающимся количеством Parallel Slaves.
Давайте посмотрим на другой пример, когда несколько DFO используются, но не все они активны одновременно:
Рис. 1: Hash
Distribution Build Phase
Рис. 2: Hash
Distribution
Probe
Phase
· broadcast: HASH JOIN
выполняется вместе с одним из источников
соединения. Здесь данные не распределяются
по ключу соединения, но каждый Parallel
Slave обрабатывает данные
получаемые от дочерней операции (обычно
это Parallel Full
Table Scan),
другой столбец соединения будет
обрабатываться всеми Parallel
Slave Set
выполняющими соединение. Следует
отметить, что обработка данных эффективно
разделяется на количество целевых
Parallel Slave’s,
что соответствует Degree of
Parallelism(DOP)
(кол-ву параллелей) выполняющих эту
операцию / DFO (Data Flow Operation). Т.о. большой
столбец соединения в комбинации с
большой степенью DOP(Degree
of Parallelism)
приведет к тому, что огромное количество
данных распределится между двумя
Parallel Slave
Sets – если это первый
столбец соединения HASH
JOIN, который используется
для построения hash таблицы,
то каждый Parallel Slave
будет строить hash таблицу
для всех строк первого
столбца соединения.
Обычно, эта операция
наиболее эффективна, когда первый
столбец соединения мал относительно
второго и поэтому будет транслироваться
в Parallel Slave
Set выполняющий соединение.
То, что фаза строительства хэш-таблицы
для HASH JOIN
- это операция блокирования, в данном
случае, не существенно, т.к. оба Parallel
Slave Sets
активны в этом случае – для небольшого
столбца соединения операция BROADCAST
может быть выполнена последовательно
Coordinator процессом. После
того, как хэш-таблица построена, только
один Parallel Slave Set выполняет соединение с
вторым столбцом и, следовательно, другая
операция может выполняться вторым
Parallel Slave Set, избегая операций блокирования
для этого случая соединения. Учтите,
что также можно транслировать(broadcast)
второй столбец соединения, но это
означает что оба Parallel Slave Sets активны,
хотя HASH JOIN
не является блокирующей операцией (он
может передавать результаты в родительскую
операцию до своего завершения), но опять
же, может потребоваться аномальная
блокирующая операция – если вам не
повезет и эти BROADCASTED(транслируемые)
данные большого объёма будут «паркованы»(
обычно представляется BUFFERED
(BUFFER SORT)
операцией), таким образом транслирование
(broadcast) второго столбца,
обычно плохая идея.
Build Phase
(фаза построения) такой операции, как
правило, выглядит следующим образом:
Рис. 3: Broadcast
Distribution Build Phase
Probe фаза
может быть выполнена одним сетом
обработчиков (slave set),
он производит двойную обработку данных
– как и Parallel Full Table Scan, так и операция
соединения (рис. 4).
Рис. 4:
Broadcast
Distribution
Probe
Phase
· partition-wise: Если оба столбца
соединения равно-партиционированы
(equi-partitioned ) по ключу соединения – так
называемый полный "Partition-Wise Join"
может быть выполнен без распределения
данных, таким образом операция полностью
может быть выполнена одним Parallel
Slave Set (
соединение и обработка столбцов
соединения). Стоит заметить, что здесь
возможны варианты, так называемый
«Partial Partition-Wise Join», где один столбец
объединения будет распределен по
партициям другого столбца соединения,
но так как один из столбцов источников
должен быть распределен снова, это
похоже на вариант broadcast’а
(рассеивания) относительно блокировок,
разница только в том, что тут не требуется
дублирование данных как в операции
broadcast’а, и следовательно,
должен быть более эффективен при
соединении больших объемов строк.
Full Partition-Wise соединение
– самый эффективный способ соединения
данных в Oracle, в частности
если он выполняется параллельно (он
может выполнен и последовательно, в
отличие от «Partial»
(частичного) варианта, требующего
Parallel Execution
для перераспределения данных
присоединяемого столбца), и еще больше
актуален в Parallel Execution
по RAC узлам, потому что в
этом случае нет данных требующих
перераспределения для распараллеливания
между экземплярами.
Чего не видно на картинках
снизу, является тот факт, что Full
Partition-Wise Join может
обрабатываться на уровне партиций, это
означает что партиция первого столбца
соединения соединяется с соответствующей
второго столбца соединения, и когда это
соединения выполнится процесс переходит
к следующей паре партиций. Таким образом,
каждый Parallel Slave
выполняет фазу построения для партиции,
затем переходит к фазе считывания
соответствующей партиции другого
столбца объединения. Это означает, что
при Partition-Wise Join требуется память только
на построение хэш таблиц для партиций,
которые одновременно обрабатываются,
что соотносится со степенью параллелизма
данной операции, не имеет значения
сколько партиций всего обработается,
т.е. не зависит от общего размера
объединения.
Это является ключевым
отличием от остальных Parallel Join операций
(включая Partial Partition-Wise Join), где Parallel Slaves
требуется построить хэш таблицы, которые
полностью содержат столбцы объединения,
что значит размер хэш таблицы зависит
от размера столбцов объединения. Обратите
внимание также отличается и последовательный
(не параллельный) Partition-Wise
Join.
Фаза построения такой
операции указана на рис.5
Рис 5:
Распределение партиций Buld
Phase
(Фазы Построения)
В принципе Probe
Phase (фаза Поиска) выглядит
аналогично, и т.к. активен только один
Slave Set, другая
операция соединения может выполняться
другим Slave Set
(рис.6).
Рис. 6:
Распределение партиций Probe
Phase (Фазы Поиска)
Другие методы соединения
Стоит заметить, что те
же самые принципы применяются и для
MERGE JOIN, но для MERGE соединения HASH
распределение и Partition-Wise соединения
поддерживаются только для Equi-Joins,
в то время как для не Equi-Joins
поддерживается только BROADCAST распределение.
Более того, независимо какой из двух
столбцов соединения первый или второй,
так как оба источника должны быть в
определенном порядке, перед тем как они
будут соединены. Это означает, что
требуется отдельная SORT
операция на обоих столбцах соединения,
делая MERGE JOIN эффективным только, если
хотя бы один из столбцов соединения уже
отсортирован по ключам соединения,
например с помощью индекса. Хотя
технически Oracle поддерживает
доступ к первому столбцу MERGE
соединения без дополнительной операции
сортировки.
NESTED LOOP
JOIN в принципе поддерживает
только broadcast распределение
ведущего столбца, но обычно происходит
без всякого распределения, если второй
столбец индексирован или использует
кластерный способ доступа ( на основе
кластерных таблиц) , что является
преимущественными способами доступа
для NESTED LOOP
JOIN.
Примеры Параллельного
Распределения
Рассмотрим план
выполнения как был приведен выше, но с
другим методом распределения соединений.
-------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | PX COORDINATOR | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN | | Q1,02 | PCWP | | | 4 | PX RECEIVE | | Q1,02 | PCWP | | | 5 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST | | 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL | T3 | Q1,00 | PCWP | | |* 8 | HASH JOIN | | Q1,02 | PCWP | | | 9 | PX RECEIVE | | Q1,02 | PCWP | | | 10 | PX SEND BROADCAST | :TQ10001 | Q1,01 | P->P | BROADCAST | | 11 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | | 12 | TABLE ACCESS FULL| T1 | Q1,01 | PCWP | | | 13 | PX BLOCK ITERATOR | | Q1,02 | PCWC | | | 14 | TABLE ACCESS FULL | T1 | Q1,02 | PCWP | | --------------------------------------------------------------------------
Отметим изменения:
- Здесь нет BUFFERED операций, оба HASH JOIN теперь не блокирующие, так что нет потенциальной угрозы «парковки» данных. Недостатком в данном случае является то что, датасеты таблиц T1 и T3 будут дублироваться столько раз, сколько слейвов получают распределенные данные, потенциально увеличивая расходы по CPU, памяти и использованию RAC интерконнекта.
- Оба HASH JOIN и полное сканирование второго экземпляра таблицы T1 выполнится одним Parallel Slave Set, как видно из колонки TQ плана (обозначены как Q1,02)
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX PARTITION HASH ALL| | 1 | 4 | Q1,00 | PCWC | |
|* 4 | HASH JOIN | | | | Q1,00 | PCWP | |
| 5 | TABLE ACCESS FULL | T3 | 1 | 4 | Q1,00 | PCWP | |
|* 6 | HASH JOIN | | | | Q1,00 | PCWP | |
| 7 | TABLE ACCESS FULL | T1 | 1 | 4 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 4 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------
Здесь снова тот же SQL
оператор, не без распределения, т.к. все
таблицы hash партиционированы
по ключу соединения. Каждый Parallel Slave
работает с отдельной партицией и
выполняет hash соединение
с соответствующей партицией.
Отметим, что при
выполнении используется только Parallel
Slave Set (Q1,00) и присутствует единственный
PX PARTITION оператор, родительский для всех
соединений. Кроме того нет операторов
PX SEND / RECEIVE, т.е. перераспределения данных
не происходит – это самый быстрый способ
выполнить соединение в Оракл, но только
если не возникает перекоса в распределение
данных между партициями, так что все
Parallel Slave’ы обрабатывают примерно
одинаковые объемы данных. Иначе
длительность операции зависит от слейва
который обрабатывает наибольший объем
информации.
Cost-Based Optimizer (CBO) оценивает
различные методы распределения и, как
правило, склоняется к BROADCAST
распределению для небольших объемов
данных (где «небольшим» является размер
присоединяемого множества строк). Тем
не менее бывают случаи, когда оптимизатор
может недо- или переоценить количество
данных и склонится к HASH
распределению, где BROADCAST
соединение может быть более эффективным
(или наоборот).
Если вы хотите, чтобы
использовать "Partition Wise Join" надо
быть внимательным с DOP(
Degree Of
Parallelism) – если DOP
не соответствует количеству партиций,
в частности степень параллелизма больше
чем кол-во партиций, CBO
может склониться к методу соединения
с перераспределением данных, что может
отрицательно сказаться на производительности.
Эффективней при "Partition Wise Join", когда
DOP равен или меньше
количеству партиций.
Хинт PQ_DISTRIBUTE позволяет
влиять на распределение данных при
соединениях.
Для BROADCAST
распределения, нужно быть внимательным,
какой из 2х и источников соединения
перераспределять, для inner
join оба источника могут
быть перераспределены подобным образом,
но для outer join
есть ограничения.
Синтаксис
хинта выглядит
так:
/*+ PQ_DISTRIBUTE( [ @
queryblock ] tablespec outer_distribution inner_distribution ) */
Обратите внимание, что
первый метод в хинте относится к «другому»
источнику соединения, в то время как
второй относится к объекту упомянутому
в подсказке, т.е. если вы хотите чтобы
таблица T распределялась
BROADCAST методом, то хинт
будет выглядеть так: PQ_DISTRIBUTE([@qb_name]
T[@qb_name] NONE BROADCAST)
Есть несколько нюансов
при использовании этого хинта:
- Используйте OUTLINE опцию DBMS_XPLAN при проверке плана, для списка хинтов сгенерированных оптимизатором. Это даст понять какое из источников соединение будет фактически распределено.
- Учитывайте возможность "SWAP_JOIN_INPUTS" при HASH JOIN операциях. В этом случае оптимизатор может поменять местами источники соединения, чтобы использовать меньший для построения хэш таблицы.
Итак, нужно внимательно
выбирать какой алиас указывать в хинте
учитывая в каком порядке происходит
соединение.
Распределение операций загрузки данных.
С версии 11.2 хинт
PQ_DISTRIBUTE позволяет влиять на распределение
данных при операциях Parallel Load ( Parallel
Insert или Create Table As Select
(CTAS)).
/*+ PQ_DISTRIBUTE( [ @
queryblock ] tablespec distribution) */
Это особенно полезно
для достижения максимального сжатия
данных при загрузке, с помощью
предварительной сортировки по колонке
с наиболее повторяющимися величинами
(например, это можно определить по
атрибуту столбцов NUM_DISTINCT). По умолчанию
Oracle будет выполнять RANGE
распределение данных по SORT
ключам для финальной операции сортировки.
Но поскольку данные могут быть неравномерно
распределены по ключам сортировки, это
весьма вероятно приведет к неудачному
RANGE распределению данных,
где большинство данных будет направляться
на один и тот же Parallel
Slave, оставляя остальные
Parallel Slave'ы
без работы.
С помощью хинта
PQ_DISTRIBUTE для DML/DDL операций, выполняющих
загрузку с RANGE распределением
по ORDER BY
колонкам, можно избежать перекосов
(например, PQ_DISTRIBUTE(<target_table> NONE)).
Следует отметить что это не работает
нормально до версии 11.2.0.4, в следствии
присутствия багов, которые могут привести
к неверному результату или ORA-600,
особенно при использовании хинта
PQ_DISTRIBUTE и операций соединения – как
побочный эффект финальное соединение
с HASH распределением
превращается в соединение с RANGE
распределением (bug 12683462).
Если вам нужно повлиять
на операции загрузки с объединениями,
возможным путем обхода будет материализация
финального результата перед ORDER
BY c помощью
конструкции WITH с MATERIALIZE
хинтом, хотя это обернется затратами
на дополнительный шаг, который скорее
всего, спровоцирует операции I/O
из TEMPORARY табличного
пространства.
Планы с множественными
операциями перемещения данных (Multiple
Data Flow
Operations (DFO)).
«Какая параллельность?»
- часто задаваемый вопрос. Например,
какой фактически DOP (Degree
of Parallelism)
был использован для выполнения этого
оператора прошлой ночью? На самом деле
вопрос не так прост и ему не хвататет
некоторой точности. Прежде всего Degree
Of Parallelism (DOP) определяется оптимизатором
при генерации плана выполнения. Обзор
того как это работает можно найти здесь
(blogs.oracle.com
– прим. переводчика), в блоге «The
Data Warehouse Insider», включая обзор Automatic Degree
Of Parallelism (Auto DOP) опции добавленной в Oracle
Database 11.2.
Рассмотрим проблему,
когда оптимизатор генерирует множественные
так называемые "Data Flow Operations (DFO)" в
плане исполнения, и каждая DFO имеет свою,
возможно различную, DOP.
Во время выполнения
для каждого DOP может
использоваться до двух Parallel Slave Sets
активных одновременно, каждый Parallel
Slave Set состоит из количества Parallel Slaves
соответствующего DOP.
Таким образом, для
одного оператора SQL выполняемого
параллельно, зачастую, нет однозначного
ответа на вопрос «Какая параллельность?»,
поскольку оптимизатор, может разложить
его в несколько DFO, с
разными DOP.
Во время выполнения,
так как каждая DFO, может
иметь один или два Parallel Slave Sets, активных
одновременно, опять же, усложняет ответ
сколько Parallel Slaves используется во время
выполнения, однако необходимое количество
Parallel Slave Sets может быть определено по
плану выполнения.
Еще усложняет вопрос,
тот факт, что многочисленные DFO, построенные
оптимизатором, не будут активны все в
один момент времени, иначе потребовалось
бы больше 2х Parallel Slave Sets и выполнение бы
использовало больше Parallel Slaves, чем
предполагалось, а следовательно и
ресурсов.
Наконец, фактический
DOP для каждой DFO
во время выполнения, может сильно
отличаться от DOP определенного
оптимизатором при генерации плана
выполнения. Так называемый «Downgrade»,
когда по разным причинам используется
более низкая DOP.
Для развлечения, приведу
в глупый и в большинстве случаев полностью
неэффективный, но интересный план
выполнения, демонстрирующий два DFO
дерева, активных одновременно, и,
следовательно, требующих более 2х
активных Parallel Slave Set’ов:
--------------------------------------------------------------------------------------- | Id | Operation | Name | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2302M (1)| | | | |* 1 | FILTER | | | | | | | 2 | PX COORDINATOR | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ20002 | 5129 (1)| Q2,02 | P->S | QC (RAND) | |* 4 | HASH JOIN BUFFERED | | 5129 (1)| Q2,02 | PCWP | | | 5 | PX RECEIVE | | 1164 (1)| Q2,02 | PCWP | | | 6 | PX SEND HASH | :TQ20000 | 1164 (1)| Q2,00 | P->P | HASH | | 7 | PX BLOCK ITERATOR | | 1164 (1)| Q2,00 | PCWC | | | 8 | TABLE ACCESS FULL| T1 | 1164 (1)| Q2,00 | PCWP | | | 9 | PX RECEIVE | | 1163 (1)| Q2,02 | PCWP | | | 10 | PX SEND HASH | :TQ20001 | 1163 (1)| Q2,01 | P->P | HASH | | 11 | PX BLOCK ITERATOR | | 1163 (1)| Q2,01 | PCWC | | | 12 | TABLE ACCESS FULL| T1 | 1163 (1)| Q2,01 | PCWP | | | 13 | PX COORDINATOR | | | | | | | 14 | PX SEND QC (RANDOM) | :TQ10000 | 2328 (1)| Q1,00 | P->S | QC (RAND) | | 15 | PX BLOCK ITERATOR | | 2328 (1)| Q1,00 | PCWC | | |* 16 | TABLE ACCESS FULL | T1 | 2328 (1)| Q1,00 | PCWP | | ---------------------------------------------------------------------------------------Как уже упоминалось, интересный момент здесь в том, что для каждой отдельной DFO, возможен отдельный DOP, и подзапрос в FILTER фазе(13-16 пункты плана) будет выполнен с иной степенью параллельности чем DFO основного запроса. Это легко сказать по выводу EXPLAIN PLAN потому, что одна и та же таблица используется везде и подзапрос FILTER фазы имеет двойную стоимость относительно основного запроса.
Пока оператор выполняется это можно посмотреть в (G)V$PX_SESSION, например, когда для каждой DFO представленном отдельным SERVER_GROUP (1 или 2 нашем случае), разный REQ_DEGREE будет, показана с соответствующим отличающимся количеством Parallel Slaves.
Давайте посмотрим на другой пример, когда несколько DFO используются, но не все они активны одновременно:
--------------------------------------------------------------------------------------------- | Id | Operation | Name | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TEMP TABLE TRANSFORMATION | | | | | | 2 | PX COORDINATOR | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | | 4 | LOAD AS SELECT | SYS_TEMP_0FD9D6602_9709B5 | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | | 6 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | | | 7 | PX COORDINATOR | | | | | | 8 | PX SEND QC (RANDOM) | :TQ20002 | Q2,02 | P->S | QC (RAND) | |* 9 | HASH JOIN BUFFERED | | Q2,02 | PCWP | | | 10 | PX RECEIVE | | Q2,02 | PCWP | | | 11 | PX SEND HASH | :TQ20000 | Q2,00 | P->P | HASH | | 12 | VIEW | | Q2,00 | PCWP | | | 13 | PX BLOCK ITERATOR | | Q2,00 | PCWC | | | 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6602_9709B5 | Q2,00 | PCWP | | | 15 | PX RECEIVE | | Q2,02 | PCWP | | | 16 | PX SEND HASH | :TQ20001 | Q2,01 | P->P | HASH | | 17 | PX BLOCK ITERATOR | | Q2,01 | PCWC | | | 18 | TABLE ACCESS FULL | T1 | Q2,01 | PCWP | | ---------------------------------------------------------------------------------------------
И в этом плане имеется
несколько DFO (не показано
как в плане выше, но также с различными
DOPs), но при выполнении
видно, что эти DFO выполняются
одна за другой и, следовательно, очень
вероятно, различные Parallel Slave Set’ы
соответствующих Parallel Server Groups будет
пытаться повторно использовать те же
Parallel Slave процессы.
Фактический DOP
можно пронаблюдать с помощью Real
Time SQL Monitoring (Enterprise Manager report – прим.
Редактора), наряду с отображением
так называемого «Downgrade»,
но в случае нескольких DFO
в одном запросе Real Time
SQL Monitoring
отобразит наибольшую степень параллелизма
определенную среди всех DFO,
и так как он отображает только одну DOP
для запроса, следовательно не хватает
точности в случае множественных DOP.
Кроме всего вышесказанного, существует
возможность что процесс Parallel Coordinator не
сможет получить нужное количество
Parallel Slaves во время выполнения по разным
причинам. Это может быть преднамеренное
ограничение, налагаемое с помощью
активного плана Resource Manager, или через
параметры инстанса PARALLEL_ADAPTIVE_MULTI_USER,
PARALLEL_MAX_SERVERS или PROCESSES. Эти и другие важные
аспекты будут рассмотрены более подробно
в части 2.
No comments :
Post a Comment