четверг, 24 марта 2016 г.

Oracle: распределение (Distrib) данных в параллельных запросах

Эта заметка осмысление статьи http://oracle-randolf.blogspot.ru/2012/12/hash-join-buffered.html по неочевидной операции HASH JOIN BUFFERED в параллельных запросах.

Основная мысль, которую надо понять при работе с параллельными запроса: "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):
HASH JOIN BUFFERED
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 |            |
-----------------------------------------------------------------------------------------------------
hash join
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
buffer sort
1. PX SEND BROADCAST :TQ10000 - правая таблица целиком пересылается в BUFFER SORT
Надо заметить, что BUFFER SORT в этом случае ничего не сортирует, а только буферизирует данны.
Объем потребленной памяти = размер правой таблицы * параллелизм (из-за broadcast во все потоки).
2. HASH JOIN - происходит полное соединение таблиц.
4. PX SEND QC (RANDOM) :TQ10001 - отправка результата дальше по мере готовности.

Объем памяти в BUFFER SORT не растет во время выполнения, что подтверждает предположение - правая таблица целиком помещается в буфер сразу на первом этапе.
buffer sort 106sec


Вывод: обычный hash join лучше, когда памяти мало, т.к. нет операций с temp/памятью.
Если памяти достаточно, то hash join buffered предпочтительней, т.к. oracle может сразу перейти к следующему этапу запроса и получить большую степерь параллелизма.