Oracle mechanics

12.12.2014

Гарантированный DOP и параметр PARALLEL_MIN_PERCENT

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 23:16
Tags: , ,

Встречаются запросы с завидным потреблением временного табличного пр-ва для HASH JOIN-ов разного рода и других сортировочно-группировочных операций, при недостатке показывающие:

ORA-01652: невозможно увеличить временный сегмент до 64 в разделе TEMP

Одним из методов сокращения требуемых объёмов temp является использование параллельного выполнения благодаря которому, попутно с улучшением общей скорости выполнения запроса, можно съэкономить временное пр-во, используя бОльший объём PGA большИм кол-вом px-процессов

В этом случае бывает необходимо обеспечить определённый Degree Of Parallelism (DOP) для выполнения конкретного запроса, т.е. ограничить DOP «снизу», для чего теоретически идеально подходит механизм parallel statement queuing, однако установка параметра _PARALLEL_STATEMENT_QUEUING = TRUE не всегда срабатывает, например, при выполнении DBMS_SCHEDULER-ного задания (job) запросы без ожидания resmgr:pq queued, начинают выполняться непараллельно, невзирая на отсутствие свободных PX

Как оказалось, в заданиях DBMS_SCHEDULER отлично срабатывает другой параметр PARALLEL_MIN_PERCENT, со своими особенностями, но срабатывает и в PL/SQL блоке DBMS_SCHEDULER, и на уровне обычной пользовательской сессии

Итак, на сервере со следующими ресурсами:

11.2.0.3@ SQL> @param cpu
 
NAME                      VALUE  IS_DEF   IS_MOD     DSC
------------------------- ------ -------- ---------- --------------------------------------------
cpu_count                 16     TRUE     FALSE      number of CPUs for this instance
parallel_degree_limit     CPU    TRUE     FALSE      limit placed on degree of parallelism
parallel_threads_per_cpu  2      TRUE     FALSE      number of parallel execution threads per CPU
parallel_max_servers      20     FALSE    SYSTEM_MOD maximum parallel query servers per instance

устанавливаю:

SQL> alter session set PARALLEL_MIN_PERCENT = 20;
 
System altered

и пробуя выполнить запрос с DOP=16, установленым прямо в тексте запроса:

SQL> SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate);
 
SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate)
 
ORA-12827: insufficient parallel query slaves (requested 16, available 2, parallel_min_percent 20)

— получаю довольно странный ответ available 2 для случая когда в системе сконфигурировано 20 px и все они доступны:

SQL> select status, count(*) from v$px_process group by status;

STATUS      COUNT(*)
--------- ----------
AVAILABLE         20

Понятно, что при такой неясной арифметике запрос начинает выполняться параллельно лишь при значении параметра:

-- 12,5% * 16(dop) = 2(available)                                                 -- отсчитывая свободные PX от мифической цифры 2

SQL> alter session set PARALLEL_MIN_PERCENT = 13;                                 -- при 13% запрос ещё не работает,
 
Session altered
 
SQL> SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate);
 
SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate)
 
ORA-12827: insufficient parallel query slaves (requested 16, available 2, parallel_min_percent 13)

SQL> alter session set PARALLEL_MIN_PERCENT = 12;                                 -- при 12% -
 
Session altered
 
SQL> SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate); -- запрос уже успешно выполняется

, используя при этом все 20 свободных PX-процессов как видно из мониторинга выполнения:

SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate)

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  SCOTT (255:46677)
 SQL ID              :  50nm0v98au8wn
 SQL Execution ID    :  16777218
 Execution Started   :  12/10/2014 18:08:41
 First Refresh Time  :  12/10/2014 18:08:41
 Last Refresh Time   :  12/10/2014 18:11:31
 Duration            :  248s
 Module/Action       :  PL/SQL Developer/Command Window - New
 Service             :  SYS$USERS
 Program             :  plsqldev.exe
 DOP Downgrade       :  58% -- логично рассчитывается как (Servers Requested - Servers Allocated)/Servers Requested

Global Stats
==========================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Buffer | Read  | Read  | Write | Write |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs  | Bytes | Reqs  | Bytes | Offload |
==========================================================================================================================
|     547 |     189 |      358 |        0.00 |        0.02 |     0.00 |     1M | 59803 |  16GB | 56535 |   6GB | -26.58% |
==========================================================================================================================

Parallel Execution Details (DOP=16 , Servers Requested=48 , Servers Allocated=20)

, или из простого наблюдения за сессиями:

SQL> select program, server_set, server_group
  2    from v$session
  3    join v$px_session
  4   using (saddr)
  5   where sql_id = '50nm0v98au8wn'
  6   order by 1,2,3;

PROGRAM                                     SERVER_SET SERVER_GROUP
------------------------------------------- ---------- ------------
oracle@my-db-host.ru (P000)                          1            1
oracle@my-db-host.ru (P001)                          1            1
oracle@my-db-host.ru (P002)                          1            1
oracle@my-db-host.ru (P003)                          1            1
oracle@my-db-host.ru (P004)                          1            1
oracle@my-db-host.ru (P005)                          1            1
oracle@my-db-host.ru (P006)                          1            1
oracle@my-db-host.ru (P007)                          1            1
oracle@my-db-host.ru (P008)                          1            1
oracle@my-db-host.ru (P009)                          1            1
oracle@my-db-host.ru (P010)                          1            1
oracle@my-db-host.ru (P011)                          1            1
oracle@my-db-host.ru (P012)                          1            1
oracle@my-db-host.ru (P013)                          1            1
oracle@my-db-host.ru (P014)                          1            1
oracle@my-db-host.ru (P015)                          1            1
oracle@my-db-host.ru (P016)                          1            2
oracle@my-db-host.ru (P017)                          1            2
oracle@my-db-host.ru (P018)                          2            2
oracle@my-db-host.ru (P019)                          2            2
plsqldev.exe
 
21 rows selected

— при этом, судя по несимметричному распределению процессов по SERVER_SET/SERVER_GROUP проблемы этого распределения вряд ли является причиной предыдущих ORA-12827

Поиск по металинку наводит на документ How do parallel_min_percent and parallel_adaptive_multi_user work together? (Doc ID 729903.1)(*), где рассказывается о влиянии PARALLEL_ADAPTIVE_MULTI_USER = TRUE (по умолчанию) на расчёты PARALLEL_MIN_PERCENT в версиях Oracle 10-11.1, но в 11.2 этого не наблюдается:

SQL> alter system set parallel_adaptive_multi_user = false;

System altered

SQL> SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate);

SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate)
 
ORA-12827: insufficient parallel query slaves (requested 16, available 2, parallel_min_percent 20)

SQL> alter system set PARALLEL_ADAPTIVE_MULTI_USER = true; -- возвращаю default
 
System altered

Может быть влияет искусственно заниженный PARALLEL_MAX_SERVERS и, установив разумно-рекомендованное значение cpu_count*parallel_threads_per_cpu, можно избавиться от проблемы?

SQL> alter system set parallel_max_servers = 32;

System altered

SQL> alter session set parallel_min_percent = 51;

Session altered

SQL> SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate);

SELECT /*+ PARALLEL(16) */ * from VIEW_WITH_QWERY where dt < trunc(sysdate)
 
ORA-12827: insufficient parallel query slaves (requested 16, available 8, parallel_min_percent 51)

— увы, неясность остаётся, проявляясь в немного модифицированном виде и по-прежнему блокируя выполнение запроса при 32 свободных px:

SQL> select status, count(*) from v$px_process group by status;

STATUS      COUNT(*)
--------- ----------
AVAILABLE         32

Теоретически, конечно, возможно, что PARALLEL_MIN_PERCENT как-то ожидаемо сработает при установке параметра PARALLEL_MAX_SERVERS в значение по умолчанию:

PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5 = 640

, но серьёзно полагаться на такие значения для машинки с 16 физическими ядрами мне кажется очень смелым поступком)

Тем не менее, установка параметра PARALLEL_MIN_PERCENT (с учётом особенностей) позволяет гарантировать минимально необходимое кол-во параллелей как для отдельно выполняемых запросов, так и при выполнении заданий DBMS_SCHEDULER

P.S.*) а рекомендованные в доке трейсы вида alter session set «_px_trace» = all (как и 10046 event) с версии 11.1, похоже, изменились и не показывают деталей расчёта, но можно заметить, что ошибка возникает уже после os thread startup всех имеющихся 32-х параллелей:

        Acquired 16 slaves on 1 instances avg height=16 #set=1 qser=15091713
                P000 inst 1 spid 2637
                ...
                P015 inst 1 spid 2670
...
        Acquired 16 slaves on 1 instances avg height=8 #set=2 qser=15092225
                P016 inst 1 spid 2707
                ...
                P031 inst 1 spid 2739

, где-то на этапе параллельного разбора запроса PX Deq: Parse Reply, т.е. проблема возникает в «неправильном месте», уже после запуска PX-процессов, неэкономно, похоже на непредусмотренное поведение / баг(?)

P.P.S. В тестах использовался запрос с реальным планом выполнения:

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('50nm0v98au8wn'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 50nm0v98au8wn
--------------------
 SELECT /*+ PARALLEL(16) */ * FROM VIEW_WITH_QWERY WHERE DT < TRUNC(SYSDATE)

Plan hash value: 747305078

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |    |          |       |   867K(100)|          |
|   1 |  MAT_VIEW ACCESS BY INDEX ROWID                              |  1 |       23 |       |     1   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN                                          |  1 |          |       |     1   (0)| 00:00:01 |
|   3 |   MAT_VIEW ACCESS BY INDEX ROWID                             |  1 |       23 |       |     1   (0)| 00:00:01 |
|   4 |    INDEX UNIQUE SCAN                                         |  1 |          |       |     1   (0)| 00:00:01 |
|   5 |    MAT_VIEW ACCESS BY INDEX ROWID                            |  1 |       23 |       |     1   (0)| 00:00:01 |
|   6 |     INDEX UNIQUE SCAN                                        |  1 |          |       |     1   (0)| 00:00:01 |
|   7 |     MAT_VIEW ACCESS BY INDEX ROWID                           |  1 |       23 |       |     1   (0)| 00:00:01 |
|   8 |      INDEX UNIQUE SCAN                                       |  1 |          |       |     1   (0)| 00:00:01 |
|   9 |  TEMP TABLE TRANSFORMATION                                   |    |          |       |            |          |
|  10 |   LOAD AS SELECT                                             |    |          |       |            |          |
|  11 |    CONNECT BY NO FILTERING WITH START-WITH                   |    |          |       |            |          |
|  12 |     PX COORDINATOR                                           |    |          |       |            |          |
|  13 |      PX SEND QC (RANDOM)                                     |   158 |  1896 |       |     2   (0)| 00:00:01 |
|  14 |       PX BLOCK ITERATOR                                      |   158 |  1896 |       |     2   (0)| 00:00:01 |
|  15 |        MAT_VIEW ACCESS FULL                                  |   158 |  1896 |       |     2   (0)| 00:00:01 |
|  16 |   PX COORDINATOR                                             |    |          |       |            |          |
|  17 |    PX SEND QC (RANDOM)                                       | 56M|       81G|       |   867K  (1)| 03:22:19 |
|  18 |     HASH GROUP BY                                            | 56M|       81G|    95G|   867K  (1)| 03:22:19 | -- оценка TempSpc достаточно точная,
|  19 |      PX RECEIVE                                              | 56M|       81G|       |   867K  (1)| 03:22:19 | -- Time завышен в разы, реально ~ 1 часа
|  20 |       PX SEND HASH                                           | 56M|       81G|       |   867K  (1)| 03:22:19 |
|  21 |        HASH GROUP BY                                         | 56M|       81G|    95G|   867K  (1)| 03:22:19 |
...
| 162 |                                     PX RECEIVE               |   130M|  5838M|       | 11106   (2)| 00:02:36 |
| 163 |                                      PX SEND HASH            |   130M|  5838M|       | 11106   (2)| 00:02:36 |
| 164 |                                       PX BLOCK ITERATOR      |   130M|  5838M|       | 11106   (2)| 00:02:36 |
| 165 |                                        MAT_VIEW ACCESS FULL  |   130M|  5838M|       | 11106   (2)| 00:02:36 |
----------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=5)
   - automatic DOP: Computed Degree of Parallelism is 16

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: