Это делается достаточно просто через:
* Хинт
/*+ parallel(N) */
* Через установки сессий:
alter session enable parallel dml; --для insert ALTER SESSION FORCE PARALLEL QUERY PARALLEL N;
- N число потоков
В теории этого достаточно, чтобы ускорить запрос в разы.
Но есть ряд ситуаций в которых параллельность наоборот мешает.
Для начала разберемся с терминологией - посмотрим на параллельный план с join 2 таблиц:
create table t_1
compress
as
select /*+ use_nl(a b) */
rownum as id
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;
create table t_2
compress
as
select
rownum as id
, case when rownum <= 5e5 then mod(rownum, 2e6) + 1 else 1 end as fk_id_skew
, rownum as fk_id_uniform
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;
--соберем статистику
begin
dbms_stats.gather_table_stats(user, 't_1');
dbms_stats.gather_table_stats(user, 't_2');
end;
/
- Запрос 1Таблица T2 имеет особенность: fk_id_skew неравномерно заполнен и имеет перекос в сторону 1 - она встречается значительно чаще других.
select COUNT(*) cnt, fk_id_skew from t_2 GROUP BY fk_id_skew ORDER BY cnt desc;
CNT FK_ID_SKEW
---------- ----------
1500000 1
1 22
1 30
1 34
....
- Запрос 2Итак, выполнил простой запрос:
select count(t_2_filler) from (
select /*+ monitor
no_parallel
leading(t_1 t_2)
use_hash(t_2)
no_swap_join_inputs(t_2)
*/
t_1.id as t_1_id
, t_1.filler as t_1_filler
, t_2.id as t_2_id
, t_2.filler as t_2_filler
from t_1
, t_2
where
t_2.fk_id_uniform = t_1.id
and regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_1.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c')
);
- Запрос 3* regexp_replace в этом запросе нужен, чтобы данные отбирались не мгновенно и были видны в статистике затраты CPU.
* Хинты вставлены, чтобы запрос в плане выглядел также как написан тут.
Время выполнения выполнения запроса = 49сек.
Добавим хинт parallel(8) замен no_parallel.
Время выполнения = 8с, что в 6 раз быстрей.
Разберем для понимания план запроса:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'PARALLEL')); ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 213 | 619 (2)| 00:00:08 | | | | | 1 | SORT AGGREGATE | | 1 | 213 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 213 | | | Q1,02 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | 213 | | | Q1,02 | PCWP | | |* 5 | HASH JOIN | | 100K| 20M| 619 (2)| 00:00:08 | Q1,02 | PCWP | | | 6 | PX RECEIVE | | 2000K| 202M| 245 (2)| 00:00:03 | Q1,02 | PCWP | | | 7 | PX SEND HASH | :TQ10000 | 2000K| 202M| 245 (2)| 00:00:03 | Q1,00 | P->P | HASH | | 8 | PX BLOCK ITERATOR | | 2000K| 202M| 245 (2)| 00:00:03 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T_1 | 2000K| 202M| 245 (2)| 00:00:03 | Q1,00 | PCWP | | | 10 | PX RECEIVE | | 2000K| 204M| 371 (1)| 00:00:05 | Q1,02 | PCWP | | | 11 | PX SEND HASH | :TQ10001 | 2000K| 204M| 371 (1)| 00:00:05 | Q1,01 | P->P | HASH | | 12 | PX BLOCK ITERATOR | | 2000K| 204M| 371 (1)| 00:00:05 | Q1,01 | PCWC | | | 13 | TABLE ACCESS FULL| T_2 | 2000K| 204M| 371 (1)| 00:00:05 | Q1,01 | PCWP | | -------------------------------------------------------------------------------------------------------------------- План 1
Основопологающие фазы:
* PX BLOCK ITERATOR - чтение таблицы частями в несколько потоков
* PX SEND - 1 поток посылает данные другому. Важно знать, что только один producer (PX SEND) может быть активен в одно время, что накладывает ограничения на параллельный план выполнения, подробней: Вторая часть по распределению данных в параллельных запросах
** RANGE - данные будут разбиты на диапазоны (часто при сортировке)
** HASH - диапазон данных на основе их хэша (hash join, group by)
** RANDOM - случайная отправка
** BROADCAST - отправка таблицы во все потоки (часто на маленькой таблице, совместно с последующей ROUND ROBIN правой таблицы. Может быть проблемой производительности, если левая таблица значительно больше, чем указано в статистике, т.к. данные дублируются во все потоки)
** ROUND ROBIN - данные отправляются в потоки по кругу
Про способы распределения данных по потокам нужно поговорить отдельно:
Стоит заметить, что данные бьются по значениям в столбцах строк, а не просто по строкам.
Это нужно, чтобы один и тотже диапозон данных из разных таблиц попал в один поток для join.
Если бы Oracle делал не так, то в 1 поток могли бы попасть совершенно разные данные и join нельзя было бы совершить.
На это стоит обратить внимание, т.к. это может являться и причиной замедлений выполнения параллельного запроса при сильном перекосе данных (О причинах замделенния параллельных запросов дальше)
** P->S - параллельность в последовательное выполнение (узкое место или конец запроса - вторая из основных причин замедления параллельного запроса)
** PCWP - параллельность с родителем: сканируем таблицу и сразу делаем join с другой
** PCWC - наоборот: передаем фильтр из внешнего потока и применяем при сканировании
* PX RECEIVE - получение данных из одного параллельного потока в другой
* PX SEND QC - отправка данных координатору
* PX COORDINATOR - приемник всех параллельных запросов
* TQ - Номер потока
Мы рассмотрели идеальный случай распараллеленого запроса. Остановимся подробней на причинах замеделений:
1. Событие "P->S - параллельность в последовательное выполнение"
2. PX SEND skew - Перекос данных
и ускорения:
3. Bloom filter
4. Partition wise
