пятница, 29 января 2016 г.

Oracle: оптимизация параллельных запросов

Один из простых способов ускорения запросов - это их расспараллеливание.
Это делается достаточно просто через:
* Хинт
/*+ 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.
Время выполнения = , что в 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->P - данные из одной параллельной группы передаются в другую параллельную группу
 ** 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


1. Наличие в плане события "P->S - параллельность в последовательное выполнение", кроме перед "PX COORDINATOR"
Это говорит нам о том, что Oracle вынужден был собрать все потоки в одну последовательность (sequence), что дало бутылочное горлышко ожидания выполнения самого долго потока всеми остальными.

Самыми частыми причинами этого является:
* при добавлении pl/sql функции в запрос, которая не может быть распараллелена
Обойти можно только если функция детерменестическая, когда ее результат зависит только от параметров (никаких вызовов за пределы функции):
 ** Диррективы determenistic и result cache при создании функции
 ** Устаревший (но рабочий) deprecated способ с константым доступом к функции через pragma ( http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/restrictreferences_pragma.htm )
 
PRAGMA RESTRICT_REFERENCES (FNC, RNDS, WNDS, RNPS, WNPS)

* наличие иерархичных запросов CONNECT BY
* наличие доступа по DB LINK
* наличие псевдостолбца с номеро строки ROWNUM
 ROWNUM/CONNECT BY/DB LINK желательно исключать из параллельных запросов, но если такой возможности нет, то максимально их изолировать через materialized with
* index range scan - последовательное сканирование индекса
* Плохо работает с сессионной переменной "_disable_cursor_sharing"=true , форсирующей новый разбор кода sql заново.
Катастрофически растет событие ожидания "cursor pin s wait" - поиск подходящего дочернего курсора из подмножества вариантов.
Подробное описание: https://iusoltsev.wordpress.com/2012/06/04/force-sql-parse-_disable_cursor_sharing-11-2-0-3/

Приведу пример с rownum. Добавим отбор номера строки из каждой таблицы:
select count(t_2_filler) from (
select  /*+ monitor
            leading(t_1 t_2)
            use_hash(t_2)
            no_swap_join_inputs(t_2)
            parallel(8)
        */
        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   (select t_1.*, rownum as rn from t_1) t_1
      ,(select t_2.*, rownum as rn from t_2) 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')
);
План поменялся,
* для расчета COUNT rownum параллельный процесс чтения таблицы с диска "PX BLOCK ITERATOR" выстраивается в очередь "P->S", что сводит на нет все перимещуство распределенного чтения.
* теперь JOIN не выполняется в отдельном потоке ( :TQ10002 )
т.к. оба потока уже были раньше преобразованы в последовательный набор данных и не могут использоваться одновременно.
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     1 |   116 |   310   (2)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE          |          |     1 |   116 |            |          |        |      |            |
|*  2 |   HASH JOIN              |          |   100K|    11M|   310   (2)| 00:00:01 |        |      |            |
|   3 |    VIEW                  |          |  2000K|   110M|   122   (1)| 00:00:01 |        |      |            |
|   4 |     COUNT                |          |       |       |            |          |        |      |            |
|   5 |      PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   6 |       PX SEND QC (RANDOM)| :TQ10000 |  2000K|   202M|   122   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   7 |        PX BLOCK ITERATOR |          |  2000K|   202M|   122   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T_1      |  2000K|   202M|   122   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   9 |    VIEW                  |          |  2000K|   110M|   186   (2)| 00:00:01 |        |      |            |
|  10 |     COUNT                |          |       |       |            |          |        |      |            |
|  11 |      PX COORDINATOR      |          |       |       |            |          |        |      |            |
|  12 |       PX SEND QC (RANDOM)| :TQ20000 |  2000K|   204M|   186   (2)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |
|  13 |        PX BLOCK ITERATOR |          |  2000K|   204M|   186   (2)| 00:00:01 |  Q2,00 | PCWC |            |
|  14 |         TABLE ACCESS FULL| T_2      |  2000K|   204M|   186   (2)| 00:00:01 |  Q2,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------
Как следствие, время выполнения запроса стало даже больше ( 51 с ), чем не параллельная версия (49 с ) из-за лишних издержек на поддержку параллельности, которая не используется

2. PX SEND skew - Перекос данных
при формировании диапозонов данных в один из потоков.

Продемонстрировать это просто используя заранее созданный перекошенный столбец t_2.fk_id_skew.

Если выполнить запрос, но для join таблиц использовать условие: t_2.fk_id_skew = t_1.id
То общий план параллельного запроса не поменяется (см. План 1 ), но вот время выполнения возрастет до 38с.

Причина кроется в том, что в колонке t_2.fk_id_skew кроется 1 500 000 значений = 1 и 3 500 000 остальных. И при выполнении "PX SEND HASH" большая часть строк таблицы попадают в один поток для обработки, вместо того, чтобы равномерно распределиться.

Это хорошо видно в статистике выполнения. Для просмотра воспользуемся функцией "DBMS_SQLTUNE.REPORT_SQL_MONITOR".
Нас интересуют вкладки Parallel и Activity:

Рис.1 - Большую часть времени запрос выполнялся в один поток, остальные потоки его ждали.


Рис. 2. - Это же подтверждается на вкладке PARALLEL: 37c от общего времени работал 1 поток.

Oracle поступает верно, т.к. нельзя же сделать join данных из разных диапазонов.

Для сравнения взгляните статистику выполнения для хорошо распараллеленого запроса (План 1) с условием без перекосов t_2.fk_id_uniform = t_1.id
 
Рис. 3 и Рис. 4

Все выполнялось в 8 потоков и каждый поток равномерно обработал только свою равную часть.

Собственно решения этой проблемы из коробки в Oracle 11 нет. Гистограммы, как при анализе статистики не помогают, т.к. параллелятся данные по значениям в столбцах.
Есть обходные маневры ( http://allthingsoracle.com/parallel-execution-skew-addressing-skew-using-manual-rewrites/ ):
* Разбиваем запрос на UNION, где первая часть без перекошенного значения в параллели и второй union с перкошенным без параллели
* Через генерацию сурогатных уникальных ключей для перекошенного значения на основе косвенных показателей и соединение по новому ключу.
В этом случае данные по равномерному уникальному ключу будут удачно распараллелены.

Замечу, что в Oracle 12 эту проблему уже решили через гистограммы. В плане это отобразится как "PX SEND HYBRID HASH (SKEW)" (HYBRID HASH), т.е. Oracle сам сгенерировал сурогатные ключи для перекошенного значения и удачно смогу распараллелить. (подробней: http://allthingsoracle.com/parallel-execution-skew-12c-hybrid-hash-distribution-with-skew-detection/ )

3. Bloom filters
Не вдаваясь в механику создания битовых векторов bloom filter опишу преимущество их использования.
Пример запроса:
SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.mod = 42
--------------------------------------------------------------------------
| 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 JOIN FILTER CREATE| :BF0000 | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | 
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | |
| 9 | PX RECEIVE | | Q1,02 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | 
| 11 | PX JOIN FILTER USE | :BF0000 | Q1,01 | PCWP | |
| 12 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | |
--------------------------------------------------------------------------
3 - access("T1"."ID"="T2"."ID")
8 - filter("T1"."MOD"=42)
1. На таблице T1 с фильтром "filter("T1"."MOD"=42)" создается bloom filter - PX JOIN FILTER CREATE
2. Фильтр из п.1 применяется на таблицу T2 - PX JOIN FILTER USE
Тем самым ограничивая размер правой таблицы.
3. Отфильтрованная таблица T2 соединяется с HASH JOIN BUFFERED
Подробное описание bloom filter: Bloom filter

bloom filter хороши в:
* Параллельных запросах - уменьшается количество передаваемых данных между потоками за счет предфильтрации правой таблицы
* RAC системах - уменьшает число передаваемых по сети данных между нодами
* InMemory таблицах - осуществляя inmemory предфильтрацию таблицы и осуществление не inmemory join на быстро отфильтрованной правой таблице


4. Partition Wise
В целом похоже на предыдущий пункт, но фильтр накладывается на партиции правой таблицы, также уменьшая сканирования.
Дедектируется в плане по фразам:
* PART JOIN FILTER CREATE ( :BF0000 ) - создание фильтра на левой таблице
* Pstart| Pstop = :BF0000|:BF0000 - применение фильтра в операции чтения правой таблицы PCWC
with reg as (
  select /*+ MATERIALIZE */  r.kd_reg, r.NM_reg, r.NM_TER, r.dimension_key
   from dim_region r
  where r.nm_otd  = 'Фильтр левой таблицы'
    and r.src_id             =  2
)
select /*+ OPT_ESTIMATE(table bd MIN=1000000) LEADING(r1 bd) parallel(8) */ 
     r1.NM_reg as nm_otd,
     bd.sm_bal + bd.sm_bal_nds as sm_bal
from reg r1
     join FCT_PROM_BAL_DT  bd on bd.kd_reg = r1.kd_reg and bd.dim_region = r1.dimension_key
where bd.accounting_dt  = to_date('01.12.2015', 'dd.mm.yyyy');
                     
select * from table(dbms_xplan.display(format=>'ALLSTATS ALL ADVANCED'));
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |   1000K|   103M|   155   (4)| 00:00:01 |       |       |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION  |                             |        |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR            |                             |        |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)      | :TQ10000                    |      4 |   304 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     LOAD AS SELECT          | SYS_TEMP_0FD9D750B_1C7D4113 |        |       |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR      |                             |      4 |   304 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL     | DIM_REGION                  |      4 |   304 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|   7 |   PX COORDINATOR            |                             |        |       |            |          |       |       |        |      |            |
|   8 |    PX SEND QC (RANDOM)      | :TQ20001                    |   1000K|   103M|   153   (4)| 00:00:01 |       |       |  Q2,01 | P->S | QC (RAND)  |
|*  9 |     HASH JOIN               |                             |   1000K|   103M|   153   (4)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|  10 |      PART JOIN FILTER CREATE| :BF0000                     |      4 |   312 |     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|  11 |       PX RECEIVE            |                             |      4 |   312 |     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|  12 |        PX SEND BROADCAST    | :TQ20000                    |      4 |   312 |     2   (0)| 00:00:01 |       |       |  Q2,00 | P->P | BROADCAST  |
|  13 |         VIEW                |                             |      4 |   312 |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  14 |          PX BLOCK ITERATOR  |                             |      4 |   208 |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWC |            |
|  15 |           TABLE ACCESS FULL | SYS_TEMP_0FD9D750B_1C7D4113 |      4 |   208 |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  16 |      PX BLOCK ITERATOR      |                             |   1000K|    29M|   150   (3)| 00:00:01 |:BF0000|:BF0000|  Q2,01 | PCWC |            |
|* 17 |       TABLE ACCESS FULL     | FCT_PROM_BAL_DT             |   1000K|    29M|   150   (3)| 00:00:01 |  4285 |  4403 |  Q2,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------


Вторая часть по распределению данных в параллельных запросах