Oracle mechanics

28.03.2013

Запросы с UNION ALL и высокой стоимостью в 11.2.0.3

Filed under: bugs,CBO,Oracle,SQL Tuning — Игорь Усольцев @ 00:43
Tags: , ,

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

11.2.0.3.@ SQL> explain plan for -- Case#1
  2  select
  3  bg1.client_id
  4  from
  5      VIEW_W_UNION_ALL bg1
  6      left join VIEW_W_UNION_ALL bg2
  7        on bg2.client_id = bg1.client_id and
  8           bg2.product_id = bg1.product_id and
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id,
 12        bg1.product_id,
 13        bg1.start_dt
 14  /

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                               |  2359K|    76M|       |   922M (74)|999:59:59 |
|   1 |  HASH GROUP BY                      |                               |  2359K|    76M|   192T|   922M (74)|999:59:59 |
|   2 |   NESTED LOOPS OUTER                |                               |  4761G|   147T|       |    73M  (1)|225:33:27 | -- не самая быстрая операция для больших наборов данных
|   3 |    VIEW                             | VIEW_W_UNION_ALL              |  4098K|    74M|       | 17754   (3)| 00:03:16 |
|   4 |     UNION-ALL                       |                               |       |       |       |            |          |
|*  5 |      FILTER                         |                               |       |       |       |            |          |
|*  6 |       MAT_VIEW ACCESS FULL          | MAT_VIEW_________1            |  2028K|    36M|       |  8877   (3)| 00:01:38 |
|*  7 |      FILTER                         |                               |       |       |       |            |          |
|*  8 |       MAT_VIEW ACCESS FULL          | MAT_VIEW_________2            |  2069K|    37M|       |  8877   (3)| 00:01:38 |
|*  9 |    VIEW                             | VIEW_W_UNION_ALL              |     1 |    15 |       |    18   (0)| 00:00:01 |
|  10 |     UNION ALL PUSHED PREDICATE      |                               |       |       |       |            |          |
|* 11 |      FILTER                         |                               |       |       |       |            |          |
|* 12 |       MAT_VIEW ACCESS BY INDEX ROWID| MAT_VIEW_________1            |     1 |    19 |       |     9   (0)| 00:00:01 |
|* 13 |        INDEX RANGE SCAN             | IDX_MAT_VIEW______1_CLIENT_ID |     6 |       |       |     3   (0)| 00:00:01 |
|* 14 |      FILTER                         |                               |       |       |       |            |          |
|* 15 |       MAT_VIEW ACCESS BY INDEX ROWID| MAT_VIEW_________2            |     1 |    19 |       |     9   (0)| 00:00:01 |
|* 16 |        INDEX RANGE SCAN             | IDX_MAT_VIEW______2_CLIENT_ID |     6 |       |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

LEFT JOIN в этом запросе может показаться излишним, что не совсем так — здесь выделена только проблемная часть более комплексного запроса, действительно требующего соединения. Хотя и непонятно, отчего Oracle не может на этапе разбора исключить явно избыточное внешнее соединение — но сейчас интересен собственно запрос с большой стоимостью выполнения (причина чего, вероятнее всего, как-то связана с неточной оценкой количества строк, получаемого в результате NESTED LOOPS OUTER — 4761G)
Интересно, что до добавления одного из условий соединения план запроса имел гораздо более разумный вид, используя более подходящую для соединения больших объёмов данных операцию HASH JOIN OUTER в отличие от NESTED LOOPS OUTER, использованной в предыдущем (проблемном) случае Case#1:

SQL> explain plan for -- Case#2
  2  select
  3  bg1.client_id
  4  from
  5      VIEW_W_UNION_ALL bg1
  6      left join VIEW_W_UNION_ALL bg2
  7        on bg2.client_id = bg1.client_id and
  8  --         bg2.product_id = bg1.product_id and       -- исключённое условие
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id,
 12        bg1.product_id,
 13        bg1.start_dt
 14  /

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |  6955K|   225M|       |   160K  (1)| 00:29:22 |-- адекватные стоимость и время выполнения
|   1 |  HASH GROUP BY            |                    |  6955K|   225M|   295M|   160K  (1)| 00:29:22 |-- гораздо более подходящая операция
|*  2 |   HASH JOIN OUTER         |                    |  6955K|   225M|   121M| 53337   (2)| 00:09:47 |
|   3 |    VIEW                   | VIEW_W_UNION_ALL   |  4098K|    74M|       | 17754   (3)| 00:03:16 |
|   4 |     UNION-ALL             |                    |       |       |       |            |          |
|*  5 |      FILTER               |                    |       |       |       |            |          |
|*  6 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  2028K|    36M|       |  8877   (3)| 00:01:38 |
|*  7 |      FILTER               |                    |       |       |       |            |          |
|*  8 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  2069K|    37M|       |  8877   (3)| 00:01:38 |
|   9 |    VIEW                   | VIEW_W_UNION_ALL   |  8155K|   116M|       | 17541   (1)| 00:03:13 |
|  10 |     UNION-ALL             |                    |       |       |       |            |          |
|* 11 |      FILTER               |                    |       |       |       |            |          |
|  12 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  4079K|    54M|       |  8770   (1)| 00:01:37 |
|* 13 |      FILTER               |                    |       |       |       |            |          |
|  14 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  4075K|    54M|       |  8770   (1)| 00:01:37 |
--------------------------------------------------------------------------------------------------------

— понятно, что добавление условия соединения логично уменьшив кол-во планируемо-возвращаемых запросом строк c 6955K (Case#2) до 2359K (Case#1) парадоксально на много порядков увеличило стоимость запроса

Может ли это быть связано с ошибочным выбором операции внешнего соединения?:

SQL> explain plan for -- Case#3
  2  select--+ use_hash(bg1 bg2)
  3  bg1.client_id
  4  from
  5      VIEW_W_UNION_ALL bg1
  6      left join VIEW_W_UNION_ALL bg2
  7        on bg2.client_id = bg1.client_id and
  8           bg2.product_id = bg1.product_id and
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id, bg1.product_id, bg1.start_dt
 12  /

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |    11M|   438M|       |  1802M (40)|999:59:59 | -- стоимость всего запроса возросла
|   1 |  HASH GROUP BY            |                    |    11M|   438M|   227T|  1802M (40)|999:59:59 |
|*  2 |   HASH JOIN OUTER         |                    |  4761G|   168T|   121M|    35M(100)|108:50:05 | -- стоимость операции соединения ожидаемо уменьшилась
|   3 |    VIEW                   | VIEW_W_UNION_ALL   |  4098K|    74M|       | 17754   (3)| 00:03:16 |
|   4 |     UNION-ALL             |                    |       |       |       |            |          |
|*  5 |      FILTER               |                    |       |       |       |            |          |
|*  6 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  2028K|    36M|       |  8877   (3)| 00:01:38 |
|*  7 |      FILTER               |                    |       |       |       |            |          |
|*  8 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  2069K|    37M|       |  8877   (3)| 00:01:38 |
|   9 |    VIEW                   | VIEW_W_UNION_ALL   |  8155K|   155M|       | 17541   (1)| 00:03:13 |
|  10 |     UNION-ALL             |                    |       |       |       |            |          |
|* 11 |      FILTER               |                    |       |       |       |            |          |
|  12 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  4079K|    73M|       |  8770   (1)| 00:01:37 |
|* 13 |      FILTER               |                    |       |       |       |            |          |
|  14 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  4075K|    73M|       |  8770   (1)| 00:01:37 |
--------------------------------------------------------------------------------------------------------

— увы, план проблемного запроса при использовании казалось бы «правильной» операции HASH JOIN OUTER показывает ещё большую стоимость!

Что неожиданно, поскольку в  Case#2 с удалённым условием, например, при замене HASH JOIN OUTER на NESTED LOOPS OUTER стоимость, напротив, предсказуемо значительно увеличивается:

SQL> explain plan for -- Case#4
  2  select--+ use_nl(bg1 bg2)
  3  bg1.client_id
  4  from
  5      VIEW_W_UNION_ALL bg1
  6      left join VIEW_W_UNION_ALL bg2
  7        on bg2.client_id = bg1.client_id and
  8  --         bg2.product_id = bg1.product_id and       -- исключённое условие
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id,
 12        bg1.product_id,
 13        bg1.start_dt
 14  /

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                               |  7003K|   220M|       |    74M  (1)|227:43:30 |
|   1 |  HASH GROUP BY                      |                               |  7003K|   220M|   297M|    74M  (1)|227:43:30 |
|   2 |   NESTED LOOPS OUTER                |                               |  7003K|   220M|       |    74M  (1)|227:24:11 |
|   3 |    VIEW                             | VIEW_W_UNION_ALL              |  4131K|    74M|       | 17702   (3)| 00:03:15 |
|   4 |     UNION-ALL                       |                               |       |       |       |            |          |
|*  5 |      FILTER                         |                               |       |       |       |            |          |
|*  6 |       MAT_VIEW ACCESS FULL          | MAT_VIEW_________1            |  2056K|    37M|       |  8824   (3)| 00:01:38 |
|*  7 |      FILTER                         |                               |       |       |       |            |          |
|*  8 |       MAT_VIEW ACCESS FULL          | MAT_VIEW_________2            |  2075K|    37M|       |  8877   (3)| 00:01:38 |
|   9 |    VIEW                             | VIEW_W_UNION_ALL              |     1 |    14 |       |    18   (0)| 00:00:01 |
|  10 |     UNION ALL PUSHED PREDICATE      |                               |       |       |       |            |          |
|* 11 |      FILTER                         |                               |       |       |       |            |          |
|* 12 |       MAT_VIEW ACCESS BY INDEX ROWID| MAT_VIEW_________1            |     1 |    14 |       |     9   (0)| 00:00:01 |
|* 13 |        INDEX RANGE SCAN             | IDX_MAT_VIEW______1_CLIENT_ID |     6 |       |       |     3   (0)| 00:00:01 |
|* 14 |      FILTER                         |                               |       |       |       |            |          |
|* 15 |       MAT_VIEW ACCESS BY INDEX ROWID| MAT_VIEW_________2            |     1 |    14 |       |     9   (0)| 00:00:01 |
|* 16 |        INDEX RANGE SCAN             | IDX_MAT_VIEW______2_CLIENT_ID |     6 |       |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

— т.е. преимущество в стоимости HASH JOIN перед NESTED LOOPS на значительных объёмах данных теряется где-то в расчётах оптимизатора ;)

Общим для обоих проблемных запросов (Case#1 и Case#3) является очевидная ошибочная оценка кол-ва возвращаемых операциями внешнего соединения (и NESTED LOOPS OUTER, и HASH JOIN OUTER) строк — 4761G, что не согласуется с результатом аналогичной операции в плане запроса с более слабыми условиями соединения (Case#1) — 6955K и простой логикой!

Похоже на баг, и, действительно, при понижении версии оптимизатора до 11.2.0.2 эффект неадекватной стоимости пропадает, стоимость и операции в плане выполнения «нормализуются:

SQL> explain plan for
  2  select--+ OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
  3  bg1.client_id
  4  from
  5      VIEW_W_UNION_ALL bg1
  6      left join VIEW_W_UNION_ALL bg2
  7        on bg2.client_id = bg1.client_id and
  8           bg2.product_id = bg1.product_id and
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id,
 12        bg1.product_id,
 13        bg1.start_dt
 14  /

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |  4098K|   152M|       |   124K  (1)| 00:22:54 |
|   1 |  HASH GROUP BY            |                    |  4098K|   152M|   205M|   124K  (1)| 00:22:54 |
|*  2 |   HASH JOIN OUTER         |                    |  4098K|   152M|   121M| 55435   (2)| 00:10:10 |
|   3 |    VIEW                   | VIEW_W_UNION_ALL   |  4098K|    74M|       | 17754   (3)| 00:03:16 |
|   4 |     UNION-ALL             |                    |       |       |       |            |          |
|*  5 |      FILTER               |                    |       |       |       |            |          |
|*  6 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  2028K|    36M|       |  8877   (3)| 00:01:38 |
|*  7 |      FILTER               |                    |       |       |       |            |          |
|*  8 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  2069K|    37M|       |  8877   (3)| 00:01:38 |
|   9 |    VIEW                   | VIEW_W_UNION_ALL   |  8155K|   155M|       | 17541   (1)| 00:03:13 |
|  10 |     UNION-ALL             |                    |       |       |       |            |          |
|* 11 |      FILTER               |                    |       |       |       |            |          |
|  12 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  4079K|    73M|       |  8770   (1)| 00:01:37 |
|* 13 |      FILTER               |                    |       |       |       |            |          |
|  14 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  4075K|    73M|       |  8770   (1)| 00:01:37 |
--------------------------------------------------------------------------------------------------------

Влияет ли на аномальную стоимость операция UNION ALL, используемая в определении обзора VIEW_W_UNION_ALL:

SQL> explain plan for
  2  select
  3  bg1.client_id
  4  from
  5      MAT_VIEW_________1 bg1
  6      left join MAT_VIEW_________1 bg2
  7        on bg2.client_id = bg1.client_id and
  8           bg2.product_id = bg1.product_id and
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id, bg1.product_id, bg1.start_dt
 12  /

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |  2028K|    73M|       | 61212   (1)| 00:11:14 |
|   1 |  HASH GROUP BY         |                    |  2028K|    73M|    94M| 61212   (1)| 00:11:14 |
|*  2 |   HASH JOIN OUTER      |                    |  2028K|    73M|    59M| 27480   (2)| 00:05:03 |
|*  3 |    MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  2028K|    36M|       |  8877   (3)| 00:01:38 |
|   4 |    MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  4079K|    73M|       |  8770   (1)| 00:01:37 |
-----------------------------------------------------------------------------------------------------

— очень похоже, что влияет: без UNION ALL план запроса теряет оригинальность!

При проверке фиксов, добавленных в версии 11.2.0.3 и имеющих отношение к UNION ALL:

SQL> select bugno, description, optimizer_feature_enable, is_default
  2    from v$session_fix_control
  3   where session_id = sys_context('USERENV', 'SID')
  4     and OPTIMIZER_FEATURE_ENABLE = '11.2.0.3'
  5     and upper(description) like '%UNION%'
  6  /

     BUGNO DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE  IS_DEFAULT
---------- ---------------------------------------------------------------- ------------------------- ----------
  11814428 use union all view stats for colgroup cardinality sanity check   11.2.0.3                        1
  11668189 parallelize top-level union all if PDDL or PDML                  11.2.0.3                        1
  11881047 non top-level union is parallel if at least one branch is parall 11.2.0.3                        1

— по описанию хорошо согласуется с наблюдаемыми проблемами фикс 11814428. К тому же проблема наблюдается без параллельного выполнения (хотя и при параллельном выполнении также присутствует)

При отключении фикса 11814428:

SQL> explain plan for
  2  select--+ OPT_PARAM('_fix_control' '11814428:0')
  3  bg1.client_id
  4  from
  5      VIEW_W_UNION_ALL bg1
  6      left join VIEW_W_UNION_ALL bg2
  7        on bg2.client_id = bg1.client_id and
  8           bg2.product_id = bg1.product_id and
  9           bg2.start_dt = add_months(bg1.start_dt, -12)
 10  where bg1.start_dt > add_months(sysdate, -6)
 11  group by bg1.client_id, bg1.product_id, bg1.start_dt
 12  /

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |  4098K|   152M|       |   124K  (1)| 00:22:54 |
|   1 |  HASH GROUP BY            |                    |  4098K|   152M|   205M|   124K  (1)| 00:22:54 |
|*  2 |   HASH JOIN OUTER         |                    |  4098K|   152M|   121M| 55435   (2)| 00:10:10 |
|   3 |    VIEW                   | VIEW_W_UNION_ALL   |  4098K|    74M|       | 17754   (3)| 00:03:16 |
|   4 |     UNION-ALL             |                    |       |       |       |            |          |
|*  5 |      FILTER               |                    |       |       |       |            |          |
|*  6 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  2028K|    36M|       |  8877   (3)| 00:01:38 |
|*  7 |      FILTER               |                    |       |       |       |            |          |
|*  8 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  2069K|    37M|       |  8877   (3)| 00:01:38 |
|   9 |    VIEW                   | VIEW_W_UNION_ALL   |  8155K|   155M|       | 17541   (1)| 00:03:13 |
|  10 |     UNION-ALL             |                    |       |       |       |            |          |
|* 11 |      FILTER               |                    |       |       |       |            |          |
|  12 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________1 |  4079K|    73M|       |  8770   (1)| 00:01:37 |
|* 13 |      FILTER               |                    |       |       |       |            |          |
|  14 |       MAT_VIEW ACCESS FULL| MAT_VIEW_________2 |  4075K|    73M|       |  8770   (1)| 00:01:37 |
--------------------------------------------------------------------------------------------------------

— план возвращается к ожидаемому виду.

После описанных тестов достаточно просто найти связанный с найденным фиксом Bug 14467202 : COST OF COMPLEX SQL JUMPS TO 18E WITH 11814428, и заказать порт для своей версии

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

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

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