Oracle mechanics

27.10.2012

Особенность расчёта стоимости рекурсивных запросов при обновнении MATVIEW

Filed under: bugs,CBO,mview,Oracle — Игорь Усольцев @ 10:54
Tags: ,

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

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

рекурсивно выполняется наблюдаемый в V$SESSION и V$OPEN_CURSOR запрос типа V$OPEN_CURSOR.CURSOR_TYPE = ‘OPEN-RECURSIVE’:

INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "SCOTT"."MV_1" SELECT DISTINCT ...

Хорошо известно, что планы рекурсивных запросов полного обновления (а также планы CTAS создания CREATE MATERIALIZED VIEW) имеют особенности, например Рекурсивные запросы для materialized view с использованием db link

Далее — наглядный частный пример отличий оптимизации Oracle в части правил расчёта стоимости для рекурсиных и запросов, выполняемых «автономно»

Рекурсивный запрос выполняется медленно, неспешно выполняя NESTED LOOPS с источником / обзором с десятками миллионов строк:

11.2.0.3.@ SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('dap2m911mrryn', 3635044765));

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                         |       |       |     4 (100)|          |       |       |        |      |            |
|   1 |  LOAD AS SELECT                          |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                         |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                   |     1 |   372 |     4  (25)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     HASH UNIQUE                          |     1 |   372 |     4  (25)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                          |     1 |   372 |     3   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH                       |     1 |   372 |     3   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
|   7 |        NESTED LOOPS                      |     1 |   372 |     3   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            | -- ***
|   8 |         VIEW                             |     1 |   360 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            | -- недорогой UNION-ALL обзор из 1 строки,
|   9 |          UNION-ALL                       |       |       |            |          |       |       |  Q1,00 | PCWP |            | -- получаемой объединением 32М + 25М строк
|  10 |           FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR             |    32M|    10G| 94478   (1)| 00:22:03 |     1 |1048575|  Q1,00 | PCWC |            |
|  12 |             MAT_VIEW ACCESS FULL         |    32M|    10G| 94478   (1)| 00:22:03 |     1 |1048575|  Q1,00 | PCWP |            |
|  13 |           FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  14 |            PX BLOCK ITERATOR             |    25M|  8679M|  9831   (1)| 00:02:18 |     1 |1048575|  Q1,00 | PCWC |            |
|  15 |             MAT_VIEW ACCESS FULL         |    25M|  8679M|  9831   (1)| 00:02:18 |     1 |1048575|  Q1,00 | PCWP |            |
|  16 |         VIEW                             |   229 |  2748 |    22   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  17 |          UNION-ALL PARTITION             |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  18 |           FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  19 |            MAT_VIEW ACCESS BY INDEX ROWID|   114 |  1368 |    12   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  20 |             INDEX RANGE SCAN             |   114 |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  21 |           FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  22 |            MAT_VIEW ACCESS BY INDEX ROWID|   114 |  1368 |    11   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  23 |             INDEX RANGE SCAN             |   114 |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

— при этом план выполнения имеет общую стоимость Cost=4 при стоимости промежуточных операций MAT_VIEW ACCESS FULL 9831 и 94478 — что немного нарушает простые арифметические правила

Проверить предположение можно, выполнив тот же запрос автономно, например, из SQL*Plus. Чтобы достичь близкого плана выполнения придётся воспользоваться подсказкой /*+ USE_NL(a b) */, напрямую указывающей необходимый метод доступа, иначе Oracle из разумных соображений стоимости упорно будет выбирать более эффективный и дешёвый HASH JOIN.

В плане выполнения «автономного» запроса Oracle показывает совсем другой, гораздо более разумный расчёт стоимости:

SQL> explain plan for
  2  INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO "SCOTT"."MV_1"
  3  SELECT /*+ USE_NL(a b) */ DISTINCT
 ...
 16  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                          |   115 | 42205 |    51M  (1)|198:55:36 |       |       |        |      |            |
|   1 |  LOAD AS SELECT                           |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                          |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                    |   115 | 42205 |    51M  (1)|198:55:36 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     HASH UNIQUE                           |   115 | 42205 |    51M  (1)|198:55:36 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                           |   115 | 42205 |    51M  (1)|198:55:36 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH                        |   115 | 42205 |    51M  (1)|198:55:36 |       |       |  Q1,00 | P->P | HASH       |
|   7 |        HASH UNIQUE                        |   115 | 42205 |    51M  (1)|198:55:36 |       |       |  Q1,00 | PCWP |            | -- имхо, избыточная операция удаления дубликатов
|   8 |         NESTED LOOPS                      |    14G|  5107G|    51M  (1)|198:49:29 |       |       |  Q1,00 | PCWP |            |
|   9 |          VIEW                             |    65M|    21G|   109K  (1)| 00:25:30 |       |       |  Q1,00 | PCWP |            | -- тот же UNION-ALL обзор - 65М = 32М + 32М
|  10 |           UNION-ALL                       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|* 11 |            FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  12 |             PX BLOCK ITERATOR             |    32M|    10G| 94478   (1)| 00:22:03 |     1 |1048575|  Q1,00 | PCWC |            |
|* 13 |              MAT_VIEW ACCESS FULL         |    32M|    10G| 94478   (1)| 00:22:03 |     1 |1048575|  Q1,00 | PCWP |            |
|* 14 |            FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  15 |             PX BLOCK ITERATOR             |    32M|    10G| 14799   (1)| 00:03:28 |     1 |1048575|  Q1,00 | PCWC |            |
|* 16 |              MAT_VIEW ACCESS FULL         |    32M|    10G| 14799   (1)| 00:03:28 |     1 |1048575|  Q1,00 | PCWP |            |
|  17 |          VIEW                             |     1 |     7 |    23   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  18 |           UNION ALL PUSHED PREDICATE      |       |       |            |          |       |       |  Q1,00 | PCWP |            | -- операция доступна с 10.2, не удаляет дубликаты
|* 19 |            FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  20 |             MAT_VIEW ACCESS BY INDEX ROWID|   114 |  1368 |    11   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 21 |              INDEX RANGE SCAN             |   114 |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 22 |            FILTER                         |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  23 |             MAT_VIEW ACCESS BY INDEX ROWID|   114 |  1368 |    11   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 24 |              INDEX RANGE SCAN             |   114 |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------

— при том же порядке и методах доступа стоимость плана правильно складывается и достигает 51М против 4 в случае рекурсивного запроса. Кроме того, логично и правдоподобно оцениваются получаемое количество строк и предполагаемое время выполнения — свыше 198 часов!

Совпадающий с рекурсивным план выполнения можно получить с помощью понижения значения OPTIMIZER_FEATURES_ENABLE до версии 10.1, например, подсказкой /*+ OPTIMIZER_FEATURES_ENABLE(‘10.1.0’) USE_NL(a b) */:

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                         |  7489K|  2657M|       | 26314   (1)| 00:06:09 |       |       |        |      |            |
|   1 |  LOAD AS SELECT                          |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                         |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                   |  7489K|  2657M|       | 26314   (1)| 00:06:09 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT UNIQUE                          |  7489K|  2657M|  5443M| 26314   (1)| 00:06:09 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                          |  7489K|  2657M|       | 13413   (1)| 00:03:08 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH                       |  7489K|  2657M|       | 13413   (1)| 00:03:08 |       |       |  Q1,00 | P->P | HASH       |
|   7 |        NESTED LOOPS                      |  7489K|  2657M|       | 13413   (1)| 00:03:08 |       |       |  Q1,00 | PCWP |            |
|   8 |         VIEW                             | 32720 |    11M|       |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            | -- Cost=2 !
|   9 |          UNION-ALL                       |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|* 10 |           FILTER                         |       |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  11 |            PX BLOCK ITERATOR             |    33M|    11G|       | 94938   (1)| 00:22:10 |     1 |1048575|  Q1,00 | PCWC |            |
|* 12 |             MAT_VIEW ACCESS FULL         |    33M|    11G|       | 94938   (1)| 00:22:10 |     1 |1048575|  Q1,00 | PCWP |            |
|* 13 |           FILTER                         |       |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  14 |            PX BLOCK ITERATOR             |    30M|    10G|       | 14862   (1)| 00:03:29 |     1 |1048575|  Q1,00 | PCWC |            |
|* 15 |             MAT_VIEW ACCESS FULL         |    30M|    10G|       | 14862   (1)| 00:03:29 |     1 |1048575|  Q1,00 | PCWP |            |
|  16 |         VIEW                             |   229 |  2748 |       |    12   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  17 |          UNION-ALL PARTITION             |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|* 18 |           FILTER                         |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  19 |            MAT_VIEW ACCESS BY INDEX ROWID|   114 |  1368 |       |    11   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 20 |             INDEX RANGE SCAN             |   114 |       |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 21 |           FILTER                         |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  22 |            MAT_VIEW ACCESS BY INDEX ROWID|   114 |  1368 |       |    11   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 23 |             INDEX RANGE SCAN             |   114 |       |       |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------

Суммарная стоимость выше чем у рекурсивного запроса, но при расчётах так же странно соблюдаюся правила математики

В документах MOS описаны многочисленные особенности рекурсивных планов выполнения создания и полного обновления материализованных обзоров типа: Bug.6840494 CBQT DISALLOWED FOR CTAS UNDER CREATE MATERIALIZED VIEW / IAS UNDER REFRESH с метками not feasible to fix, нулевая стоимость доступа по уникальным индексам и т.д. Интересно, что большинство этих отличий проявилось начиная с версии 10.2, из чего можно предположить что факт получения сходного плана при OFE=10.1 не является случайным

Встречаются даже рекомендации использовать в запросах создания матвью секцию OUTLINE плана выполнения автономного запроса в виде:

 /*+
     BEGIN_OUTLINE_DATA
     ...
     END_OUTLINE_DATA
 */

целиком — How to Troubleshoot Slow CREATE MATERIALIZED VIEW Issues [ID 1313218.1]

Для того чтобы добиться выбора разумного с точки зрения скорости выполнения  плана при обновлении рассматриваемого матвью необходимо и достаточно было указать правильный метод доступа подсказкой USE_HASH в определении CREATE 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 такие блоггеры, как: