Основная мысль, которую надо понять при работе с параллельными запроса: "At most one data distribution can be active at the same time" - только одно распределение данных ( PQ Distrib / PX SEND ) может работать одновременно.
Это является причиной появления операций HASH JOIN BUFFERED или BUFFER SORT в параллельных планах, именно они сбивают с мысли человека привыкшего к последовательным не параллельным запросам.
Может сложиться ошибочное мнение о том что при выполнении HASH JOIN закончился hash area size и oracle свопит промежуточные данные на диск/память или делается какаято сортировка, чтобы потом выполнить merge join.
В реальности, как я уже сказал ранее, на параллельные запросы накладывается дополнительное условие: только одно распределение данных ( PQ Distrib / PX SEND ) может работать в один момент.
Это вынуждает складировать промежуточные данные других потоков до конца выполнения распределения, что является причиной появления буферных операций HASH JOIN BUFFERED и BUFFER SORT.
Рассмотрим на примере:
Создадим 2 таблицы:
create table t2
compress
as
select
rownum as id
, mod(rownum, 1000) + 1 as fk
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;
exec dbms_stats.gather_table_stats(null, 't2')
-- Create a copy of T2
create table t4
compress as
select * from t2;
insert /*+ append */ into t4
select
1000000 + rownum as id
, 1000 + mod(rownum, 1000) + 1 as fk
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000000
;
commit;
exec dbms_stats.gather_table_stats(null, 't4')
alter table t2 parallel 2;
alter table t4 parallel 2;
Oracle может выполнить hash join 3 разными способами.
Способ 1 с HASH JOIN BUFFERED:
explain plan for
select * from (
select /*+ no_merge use_hash(a b) no_cpu_costing leading(a b) pq_distribute(b, hash, hash) */
a.filler as a_filler, b.filler as b_filler
from
t2 a
, t4 b
where
a.fk = b.fk
)
where rownum > 1;
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 96G| 56606 | | | |
| 1 | COUNT | | | | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10002 | 1000M| 96G| 56606 | Q1,02 | P->S | QC (RAND) |
| 5 | VIEW | | 1000M| 96G| 56606 | Q1,02 | PCWP | |
|* 6 | HASH JOIN BUFFERED | | 1000M| 195G| 56606 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1000K| 100M| 175 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1000K| 100M| 175 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1000K| 100M| 175 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T2 | 1000K| 100M| 175 | Q1,00 | PCWP | |
| 11 | PX RECEIVE | | 2000K| 200M| 359 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 2000K| 200M| 359 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 2000K| 200M| 359 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| T4 | 2000K| 200M| 359 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------
Хинты заданы, чтобы точно гарантировать последовательность работы.
Через хинт pq_distribute(b, hash, hash) мы задаем способ обмена данными между потоками - в данном случае hash.
Используя мониторинг становится ясна последовательность выполнения (Timeline):
1. PX SEND HASH :TQ10000 - левая таблица целиком хэшируется и отправляется в :TQ10002
2. PX SEND HASH :TQ10001 - правая таблица также целиком хэшируется и отправляется в :TQ10000.
Так же до этапа HASH JOIN происходит откидывание несоответствующих хэшей правой таблицы (Randolf подтверждает это трассировкой и рассчетами на основании количества строк в правой hash таблице)
Дополнительная затраченная память отображается в столбце Memory.
3. PX SEND QC (RANDOM) :TQ10002 - происходит соединение таблиц используя хэши левой и правой таблицы + параллельная отправка промежуточных данных на следующий этап.
Такая последовательность действий обеспечивает нам 1 рабочий PX SEND одновременно.
Способ 2: HASH JOIN альтернативный план, когда хэш правой таблицы передается постепенно и не буферизуется.
Чтобы получить его, достаточно добавить агрегирующую функцию (count/min/max) - что заблокирут передачу промежуточных данных на уровень выше.
explain plan for
select count(*) from (
select /*+ no_merge use_hash(a b) no_cpu_costing leading(a b) pq_distribute(b, hash, hash) */
a.filler as a_filler, b.filler as b_filler
from
t2 a
, t4 b
where
a.fk = b.fk
);
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 56043 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,02 | PCWP | |
| 5 | VIEW | | 1000M| | 56043 | Q1,02 | PCWP | |
|* 6 | HASH JOIN | | 1000M| 7629M| 56043 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1000K| 3906K| 175 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 1000K| 3906K| 175 | Q1,00 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1000K| 3906K| 175 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T2 | 1000K| 3906K| 175 | Q1,00 | PCWP | |
| 11 | PX RECEIVE | | 2000K| 7812K| 359 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 2000K| 7812K| 359 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 2000K| 7812K| 359 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| T4 | 2000K| 7812K| 359 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------
1. PX SEND HASH :TQ10000 - левая таблица целиком хэшируется и отправляется в :TQ10002
2. PX SEND HASH :TQ10001 - правая таблица построчно хэшируется и отправляется в :TQ10000.
Эта операция не требует дополнительных затрат памяти.
3. HASH JOIN - происходит построчное соединение таблиц используя хэши левой и правой таблицы.
4. PX SEND QC (RANDOM) :TQ10002 - отправка результата дальше, выполняется после полного выполнения этапа 2, т.е. после полного выполнения HASH JOIN этапа 3.
Способ 3: BUFFER SORT альтернативный план, в целом похожий на HASH JOIN BUFFERED
select * from (
select /*+ no_merge use_hash(a b) no_cpu_costing leading(a b) pq_distribute(b, none, broadcast) */
a.filler as a_filler, b.filler as b_filler
from
t2 a
, t4 b
where
a.fk = b.fk
)
where rownum > 1;
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000M| 96G| 56606 | | | |
| 1 | COUNT | | | | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 1000M| 96G| 56606 | Q1,01 | P->S | QC (RAND) |
| 5 | VIEW | | 1000M| 96G| 56606 | Q1,01 | PCWP | |
|* 6 | HASH JOIN | | 1000M| 195G| 56606 | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1000K| 100M| 175 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL | T2 | 1000K| 100M| 175 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | 2000K| 200M| 359 | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 2000K| 200M| 359 | Q1,00 | P->P | BROADCAST |
| 12 | PX BLOCK ITERATOR | | 2000K| 200M| 359 | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL| T4 | 2000K| 200M| 359 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------
Меняем метод рассылки данных на BROADCAST - копирование данных во все потоки.
В этом случае хэши правой таблицы не создаются, вместо этого данные помещаются в BUFFER SORT и используются при выполнении HASH JOIN
1. PX SEND BROADCAST :TQ10000 - правая таблица целиком пересылается в BUFFER SORT
Надо заметить, что BUFFER SORT в этом случае ничего не сортирует, а только буферизирует данны.
Объем потребленной памяти = размер правой таблицы * параллелизм (из-за broadcast во все потоки).
2. HASH JOIN - происходит полное соединение таблиц.
4. PX SEND QC (RANDOM) :TQ10001 - отправка результата дальше по мере готовности.
Объем памяти в BUFFER SORT не растет во время выполнения, что подтверждает предположение - правая таблица целиком помещается в буфер сразу на первом этапе.
Вывод: обычный hash join лучше, когда памяти мало, т.к. нет операций с temp/памятью.
Если памяти достаточно, то hash join buffered предпочтительней, т.к. oracle может сразу перейти к следующему этапу запроса и получить большую степерь параллелизма.
