Oracle mechanics

24.11.2012

Особенности расчёта cardinality в материализованных подзапросах при обновлении 11.1 -> 11.2

Filed under: CBO,heuristics,Oracle,parameters,SQL — Игорь Усольцев @ 11:10
Tags: , , , ,

После обновления 11.1.0.7 -> 11.2.0.3 БОЛЬШОЙ ЗАПРОС перешёл к бесконечным временам выполнения, причиной чему стала неточность определения cardinality некого подзапроса, вынесенного в секцию WITH основного запроса, и автоматически материализованного Oracle ввиду неоднократности использования

План проблемного подзапроса, выполняемого отдельно, без необходимости TEMP TABLE TRANSFORMATION показывает не вполне точные, но разумные оценки:

11.2.0.3.@ SQL> WITH SHOP_OFF AS
  2   (SELECT
  3     vcap.from_time,
  4     vcap.datasource_id,
  5     t.VALUE cause,
  6     trunc(sysdate - 3) - trunc(first_off.day_off)
  7      FROM (SELECT MIN(from_time) day_off, datasource_id
  8              FROM v_current_active_period
  9             WHERE to_time IS NULL
 10             GROUP BY datasource_id) first_off,
 11           v_current_active_period vcap,
 12           term t
 13     WHERE vcap.period_type_code = t.code
 14       AND t.dictionary_id = 40
 15       AND t.code IN (3, 12, 5)
 16       and trunc(sysdate - 3) - trunc(first_off.day_off) in (17, 21)
 17       and first_off.datasource_id = vcap.datasource_id
 18       and first_off.day_off = vcap.from_time)
 19  select * from SHOP_OFF
 20  /

82 rows selected.

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |   274 | 25756 |   201   (3)| 00:00:01 | -- разумная оценка: Rows=274 при 82 rows selected
|*  1 |  FILTER                         |                 |       |       |            |          | -- FILTER выполняется после GROUP BY
|   2 |   HASH GROUP BY                 |                 |   274 | 25756 |   201   (3)| 00:00:01 | -- Complex View Merging работает
|*  3 |    HASH JOIN                    |                 | 13739 |  1261K|   199   (2)| 00:00:01 |
|*  4 |     HASH JOIN                   |                 |  5623 |   444K|   101   (2)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| TERM            |     1 |    59 |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | DICTIONARY_CODE |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL          | OPEN_CUTOFF     | 99907 |  2146K|    98   (2)| 00:00:01 |
|   8 |     TABLE ACCESS FULL           | OPEN_CUTOFF     | 99907 |  1268K|    98   (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   1 - filter((TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=17 OR
              TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=21) AND "FROM_TIME"=MIN("FROM_TIME"))
   3 - access("DATASOURCE_ID"="DATASOURCE_ID")
   4 - access("TYPE"="T"."CODE")
   6 - access("T"."DICTIONARY_ID"=40)
       filter("T"."CODE"=3 OR "T"."CODE"=5 OR "T"."CODE"=12)
   7 - filter("TYPE"=3 OR "TYPE"=5 OR "TYPE"=12)

Проблема с оценкой cardinality отчётливо проявляется при искуственной материализации подзапроса подсказкой:

SQL> WITH SHOP_OFF AS
  2   (SELECT /*+ materialize */
...
 20  /

82 rows selected.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |     1 |  2030 |   203   (3)| 00:00:01 | -- неправильная оценка Rows=1, порождающая проблемы в основном запросе
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D71A3_B49AC5D6 |       |       |            |       |
|   3 |    NESTED LOOPS                |                             |       |       |            |       |
|   4 |     NESTED LOOPS               |                             |     1 |    86 |   201   (3)| 00:00:01 |
|*  5 |      HASH JOIN                 |                             |     1 |    30 |   200   (3)| 00:00:01 | -- проблема cardinality начинается с этого JOIN
|   6 |       VIEW                     |                             |   814 | 11396 |   102   (5)| 00:00:01 |
|*  7 |        FILTER                  |                             |       |       |            |       |
|   8 |         SORT GROUP BY          |                             |   814 | 10582 |   102   (5)| 00:00:01 | -- Complex View Merging не работает
|   9 |          TABLE ACCESS FULL     | OPEN_CUTOFF                 | 99907 |  1268K|    98   (2)| 00:00:01 |
|* 10 |       TABLE ACCESS FULL        | OPEN_CUTOFF                 | 99907 |  1561K|    98   (2)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN         | DICTIONARY_CODE             |     1 |       |     0   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID| TERM                        |     1 |    56 |     1   (0)| 00:00:01 |
|  13 |   VIEW                         |                             |     1 |  2030 |     2   (0)| 00:00:01 |
|  14 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D71A3_B49AC5D6 |     1 |    75 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

   5 - access("FIRST_OFF"."DATASOURCE_ID"="DATASOURCE_ID" AND "FIRST_OFF"."DAY_OFF"="FROM_TIME")
   7 - filter(TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=17 OR
              TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=21)
  10 - filter("TYPE"=3 OR "TYPE"=5 OR "TYPE"=12)
  11 - access("T"."DICTIONARY_ID"=40 AND "TYPE"="T"."CODE")
       filter("T"."CODE"=3 OR "T"."CODE"=5 OR "T"."CODE"=12)

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

  1. неправильно оценивается HASH JOIN cardinality на шаге 5 плана, из чего происходит общая неправильная оценка кол-ва строк подзапроса
  2. не работает Complex View Merging для inline обзора с GROUP BY

Проблема №1

отражается в трейсе 10053 в виде заниженной оценке избирательности соединения sel (0.000000):

Join Card:  0.024482 = outer (99796.000000) * inner (814.000000) * sel (0.000000) ### Here be Dragons
Join Card - Rounded: 1 Computed: 0.02
  Outer table:  OPEN_CUTOFF  Alias: OPEN_CUTOFF
    resc: 99.85  card 99796.00  bytes: 16  deg: 1  resp: 99.85
  Inner table:  from$_subquery$_002  Alias: FIRST_OFF
    resc: 101.57  card: 814.00  bytes: 14  deg: 1  resp: 101.57
...
Best:: JoinMethod: Hash
       Cost: 200.29  Degree: 1  Resp: 200.29  Card: 0.02 Bytes: 30

и отчётливо проявляется при изменении OFE 11.1.0.7 -> 11.2.0.1:

SQL> alter session set optimizer_features_enable='11.1.0.7';

Session altered.

SQL> WITH SHOP_OFF AS
  2   (SELECT /*+ materialize */
...

82 rows selected.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |    17 | 34510 |   205   (4)| 00:00:03 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D71A8_B49AC5D6 |       |       |            |       |
|*  3 |    HASH JOIN                   |                             |    17 |  1462 |   203   (4)| 00:00:03 |
|*  4 |     HASH JOIN                  |                             |   309 |  9270 |   200   (3)| 00:00:03 | -- Rows=309, оценка существенно отличается в лучшую сторону
|   5 |      VIEW                      |                             |   814 | 11396 |   102   (5)| 00:00:02 |
|*  6 |       FILTER                   |                             |       |       |            |       |
|   7 |        SORT GROUP BY           |                             |   814 | 10582 |   102   (5)| 00:00:02 |
|   8 |         TABLE ACCESS FULL      | OPEN_CUTOFF                 | 99907 |  1268K|    98   (2)| 00:00:02 |
|*  9 |      TABLE ACCESS FULL         | OPEN_CUTOFF                 | 37982 |   593K|    98   (2)| 00:00:02 |
|  10 |     TABLE ACCESS BY INDEX ROWID| TERM                        |     1 |    56 |     2   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN          | DICTIONARY_CODE             |     1 |       |     1   (0)| 00:00:01 |
|  12 |   VIEW                         |                             |    17 | 34510 |     2   (0)| 00:00:01 |
|  13 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D71A8_B49AC5D6 |    17 |  1275 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Причина может быть найдена среди BUGFIX CBO версии 11.2.0.1:

SQL> select * from v$system_fix_control where bugno = 8408665;

  BUGNO VALUE SQL_FEATURE               DESCRIPTION                                                   OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
------- ----- ------------------------- ------------------------------------------------------------- ------------------------- ----- ----------
8408665     1 QKSFM_CARDINALITY_8408665 allow join selectivity cdn sanity check when unanlyzed tables 11.2.0.1                      0          1

— под unanalyzed tables, очевидно, подразумевается inline обзор FIRST_OFF (from$_subquery$_002 в трейсе оптимизатора), с которым по каким-то причинам не была выполнена операция View Merging

Тест:

SQL> WITH SHOP_OFF AS
  2   (SELECT /*+ materialize OPT_PARAM('_fix_control' '8408665:0') */
...
 20  /

82 rows selected.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |    46 | 93380 |   205   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D71E5_B49AC5D6 |       |       |            |       |
|*  3 |    HASH JOIN                   |                             |    46 |  3956 |   203   (4)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TERM                        |     1 |    56 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | DICTIONARY_CODE             |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     HASH JOIN                  |                             |   814 | 24420 |   200   (3)| 00:00:01 | -- Rows=814, sanity check отключен
|   7 |      VIEW                      |                             |   814 | 11396 |   102   (5)| 00:00:01 |
|*  8 |       FILTER                   |                             |       |       |            |       |
|   9 |        SORT GROUP BY           |                             |   814 | 10582 |   102   (5)| 00:00:01 |
|  10 |         TABLE ACCESS FULL      | OPEN_CUTOFF                 | 99907 |  1268K|    98   (2)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL         | OPEN_CUTOFF                 | 99907 |  1561K|    98   (2)| 00:00:01 |
|  12 |   VIEW                         |                             |    46 | 93380 |     2   (0)| 00:00:01 |
|  13 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D71E5_B49AC5D6 |    46 |  3450 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

— при отключении fix_control 8408665 избирательность HASH JOIN на шаге 6 разумно увеличивается, что влияет и на изменение плана в целом, и на более правильную общую оценку кол-ва строк, возвращаемых всем маиериализованным подзапросом — 46

При поиске в MOS по ключевому слову 8408665 обнаруживается Bug 14468519 : INCORRECT CARDINALITY ESTIMATION LEADS TO POOR PLAN, в котром кроме отключения fix_control 8408665 для решения схожей проблемы рекомендуют отключение параметра:

SQL> @param_ _optimizer_enable_extended_stats

NAME                             VALUE IS_DEF DSC
-------------------------------- ----- ------ --------------------------------------------------
_optimizer_enable_extended_stats TRUE  TRUE   use extended statistics for selectivity estimation

, что действительно приводит к аналогично разумному результату:

SQL> WITH SHOP_OFF AS
  2   (SELECT /*+ materialize  OPT_PARAM('_optimizer_enable_extended_stats' 'FALSE')*/
...
 20  /

82 rows selected.

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |    46 | 93380 |   205   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D71EF_B49AC5D6 |       |       |            |       |
|*  3 |    HASH JOIN                   |                             |    46 |  3956 |   203   (4)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TERM                        |     1 |    56 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | DICTIONARY_CODE             |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     HASH JOIN                  |                             |   814 | 24420 |   200   (3)| 00:00:01 |
|   7 |      VIEW                      |                             |   814 | 11396 |   102   (5)| 00:00:01 |
|*  8 |       FILTER                   |                             |       |       |            |       |
|   9 |        SORT GROUP BY           |                             |   814 | 10582 |   102   (5)| 00:00:01 |
|  10 |         TABLE ACCESS FULL      | OPEN_CUTOFF                 | 99907 |  1268K|    98   (2)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL         | OPEN_CUTOFF                 | 99907 |  1561K|    98   (2)| 00:00:01 |
|  12 |   VIEW                         |                             |    46 | 93380 |     2   (0)| 00:00:01 |
|  13 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D71EF_B49AC5D6 |    46 |  3450 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Проблема №2: Complex View Merging

Информацию о невозможности Complex View Merging в материализованном подзапросе в отличие от НЕматериализованного можно также найти в 10053 трейсе:

CVM:     CVM bypassed: Aggregate in OR predicate.

, что формально правильно для условия запроса:

 16       and trunc(sysdate - 3) - trunc(first_off.day_off) in (17, 21)

, преобразованного в предикат плана:

   7 - filter(TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=17 OR
              TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=21)

— хотя в случае нематериализуемого подзапроса (самый первый план выполнения) Oracle находит возможность переместить операцию FILTER с этим условием на последний шаг выполнения запроса и успешно выполнить CVM — т.е. такое поведение похоже на особенность оптимизатора именно при материализации подзапроса

Искусственно добиться Complex View Merging можно и для материализованного подзапроса, но для этого потребуется вручную изменить текст, эквивалентно переместив предикат в тело inline view FIRST_OFF:

SQL> WITH SHOP_OFF AS
  2   (SELECT /*+ materialize */
  3     vcap.from_time,
  4     vcap.datasource_id,
  5     t.VALUE cause,
  6     trunc(sysdate - 3) - trunc(first_off.day_off)
  7      FROM (SELECT MIN(from_time) day_off, datasource_id
  8              FROM v_current_active_period
  9             WHERE to_time IS NULL
 10             GROUP BY datasource_id
 11  having trunc(sysdate - 3) - trunc(MIN(from_time)) in (17, 21)) first_off, -- перемещённый в тело обзора предикат
 12           v_current_active_period vcap,
 13           term t
 14     WHERE vcap.period_type_code = t.code
 15       AND t.dictionary_id = 40
 16       AND t.code IN (3, 12, 5)
 17  --     and trunc(sysdate - 3) - trunc(first_off.day_off) in (17, 21)      -- отсюда
 18       and first_off.datasource_id = vcap.datasource_id
 19       and first_off.day_off = vcap.from_time)
 20  select * from SHOP_OFF
 21  /

82 rows selected.

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                             |   274 |   543K|   204   (3)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION        |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D71E8_B49AC5D6 |       |       |            |          |
|*  3 |    FILTER                         |                             |       |       |            |          | -- ***
|   4 |     HASH GROUP BY                 |                             |   274 | 25756 |   201   (3)| 00:00:01 |
|*  5 |      HASH JOIN                    |                             | 13739 |  1261K|   199   (2)| 00:00:01 |
|*  6 |       HASH JOIN                   |                             |  5623 |   444K|   101   (2)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| TERM                        |     1 |    59 |     2   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | DICTIONARY_CODE             |     1 |       |     1   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL          | OPEN_CUTOFF                 | 99907 |  2146K|    98   (2)| 00:00:01 |
|  10 |       TABLE ACCESS FULL           | OPEN_CUTOFF                 | 99907 |  1268K|    98   (2)| 00:00:01 |
|  11 |   VIEW                            |                             |   274 |   543K|     3   (0)| 00:00:01 |
|  12 |    TABLE ACCESS FULL              | SYS_TEMP_0FD9D71E8_B49AC5D6 |   274 | 20550 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   3 - filter((TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=17 OR
              TRUNC(SYSDATE@!-3)-TRUNC(MIN("FROM_TIME"))=21) AND "FROM_TIME"=MIN("FROM_TIME"))
...

— Oracle аналогично с нематериализованным случаем перемещает предикат / операцию FILTER на последний шаг #3 выполнения, что заметно влияет на оценку cardinality всего подзапроса и подтверждает предположение об отрицательном влиянии НЕприменения Complex View Merging при материализации подзапроса на оценку cardinality и, таким образом, на производительность всего БОЛЬШОГО ЗАПРОСА

3 комментария »

  1. Игорь, а я в таких случаях пару раз добавлял dynamic_sampling для материализованного блока — вроде помогало хорошо

    комментарий от Sayan Malakshinov — 24.11.2012 @ 11:13 | Ответить

    • это была бы хорошая дополнительная возможность исправления неточностей и ограничений оптимизатора, Саян
      но, насколько я понимаю dynamic sampling не применяется к временным (материализуемым) таблицам типа SYS_TEMP_%

      во всяком случае, добавление в запрос подсказки:

      WITH SHOP_OFF AS
       (SELECT /*+ materialize */
       ...
      select /*+ dynamic_sampling(10) */ * from SHOP_OFF
      /
      ...
      Note
      -----
         - dynamic sampling used for this statement (level=10)

      не стимулирует dynamic sampling в отношении SYS_TEMP_% и, как следствие, не меняет план

      комментарий от Igor Usoltsev — 24.11.2012 @ 19:32 | Ответить

      • Да, прошу прощения, действительно для кардинальности таких таблиц берутся в расчет значения, полученные при построении плана самого subquery factoring блока, а в моих случаях это помогало, т.к. ошибки кардинальности были именно в расчетах по таблицам этого блока. Вероятно, это сделано для снижения оверхеда, хотя действительно было бы неплохо иметь возможность уточнить кардинальность, учитывая количество вставленных строк при ее заполнении. Но только заполняется она после парса, хотя сама таблица создается до.

        комментарий от Sayan Malakshinov — 25.11.2012 @ 03:29 | Ответить


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