Oracle mechanics

27.05.2012

DOP при обновлении MATVIEW с использованием удалённых таблиц

Filed under: heuristics,Oracle,remote — Игорь Усольцев @ 13:16
Tags: , ,

Про особенности параллельного выполнения запросов DML, выполняемых при кумулятивном (non-atomic) ресурсоёмком обновлении materialized view значительного объёма (> 300 млн строк), построенного из относительно небольших исходных таблиц, расположеных на удалённой бд (dblink)

Запросы к небольшим источникам данных генерирующие объёмный набор данных (result set) ресурсоёмки по ЦПУ и памяти (PGA и/или temporary tablespace) и сами по себе интересны в плане оптимизации времени выполнения и требуемых ресурсов. При выполнении таких запросов в составе DML операций создания и обновления materialized view Oracle накладывает дополнительные ограничения Degree Of Parallelism (DOP) в случае использования удалённых источников данных

Запрос, выполняемый при локальном обновлении mview формирует временную таблицу S_TMP из 2-х удалённых таблиц, которую в дальнейшем соединяет по достаточно слабому условию:

insert/*+ BYPASS_RECURSIVE_CHECK APPEND */ into TTT_MV_URLS
WITH s_tmp
     AS (SELECT DISTINCT domain, client_id
         FROM t_domain@remote_site.ru
         WHERE domain NOT IN (SELECT domain FROM t_restricted_domain@remote_site.ru)
         AND active = 1
         AND url_source = 'text')
SELECT DISTINCT cu.client_id AS src_client_id, ccu.client_id AS dst_client_id
      FROM s_tmp cu
      JOIN s_tmp ccu
      ON ccu.client_id <> cu.client_id AND ccu.domain = cu.domain
/

Для анализа сначала имеет смысл рассмотреть

подзапрос с конструкцией WITH

(который с большой вероятностью будет выполняться в итоговом DML в первую очередь в процессе TEMP TABLE TRANSFORMATION (TTT), поскольку временнуая таблица S_TMP  используется дважды)

11.2.0.3.@ SQL> WITH s_tmp
2       AS (SELECT
3             DISTINCT domain, client_id FROM t_domain@remote_site.ru
4            WHERE     domain NOT IN (SELECT domain FROM t_restricted_domain@remote_site.ru)
5                  AND active = 1
6                  AND url_source = 'text')
7  SELECT DISTINCT cu.client_id AS src_client_id FROM s_tmp cu
8  /

621557 rows selected.

Elapsed: 00:00:02.42

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                         |   622K|    24M|       | 19994   (1)| 00:03:40 |        |
|   1 |  HASH UNIQUE           |                         |   622K|    24M|    62M| 19994   (1)| 00:03:40 |        |
|*  2 |   HASH JOIN RIGHT ANTI |                         |  1351K|    54M|       |  5576   (2)| 00:01:02 |        |
|   3 |    INDEX FULL SCAN     | T_RESTRICTED_DOMAIN_U01 |   175 |  1750 |       |     1   (0)| 00:00:01 | REMOTE |
|*  4 |    TABLE ACCESS FULL   | T_DOMAIN                |  1352K|    41M|       |  5565   (2)| 00:01:02 | REMOTE |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A1"."DOMAIN"="A2"."DOMAIN")
4 - filter("A1"."URL_SOURCE"='text' AND "A1"."ACTIVE"=1)

Note
-----
- fully remote statement

— при автономном выполнении всё как бы здорово и быстро (+ достаточно точные оценки cardinality в плане выполнения!), но запрос выполнился удалённо (fully remote statement), а при обновлении mview (DML) курсор будет выполняться локально, поэтому для стимуляции локального выполнения материализуем конструкцию WITH:

11.2.0.3.@ SQL> WITH s_tmp
2       AS (SELECT--+ materialize --подсказка используется, чтобы запрос выполнялся локально, а не на дальней стороне линка
3             DISTINCT domain, client_id FROM t_domain@remote_site.ru
4            WHERE     domain NOT IN (SELECT domain FROM t_restricted_domain@remote_site.ru)
5                  AND active = 1
6                  AND url_source = 'text')
7  SELECT DISTINCT cu.client_id AS src_client_id FROM s_tmp cu
8  /

621557 rows selected.

Elapsed: 00:12:04.82

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |   206K|  2619K|       |  3471   (2)| 00:00:49 |        |      |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |        |      |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6F48_D4640C37 |       |       |       |            |          |        |      |
|*  3 |    FILTER                  |                             |       |       |       |            |          |        |      |-- фактически NESTED LOOPS с удалённой наблицей на 200К строк
|   4 |     REMOTE                 | T_DOMAIN                    |   206K|    10M|       |  2572   (2)| 00:00:37 | REMOTE | R->S |
|   5 |     REMOTE                 | T_RESTRICTED_DOMAIN         |     1 |    20 |       |     0   (0)| 00:00:01 | REMOTE | R->S |
|   6 |   HASH UNIQUE              |                             |   206K|  2619K|  4064K|   892   (1)| 00:00:13 |        |      |
|   7 |    VIEW                    |                             |   206K|  2619K|       |   275   (1)| 00:00:04 |        |      |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6F48_D4640C37 |   206K|    10M|       |   275   (1)| 00:00:04 |        |      |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter( NOT EXISTS (SELECT 0 FROM  "T_RESTRICTED_DOMAIN" WHERE "DOMAIN"=:B1))

Remote SQL Information (identified by operation id):
----------------------------------------------------

4 - SELECT "CLIENT_ID","DOMAIN","URL_SOURCE","ACTIVE" FROM "t_domain" "t_domain" WHERE
"URL_SOURCE"='text' AND "ACTIVE"=1 (accessing 'remote_site.ru' )

5 - SELECT "DOMAIN" FROM "T_RESTRICTED_DOMAIN" "T_RESTRICTED_DOMAIN" WHERE "DOMAIN"=:1 (accessing
'remote_site.ru' )

— по умолчанию локально запрос выполняется намного хуже (с гораздо менее точным планом), судя по  Remote SQL Information — локально реализуя операцию NESTED LOOPS (#3  операция FILTER) между удалёнными таблицами, используя в качестве driving table таблицу T_DOMAIN с более чем 1 млн записей

Варианты ускорения / исключения медленной операции:

1) материализация удалённых таблиц по-отдельности с последующим соединением локальных временных таблиц:

11.2.0.3.@ SQL> WITH T_DOMAIN_TTT
2       AS (SELECT /*+ materialize*/ * FROM t_domain@remote_site.ru t_domain),
3       t_restricted_domain_ttt
4       AS (SELECT /*+ materialize*/ * FROM t_restricted_domain@remote_site.ru t_restricted_domain),
5       s_tmp
6       AS (SELECT /*+ materialize*/
7                 DISTINCT domain, client_id
8             FROM T_DOMAIN_TTT
9            WHERE domain NOT IN (SELECT domain FROM t_restricted_domain_ttt)
10                  AND active = 1
11                  AND url_source = 'text')
12  SELECT DISTINCT cu.client_id AS src_client_id FROM s_tmp cu
13  /

621557 rows selected.

Elapsed: 00:00:08.97

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |   829K|    10M|       |   414K  (1)| 01:36:49 |        |      |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |        |      |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6F5E_D4640C37 |       |       |       |            |          |        |      |
|   3 |    REMOTE                  | T_DOMAIN                    |  1650K|    81M|       |  2568   (1)| 00:00:36 | REMOTE | R->S |
|   4 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6F5F_D4640C37 |       |       |       |            |          |        |      |
|   5 |    REMOTE                  | T_RESTRICTED_DOMAIN         |   175 |  3500 |       |     2   (0)| 00:00:01 | REMOTE | R->S |
|   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6F60_D4640C37 |       |       |       |            |          |        |      |
|   7 |    HASH UNIQUE             |                             |  1650K|  2484M|  2579M|   342K  (1)| 01:19:53 |        |      |
|*  8 |     HASH JOIN RIGHT ANTI   |                             |  1650K|  2484M|       |  2211   (2)| 00:00:31 |        |      |
|   9 |      VIEW                  |                             |   175 |   175K|       |     2   (0)| 00:00:01 |        |      |
|  10 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6F5F_D4640C37 |   175 |  3500 |       |     2   (0)| 00:00:01 |        |      |
|* 11 |      VIEW                  |                             |  1650K|   869M|       |  2203   (1)| 00:00:31 |        |      |
|  12 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9D6F5E_D4640C37 |  1650K|    81M|       |  2203   (1)| 00:00:31 |        |      |
|  13 |   HASH UNIQUE              |                             |   829K|    10M|    31M| 69981   (1)| 00:16:20 |        |      |
|  14 |    VIEW                    |                             |  1650K|    20M|       | 66313   (1)| 00:15:29 |        |      |
|  15 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6F60_D4640C37 |  1650K|   826M|       | 66313   (1)| 00:15:29 |        |      |
----------------------------------------------------------------------------------------------------------------------------------

2) Создание небходимого обзора на удалённом сайте с последующим выполнением запроса к обзору:

REMOTE@ SQL> create view V_REMOTE_VIEW
2  as
3  SELECT DISTINCT domain, client_id FROM t_domain
4            WHERE     domain NOT IN (SELECT domain FROM t_restricted_domain)
5                  AND active = 1
6                  AND url_source = 'text'
7  /

View created

11.2.0.3.@ SQL> WITH s_tmp AS (select--+ materialize
2                 * from v_remote_view@remote_site.ru)
3  SELECT DISTINCT cu.client_id AS src_client_id FROM s_tmp cu
4  /

621557 rows selected.

Elapsed: 00:00:03.73

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             | 51593 |   654K|       |  7236   (2)| 00:01:42 |        |      |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |       |            |          |        |      |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6F40_D4640C37 |       |       |       |            |          |        |      |
|   3 |    REMOTE                  | V_REMOTE_VIEW               |  1650K|    81M|       |  2561   (1)| 00:00:36 | REMOTE | R->S |
|   4 |   HASH UNIQUE              |                             | 51593 |   654K|    31M|  4674   (2)| 00:01:06 |        |      |
|   5 |    VIEW                    |                             |  1650K|    20M|       |  2196   (1)| 00:00:31 |        |      |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6F40_D4640C37 |  1650K|    81M|       |  2196   (1)| 00:00:31 |        |      |
----------------------------------------------------------------------------------------------------------------------------------

— последний вариант — лучший по времени, но разница 3,7 против 9 секунд невелика по сравнению с полной стоимостью DML, и в дальнейшем я использую 1-й «ленивый» вариант

Собственно DML по обновлению MVIEW

Табличка для MVIEW предварительно создана (prebuilt table) с кляузой PARALLEL 16:

11.2.0.3.@ SQL> select degree from user_tables where TABLE_NAME = 'TTT_MV_URLS';

DEGREE
------
16

Используя 1-й вариант материализации WITH и подсказку /*+ parallel*/  выполняю запрос кумулятивного обновления MVIEW:

11.2.0.3.@ SQL> insert/*+ BYPASS_RECURSIVE_CHECK APPEND */ into TTT_MV_URLS
2  WITH T_DOMAIN_ttt
3       AS (SELECT /*+ materialize*/ * FROM t_domain@remote_site.ru t_domain),
4       t_restricted_domain_ttt
5       AS (SELECT /*+ materialize*/ * FROM t_restricted_domain@remote_site.ru t_restricted_domain),
6       s_tmp
7       AS (SELECT /*+ materialize*/
8                 DISTINCT domain, client_id
9             FROM T_DOMAIN_ttt
10            WHERE domain NOT IN (SELECT domain FROM t_restricted_domain_ttt)
11                  AND active = 1
12                  AND url_source = 'text')
13  SELECT --+ parallel
14  DISTINCT cu.client_id AS src_client_id, ccu.client_id AS dst_client_id
15    FROM s_tmp cu
16         JOIN s_tmp ccu ON ccu.client_id <> cu.client_id AND ccu.domain = cu.domain
17  /

352098280 rows created.

Elapsed: 00:15:30.28

неплохо, но при выполнении запроса используется DOP=2 и в течение большей части выполнения работают только от 2 до 4-х параллельных процессов (PX) независимо от использования /*+ parallel*/ или /*+ parallel(auto)*/:

SQL> select to_char(SQL_EXEC_START, 'hh24:mi:ss') as EXEC_START,
2         PROCESS_NAME,
3         PX_MAXDOP,
4         PX_SERVERS_REQUESTED,
5         PX_SERVERS_ALLOCATED,
6         to_char(FIRST_REFRESH_TIME, 'hh24:mi:ss') as FIRST_REFRESH,
7         to_char(LAST_REFRESH_TIME, 'hh24:mi:ss') as LAST_REFRESH
8    from v$sql_monitor
9   where sql_id = '3ggfgg7tq8qr5'
10   order by LAST_REFRESH_TIME desc
11  /

EXEC_START PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED FIRST_REFRESH LAST_REFRESH
---------- ------------ ---------- -------------------- -------------------- ------------- ------------
17:20:33   ora                   2                    8                    8 17:20:38      17:34:59
17:20:33   p000                                                              17:20:40      17:34:59
17:20:33   p001                                                              17:20:40      17:34:59
17:20:33   p003                                                              17:20:40      17:31:10
17:20:33   p002                                                              17:20:40      17:26:19
17:20:33   p002                                                              17:20:38      17:20:40
17:20:33   p000                                                              17:20:38      17:20:40
17:20:33   p001                                                              17:20:38      17:20:40
17:20:33   p003                                                              17:20:38      17:20:40

SQL трейс показывает, что в процессе выполнения INSERT для конструкции WITH создаётся Global Temporary Table (GTT) с кляузой NOPARALLEL:

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6F9B_D4640C37" ("C0" VARCHAR2(255 CHAR),"C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254953371 ) NOPARALLEL

Если же выполнять тот же DML на основе локальных клонов удалённых таблиц:

11.2.0.3.@ SQL> insert/*+ BYPASS_RECURSIVE_CHECK APPEND */ into TTT_MV_URLS
2  WITH T_DOMAIN_ttt
3       AS (SELECT /*+ materialize*/ * FROM T_DOMAIN t_domain),
4       t_restricted_domain_ttt
5       AS (SELECT /*+ materialize*/ * FROM t_restricted_domain t_restricted_domain),
6       s_tmp
7       AS (SELECT /*+ materialize*/
8                 DISTINCT domain, client_id
9             FROM T_DOMAIN_ttt
10            WHERE domain NOT IN (SELECT domain FROM t_restricted_domain_ttt)
11                  AND active = 1
12                  AND url_source = 'text')
13  SELECT --+ parallel
14  DISTINCT cu.client_id AS src_client_id, ccu.client_id AS dst_client_id
15    FROM s_tmp cu
16         JOIN s_tmp ccu ON ccu.client_id <> cu.client_id AND ccu.domain = cu.domain
17  /

254341774 rows created.

Elapsed: 00:10:19.71

SQL> @v$sql_monitor

EXEC_START PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED FIRST_REFRESH LAST_REFRESH
---------- ------------ ---------- -------------------- -------------------- ------------- ------------
18:25:27   ora                  48                  288                  288 18:25:27      18:25:35

— можно видеть что запрос может выполняться быстрее с большим DOP=48 (что в этом случае интереснее), при этом в процессе выполнения GTT создаётся PARALLEL:

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6FC2_D4640C37" ("C0" VARCHAR2(255 CHAR),"C1" NUMBER(20) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254953410 ) PARALLEL
...
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                             |       |       |       |   190 (100)|          |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR               |                             |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000                    |  1269K|    70M|       |    46   (3)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     LOAD AS SELECT             |                             |       |       |       |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |                             |  1269K|    70M|       |    46   (3)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL        | T_DOMAIN                    |  1269K|    70M|       |    46   (3)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |   PX COORDINATOR               |                             |       |       |       |            |          |        |      |            |
|   8 |    PX SEND QC (RANDOM)         | :TQ20000                    |   176 |  3520 |       |     2   (0)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |
|   9 |     LOAD AS SELECT             |                             |       |       |       |            |          |  Q2,00 | PCWP |            |
|  10 |      PX BLOCK ITERATOR         |                             |   176 |  3520 |       |     2   (0)| 00:00:01 |  Q2,00 | PCWC |            |
|  11 |       TABLE ACCESS FULL        | T_RESTRICTED_DOMAIN         |   176 |  3520 |       |     2   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|  12 |   PX COORDINATOR               |                             |       |       |       |            |          |        |      |            |
|  13 |    PX SEND QC (RANDOM)         | :TQ30002                    |  1269K|    73M|       |    48   (7)| 00:00:01 |  Q3,02 | P->S | QC (RAND)  |
|  14 |     LOAD AS SELECT             |                             |       |       |       |            |          |  Q3,02 | PCWP |            |
|  15 |      HASH UNIQUE               |                             |  1269K|    73M|    82M|    48   (7)| 00:00:01 |  Q3,02 | PCWP |            |
|  16 |       PX RECEIVE               |                             |  1269K|    73M|       |    48   (7)| 00:00:01 |  Q3,02 | PCWP |            |
|  17 |        PX SEND HASH            | :TQ30001                    |  1269K|    73M|       |    48   (7)| 00:00:01 |  Q3,01 | P->P | HASH       |
|  18 |         HASH UNIQUE            |                             |  1269K|    73M|    82M|    48   (7)| 00:00:01 |  Q3,01 | PCWP |            |
|  19 |          HASH JOIN RIGHT ANTI  |                             |  1269K|    73M|       |    46   (3)| 00:00:01 |  Q3,01 | PCWP |            |
|  20 |           PX RECEIVE           |                             |   176 |  1760 |       |     2   (0)| 00:00:01 |  Q3,01 | PCWP |            |
|  21 |            PX SEND BROADCAST   | :TQ30000                    |   176 |  1760 |       |     2   (0)| 00:00:01 |  Q3,00 | P->P | BROADCAST  |
|  22 |             VIEW               |                             |   176 |  1760 |       |     2   (0)| 00:00:01 |  Q3,00 | PCWP |            |
|  23 |              PX BLOCK ITERATOR |                             |   176 |  3520 |       |     2   (0)| 00:00:01 |  Q3,00 | PCWC |            |
|  24 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6FC1_D4640C37 |   176 |  3520 |       |     2   (0)| 00:00:01 |  Q3,00 | PCWP |            |
|  25 |           VIEW                 |                             |  1269K|    61M|       |    44   (3)| 00:00:01 |  Q3,01 | PCWP |            |
|  26 |            PX BLOCK ITERATOR   |                             |  1269K|    70M|       |    44   (3)| 00:00:01 |  Q3,01 | PCWC |            |
|  27 |             TABLE ACCESS FULL  | SYS_TEMP_0FD9D6FC0_D4640C37 |  1269K|    70M|       |    44   (3)| 00:00:01 |  Q3,01 | PCWP |            |
|  28 |   LOAD AS SELECT               |                             |       |       |       |            |          |        |      |            |
|  29 |    PX COORDINATOR              |                             |       |       |       |            |          |        |      |            |
|  30 |     PX SEND QC (RANDOM)        | :TQ40003                    |  1790K|    81M|       |    94   (4)| 00:00:02 |  Q4,03 | P->S | QC (RAND)  |
|  31 |      HASH UNIQUE               |                             |  1790K|    81M|    95M|    94   (4)| 00:00:02 |  Q4,03 | PCWP |            |
|  32 |       PX RECEIVE               |                             |  1790K|    81M|       |    94   (4)| 00:00:02 |  Q4,03 | PCWP |            |
|  33 |        PX SEND HASH            | :TQ40002                    |  1790K|    81M|       |    94   (4)| 00:00:02 |  Q4,02 | P->P | HASH       |
|  34 |         HASH UNIQUE            |                             |  1790K|    81M|    95M|    94   (4)| 00:00:02 |  Q4,02 | PCWP |            |
|  35 |          HASH JOIN             |                             |  1790K|    81M|       |    92   (2)| 00:00:02 |  Q4,02 | PCWP |            |
|  36 |           PX RECEIVE           |                             |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,02 | PCWP |            |
|  37 |            PX SEND HASH        | :TQ40000                    |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,00 | P->P | HASH       |
|  38 |             VIEW               |                             |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,00 | PCWP |            |
|  39 |              PX BLOCK ITERATOR |                             |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,00 | PCWC |            |
|  40 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6FC2_D4640C37 |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,00 | PCWP |            |-- эта GTT создаётся параллельно при локальных источниках
|  41 |           PX RECEIVE           |                             |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,02 | PCWP |            |
|  42 |            PX SEND HASH        | :TQ40001                    |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,01 | P->P | HASH       |
|  43 |             VIEW               |                             |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,01 | PCWP |            |
|  44 |              PX BLOCK ITERATOR |                             |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,01 | PCWC |            |
|  45 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6FC2_D4640C37 |  1269K|    29M|       |    46   (0)| 00:00:01 |  Q4,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------

— план локального выполнения получился неадекватно дешёвым и по стоимости и по предполагаемому времени выполнения поэтому если включить автоматическое опредение DOP, например, с помощью подсказки /*+ parallel(auto)*/ то Oracle , сравнивая рассчитанное в плане время выполнения Time=2 сек. с параметром parallel_min_time_threshold, выбирает непараллельное выполнение:

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Запрос к локальным таблицам выполнялся с отключённой по умолчанию parallel_dml_mode:

11.2.0.3.@ SQL> select * from v$ses_optimizer_env where name = 'parallel_dml_mode' and sid = SYS_CONTEXT('userenv', 'sid');

SID NAME              SQL_FEATURE ISDEFAULT VALUE
--- ----------------- ----------- --------- --------
868 parallel_dml_mode QKSFM_ALL   YES       disabled

который в рассматриваемом случае не даёт никакого выигрыша в скорости выполнения DML (проверил) поскольку скорость вставки (по умолчанию выполняемой при кумулятивном обновлении в режиме direct-path) лимитируется скоростью выполнения собственно запроса — т.е. скоростью доставки боеприпасов:)

Интересно было бы найти способ управления DOP для локально выполняемого DML, использующего удалённые таблицы — и, как оказалось, для форсирования выполнения такого DML с бОльшим DOP помогает изменение parallel_dml_mode:

11.2.0.3.@ SQL> alter session enable parallel dml;

Session altered.

11.2.0.3.@ SQL> select * from v$ses_optimizer_env where name = 'parallel_dml_mode' and sid = SYS_CONTEXT('userenv', 'sid');

SID NAME               ISDEFAULT VALUE
--- -----------------  --------- -------
868 parallel_dml_mode  NO        enabled

1 row selected.

11.2.0.3.@ SQL> insert/*+ BYPASS_RECURSIVE_CHECK APPEND */ into TTT_MV_URLS
2  WITH T_DOMAIN_ttt
3       AS (SELECT /*+ materialize*/ * FROM t_domain@remote_site.ru t_domain),
4       t_restricted_domain_ttt
5       AS (SELECT /*+ materialize*/ * FROM t_restricted_domain@remote_site.ru t_restricted_domain),
6       s_tmp
7       AS (SELECT /*+ materialize*/
8                 DISTINCT domain, client_id
9             FROM T_DOMAIN_ttt
10            WHERE domain NOT IN (SELECT domain FROM t_restricted_domain_ttt)
11                  AND active = 1
12                  AND url_source = 'text')
13  SELECT --+ parallel(auto)
14  DISTINCT cu.client_id AS src_client_id, ccu.client_id AS dst_client_id
15    FROM s_tmp cu
16         JOIN s_tmp ccu ON ccu.client_id <> cu.client_id AND ccu.domain = cu.domain
17  /

352098280 rows created.

Elapsed: 00:10:19.69

SQL> @v$sql_monitor

EXEC_START PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED FIRST_REFRESH LAST_REFRESH
---------- ------------ ---------- -------------------- -------------------- ------------- ------------
18:37:26   ora                  48                  100                  100 18:37:35      18:37:45

— фактически «удалённый» DML выполнился со скоростью «локального», и таким же DOP=48 — максимальным для этого окружения!

При этом GTT по-прежнему создаётся с опцией NOPARALLEL — что, возможно, являлось одной из причин ограничеия DOP=2 для DML с использованием удалённых таблиц:

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6FDA_D4640C37" ("C0" VARCHAR2(255 CHAR),"C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254953434 ) NOPARALLEL

Фактически тот же (за исключением параллельного доступа к таблицам-источникам) план выполнения «удалённого» DML выглядит намного более реалистичным по стоимости и времени выполнения по сравнению с планом «локального» DML (см.выше):

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                             |       |       |       | 59728 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |       |            |          |
|   2 |   LOAD AS SELECT               |                             |       |       |       |            |          |
|   3 |    REMOTE                      | T_DOMAIN                    |  1650K|    81M|       |  2568   (1)| 00:00:36 |
|   4 |   LOAD AS SELECT               |                             |       |       |       |            |          |
|   5 |    REMOTE                      | T_RESTRICTED_DOMAIN         |   175 |  3500 |       |     2   (0)| 00:00:01 |
|   6 |   PX COORDINATOR               |                             |       |       |       |            |          |
|   7 |    PX SEND QC (RANDOM)         | :TQ10002                    |  1650K|  2484M|       | 23777   (1)| 00:05:33 |
|   8 |     LOAD AS SELECT             |                             |       |       |       |            |          |
|   9 |      HASH UNIQUE               |                             |  1650K|  2484M|  2579M| 23777   (1)| 00:05:33 |
|  10 |       PX RECEIVE               |                             |  1650K|  2484M|       | 23777   (1)| 00:05:33 |
|  11 |        PX SEND HASH            | :TQ10001                    |  1650K|  2484M|       | 23777   (1)| 00:05:33 |
|  12 |         HASH UNIQUE            |                             |  1650K|  2484M|  2579M| 23777   (1)| 00:05:33 |
|* 13 |          HASH JOIN RIGHT ANTI  |                             |  1650K|  2484M|       |   156   (2)| 00:00:03 |
|  14 |           PX RECEIVE           |                             |   175 |   175K|       |     2   (0)| 00:00:01 |
|  15 |            PX SEND BROADCAST   | :TQ10000                    |   175 |   175K|       |     2   (0)| 00:00:01 |
|  16 |             VIEW               |                             |   175 |   175K|       |     2   (0)| 00:00:01 |
|  17 |              PX BLOCK ITERATOR |                             |   175 |  3500 |       |     2   (0)| 00:00:01 |
|* 18 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6FD9_D4640C37 |   175 |  3500 |       |     2   (0)| 00:00:01 |
|* 19 |           VIEW                 |                             |  1650K|   869M|       |   153   (1)| 00:00:03 |
|  20 |            PX BLOCK ITERATOR   |                             |  1650K|    81M|       |   153   (1)| 00:00:03 |
|* 21 |             TABLE ACCESS FULL  | SYS_TEMP_0FD9D6FD8_D4640C37 |  1650K|    81M|       |   153   (1)| 00:00:03 |
|  22 |   PX COORDINATOR               |                             |       |       |       |            |          |
|  23 |    PX SEND QC (RANDOM)         | :TQ20003                    |  2532K|  2535M|       | 33381   (1)| 00:07:48 |
|  24 |     LOAD AS SELECT             |                             |       |       |       |            |          |
|  25 |      HASH UNIQUE               |                             |  2532K|  2535M|  2637M| 33381   (1)| 00:07:48 |
|  26 |       PX RECEIVE               |                             |  2532K|  2535M|       | 33381   (1)| 00:07:48 |
|  27 |        PX SEND HASH            | :TQ20002                    |  2532K|  2535M|       | 33381   (1)| 00:07:48 |
|  28 |         HASH UNIQUE            |                             |  2532K|  2535M|  2637M| 33381   (1)| 00:07:48 |
|* 29 |          HASH JOIN             |                             |  2532K|  2535M|       |  9204   (1)| 00:02:09 |
|  30 |           PX RECEIVE           |                             |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  31 |            PX SEND HASH        | :TQ20000                    |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  32 |             VIEW               |                             |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  33 |              PX BLOCK ITERATOR |                             |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|* 34 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6FDA_D4640C37 |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  35 |           PX RECEIVE           |                             |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  36 |            PX SEND HASH        | :TQ20001                    |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  37 |             VIEW               |                             |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|  38 |              PX BLOCK ITERATOR |                             |  1650K|   826M|       |  4601   (1)| 00:01:05 |
|* 39 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6FDA_D4640C37 |  1650K|   826M|       |  4601   (1)| 00:01:05 |
----------------------------------------------------------------------------------------------------------------------

Автоматический выбор DOP может быть неоптимальным, но теперь (без ограничения снизу DOP=2, связанным с использованием dblink) количество параллельных процессов можно легко регулировать подсказкой /*+ parallel(#PX)*/:

11.2.0.3.@ SQL> alter session enable parallel dml;

Session altered.

11.2.0.3.@ SQL> insert/*+ BYPASS_RECURSIVE_CHECK APPEND */ into TTT_MV_URLS
2  WITH T_DOMAIN_ttt
3       AS (SELECT /*+ materialize*/ * FROM t_domain@remote_site.ru t_domain),
4       t_restricted_domain_ttt
5       AS (SELECT /*+ materialize*/ * FROM t_restricted_domain@remote_site.ru t_restricted_domain),
6       s_tmp
7       AS (SELECT /*+ materialize*/
8                 DISTINCT domain, client_id
9             FROM T_DOMAIN_ttt
10            WHERE domain NOT IN (SELECT domain FROM t_restricted_domain_ttt)
11                  AND active = 1
12                  AND url_source = 'text')
13  SELECT --+ parallel(16)
14  DISTINCT cu.client_id AS src_client_id, ccu.client_id AS dst_client_id
15    FROM s_tmp cu
16         JOIN s_tmp ccu ON ccu.client_id <> cu.client_id AND ccu.domain = cu.domain
17  /

352098280 rows created.

Elapsed: 00:09:13.50

SQL> @v$sql_monitor

EXEC_START PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED FIRST_REFRESH LAST_REFRESH
---------- ------------ ---------- -------------------- -------------------- ------------- ------------
19:14:17   ora                  16                   36                   36 19:14:24      19:16:24

— например, с пониженным DOP=16 может выполняться даже быстрее

Операции с MVIEW

могут лоставлять дополнительные проблемы:

Собственно, создание MVIEW:

create materialized view TTT_MV_URLS
refresh complete on demand
as
WITH T_DOMAIN_ttt
AS (SELECT --+ materialize
* FROM t_domain@remote_site.ru T_DOMAIN),
t_restricted_domain_ttt
AS (SELECT --+ materialize
* FROM t_restricted_domain@remote_site.ru t_restricted_domain),
s_tmp
AS (SELECT --+ materialize
DISTINCT domain, client_id
FROM T_DOMAIN_ttt
WHERE domain NOT IN (SELECT domain FROM t_restricted_domain_ttt WHERE domain IS NOT NULL)
AND domain IS NOT NULL
AND active = 1
AND url_source = 'text')
SELECT --+ parallel(8)                                                    -- иначе DOP = unlimited при обновлении, неограниченный параметром PARALLEL_DEGREE_LIMIT
DISTINCT cu.client_id AS "SRC_CLIENT_ID", ccu.client_id AS "DST_CLIENT_ID"
FROM s_tmp cu
JOIN s_tmp ccu ON ccu.client_id <> cu.client_id AND ccu.domain = cu.domain;

— выполняется с DOP=2 и никак не зависит от ENABLE PARALLEL DDL/ENABLE PARALLEL DML — можно поразбираться отдельно, однако создание — операция разовая, к тому же всегда можно быстро создать пустую PREBUILT TABLE и в дальнейшем быстро параллельно обновить этот MVIEW

Немодифицированный job по кумулятивному обновлению MV:

11.2.0.3.@ SQL> begin
2       DBMS_MVIEW.REFRESH('MV_URLS_1', 'C', ATOMIC_REFRESH=>FALSE);
3  end;
4  /

PL/SQL procedure successfully completed.

Elapsed: 00:30:04.41

PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED FIRST_REFRESH LAST_REFRESH
------------ ---------- -------------------- -------------------- ------------- ------------
ora                   2                    8                    8 17:01:51      17:19:54

— выполняется также с DOP = 2, после модификации (трюка с parallel dml):

begin
execute immediate 'alter session enable parallel dml';
DBMS_MVIEW.REFRESH('TTT_MV_URLS', 'C', ATOMIC_REFRESH=>FALSE);
end;
/

— выполняется с максимально возможным в системе DOP, независимо от значения параметра в подсказке /*+ PARALLEL(#PX)*/:

EXEC_START PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED FIRST_REFRESH LAST_REFRESH
---------- ------------ ---------- -------------------- -------------------- ------------- ------------
18:25:27   ora                  48                  288                  288 18:25:27      18:25:35

— что может быть не всегда приемлемо — например, по потреблению ЦПУ, времени выполнения или требуемому размеру temporary tablespace

Например, при 20 GB временном табличном пространстве и 9 Gb PGA обновление с DOP=48 может не состояться:

11.2.0.3.@ SQL> begin
2       execute immediate 'alter session enable parallel dml';
3       DBMS_MVIEW.REFRESH('TTT_MV_URLS', 'C', ATOMIC_REFRESH=>FALSE);
4  end;
5  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P005
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP_FLASH
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 3

Как оказалось, DOP с которым выполняется обновление зависит от комбинации параметра /*+ parallel(#PX)*/ в определении MVIEW и значения параметра PARALLEL_DEGREE_LIMIT

Процедура обновления MVIEW с возможностью ограничения DOP сверху (совместно с подсказкой /*+ parallel(8)*/ в MVIEW DDL) выглядит так:

11.2.0.3.@ SQL> begin
2       execute immediate 'alter session enable parallel dml';
3       execute immediate 'alter session set parallel_degree_limit = 8';
4       DBMS_MVIEW.REFRESH('TTT_MV_URLS', 'C', ATOMIC_REFRESH=>FALSE);
5  end;
6  /

PL/SQL procedure successfully completed.

Elapsed: 00:15:52.80

— и достаточно быстро выполняется в точном соответствии с заданным значением DOP:

SQL> @v$sql_monitor

PROCESS_NAME  PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED
------------ ---------- -------------------- --------------------
ora                   8                   20                   20

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | TQ/Ins |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                             |       |       |    15 (100)|          |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |          |        |      |            |
|   2 |   LOAD AS SELECT               |                             |       |       |            |          |        |      |            |
|   3 |    REMOTE                      | T_DOMAIN                    |     1 |   600 |     2   (0)| 00:00:01 | REMOTE | R->S |            |
|   4 |   LOAD AS SELECT               |                             |       |       |            |          |        |      |            |
|   5 |    REMOTE                      | T_RESTRICTED_DOMAIN         |     1 |  1297 |     2   (0)| 00:00:01 | REMOTE | R->S |            |
|   6 |   PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|   7 |    PX SEND QC (RANDOM)         | :TQ10002                    |     1 |  1578 |     6  (34)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   8 |     LOAD AS SELECT             |                             |       |       |            |          |  Q1,02 | PCWP |            |
|   9 |      HASH UNIQUE               |                             |     1 |  1578 |     6  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|* 10 |       HASH JOIN ANTI           |                             |     1 |  1578 |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |        PX RECEIVE              |                             |     1 |   552 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH           | :TQ10000                    |     1 |   552 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|* 13 |          VIEW                  |                             |     1 |   552 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  14 |           PX BLOCK ITERATOR    |                             |     1 |   600 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 15 |            TABLE ACCESS FULL   | SYS_TEMP_0FD9D7269_D4640C37 |     1 |   600 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  16 |        PX RECEIVE              |                             |     1 |  1026 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  17 |         PX SEND HASH           | :TQ10001                    |     1 |  1026 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  18 |          VIEW                  |                             |     1 |  1026 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  19 |           PX BLOCK ITERATOR    |                             |     1 |  1297 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|* 20 |            TABLE ACCESS FULL   | SYS_TEMP_0FD9D726A_D4640C37 |     1 |  1297 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  21 |   PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  22 |    PX SEND QC (RANDOM)         | :TQ20003                    |     1 |  1050 |     6  (34)| 00:00:01 |  Q2,03 | P->S | QC (RAND)  |
|  23 |     LOAD AS SELECT             |                             |       |       |            |          |  Q2,03 | PCWP |            |
|  24 |      HASH UNIQUE               |                             |     1 |  1050 |     6  (34)| 00:00:01 |  Q2,03 | PCWP |            |
|  25 |       PX RECEIVE               |                             |     1 |  1050 |     6  (34)| 00:00:01 |  Q2,03 | PCWP |            |
|  26 |        PX SEND HASH            | :TQ20002                    |     1 |  1050 |     6  (34)| 00:00:01 |  Q2,02 | P->P | HASH       |
|  27 |         HASH UNIQUE            |                             |     1 |  1050 |     6  (34)| 00:00:01 |  Q2,02 | PCWP |            |
|* 28 |          HASH JOIN             |                             |     1 |  1050 |     5  (20)| 00:00:01 |  Q2,02 | PCWP |            |
|  29 |           PX RECEIVE           |                             |     1 |   525 |     2   (0)| 00:00:01 |  Q2,02 | PCWP |            |
|  30 |            PX SEND HASH        | :TQ20000                    |     1 |   525 |     2   (0)| 00:00:01 |  Q2,00 | P->P | HASH       |
|  31 |             VIEW               |                             |     1 |   525 |     2   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|  32 |              PX BLOCK ITERATOR |                             |     1 |   525 |     2   (0)| 00:00:01 |  Q2,00 | PCWC |            |
|* 33 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D726B_D4640C37 |     1 |   525 |     2   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|  34 |           PX RECEIVE           |                             |     1 |   525 |     2   (0)| 00:00:01 |  Q2,02 | PCWP |            |
|  35 |            PX SEND HASH        | :TQ20001                    |     1 |   525 |     2   (0)| 00:00:01 |  Q2,01 | P->P | HASH       |
|  36 |             VIEW               |                             |     1 |   525 |     2   (0)| 00:00:01 |  Q2,01 | PCWP |            |
|  37 |              PX BLOCK ITERATOR |                             |     1 |   525 |     2   (0)| 00:00:01 |  Q2,01 | PCWC |            |
|* 38 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D726B_D4640C37 |     1 |   525 |     2   (0)| 00:00:01 |  Q2,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

3 - SELECT /*+ SHARED (8) */ "ID","CLIENT_ID","SYNC_ID","DOMAIN","LOG_DATE","URL_SOURCE","REMOVED","ACTIVE" FROM
"T_DOMAIN" "T_DOMAIN" (accessing 'REMOTE_SITE.RU' )

5 - SELECT /*+ SHARED (8) */ "ID","DOMAIN","RESTRICT_SOURCE" FROM "T_RESTRICTED_DOMAIN" "T_RESTRICTED_DOMAIN" (accessing
'REMOTE_SITE.RU' )

Note
-----
- Degree of Parallelism is 8 because of hint

— в плане можно отметить неправильную оценку cardinality удалённых и, как следствие, всех остальных источников данных (Rows=1 — известная проблема в операциях MVIEW с использованием dblinkРекурсивные запросы для materialized view с использованием db link). При этом, логично, полная стоимость запроса оказывается существенно заниженной Cost=15!

Стандартный трейс оптимизатора (10053 event) для запроса обновления MVIEW мне получить не удалось — возможно, используются другие механизмы оптимизации

Интересен, но непонятен также некий хинт /*+ SHARED (8) */, параметр которого в точности отражает установленную степень параллелизма в подсказке parallel

P.S. Механизм Automatic DOP (Oracle 11.2) не меняет ситуацию:

SQL> alter session set parallel_degree_policy = auto;

Session altered

SQL> @param parallel_degree

NAME                    VALUE  DSC
----------------------- ------ ----------------------------------------------------------------------
parallel_degree_limit   CPU    limit placed on degree of parallelism
parallel_degree_policy  AUTO   policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO)

Т.е. собственномеханизм Automatic DOP отрабатывает верно, например, при обновлении вышеописанного MVIEW без использования подсказки CARDINALITY для корректировки ошибочно расчитанной оптимизатором количества строк в удалённых таблицах Rows=1:

Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

— параллельное выполнение логично не применяется для 1-строчных таблиц

, а при указании в определении MVIEW реальных Cardinality:

WITH
 T_DOMAIN_ttt
AS (SELECT --+ materialize cardinality(t_domain 1000000)
         * FROM t_domain@balance_mv.remote_site.ru t_domain),
 t_restricted_domain_ttt
     AS (SELECT --+ materialize cardinality(t_restricted_domain 200)

при запуске стандартной процедуры обновления MVIEW:

SQL> exec DBMS_MVIEW.REFRESH('TTT_MV_URLS', 'C', ATOMIC_REFRESH=>FALSE);

Note
-----
- automatic DOP: Computed Degree of Parallelism is 16

— устанавливается какая-то разумная степень параллелизма

При указании заведомо завышенных значений Cardinality удалённых таблиц:

create materialized view TTT_MV_URLS
   on prebuilt table
   refresh complete on demand
as
WITH
 T_DOMAIN_ttt AS
   (SELECT --+ materialize cardinality(T_DOMAIN 100000000)    -- это я специально ОГРОМНЫЕ ЦИФРЫ написал, в т.ч. от cardinality CBO рассчитывает большой DOP=48
    *
    FROM t_domain@remote_site.ru T_DOMAIN),
 t_restricted_domain_ttt AS
   (SELECT --+ materialize cardinality(t_restricted_domain 20000)
    *
    FROM t_restricted_domain@remote_site.ru t_restricted_domain),
 s_tmp AS
   (SELECT --+ materialize
    DISTINCT domain, client_id
    FROM T_DOMAIN_ttt
    WHERE domain NOT IN
       (SELECT domain FROM t_restricted_domain_ttt WHERE domain IS NOT NULL)
    AND domain IS NOT NULL
    AND active = 1
    AND url_source = 'text')
SELECT DISTINCT cu.client_id  AS "SRC_CLIENT_ID",
                ccu.client_id AS "DST_CLIENT_ID"
   FROM s_tmp cu
   JOIN s_tmp ccu
   ON ccu.client_id <> cu.client_id
   AND ccu.domain = cu.domain;

при запуске стандартной процедуры обновления MVIEW:

SQL> exec DBMS_MVIEW.REFRESH('TTT_MV_URLS', 'C', ATOMIC_REFRESH=>FALSE);

оптимизатор предсказуемо увеличивает DOP:

Note
-----
- automatic DOP: Computed Degree of Parallelism is 48 because of degree limit

— однако на практике — всё остаётся без изменений — включается уже известное жёсткое ограничение DOP=2 для DML с удалёнными таблицами:

SQL> @v$sql_monitor
PX_MAXDOP PX_SERVERS_REQUESTED PX_SERVERS_ALLOCATED
--------- -------------------- --------------------
2                    8                    8

Т.о. в рассматриваемом случае Automatic DOP решает только задачу расчёта количества необходимых PX процессов, а выделяются параллельные процессы в соотвествие с ограничениями, одним из которых является использование в запросе удалённых таблиц

P.P.S Интересное описание тестов параллельных операций с matview (использующих локальные источники данных):

Sanjay Mishra.How to Refresh a Materialized View in Parallel

«… Важно отметить, что параметр PARALLELISM процедуры DBMS_MVIEW.REFRESH не помогает запустить обновление materialized view в параллельном режиме…»

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

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

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 такие блоггеры, как: