Oracle mechanics

31.03.2012

«Бага в Оракле»: JOIN ELIMINATION

Filed under: commonplace,Oracle — Игорь Усольцев @ 09:28

Полностью вопрос звучал так:

Почему запрос выдаёт пустым столбец ENTITY_ID_NULL, который по условию соединения должен быть в точности равен ENTITY_ID — бага в Оракле?
И отчего в плане выполнения не упоминается таблица ENTITY?

11.1.0.7.@SQL> select
2         e.entity_id     as entity_id_null,
3         rid.entity_id,
4         rid.attr_as_str as local_region_id
5    from entity e
6   inner join entity_attr rid
7      on e.entity_id = rid.entity_id
8     and rid.attr_name = 'local-region-id'
9    left join entity_attr isdel
10      on e.entity_id = isdel.entity_id
11     and isdel.attr_name = 'is-del'
12   where nvl(isdel.attr_as_str, '0') = '0'
13     and e.entity_id in
14         (select to_number(ea2.attr_as_str)
15            from fv_entity e
16           inner join fv_entity_attr ea2
17              on e.entity_id = ea2.entity_id
18             and ea2.attr_name = 'company-id'
19           inner join fv_entity_attr ea3
20              on e.entity_id = ea3.entity_id
21             and ea3.attr_name = 'supplier-id'
22             and ea3.attr_as_str in ('1902056404'))
23  /

ENTITY_ID_NULL  ENTITY_ID LOCAL_REGION_ID
-------------- ---------- ----------------
                  3423213 47
               5516812018 213
                  2742086 51
...

Коротко — бага одного из элементов технологии трансформации Join Elimination
В плане запроса не используются ни таблицы ENTITY и FV_ENTITY, ни один из индексов по этим таблицам, и это правильно:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                     |    28 | 40376 |    27   (4)|
|   1 |  VIEW                                    | VM_NWVW_2           |    28 | 40376 |    27   (4)| -- внутренний вспомогательный обзор
|   2 |   HASH UNIQUE                            |                     |    28 |  3892 |    27   (4)|
|*  3 |    FILTER                                |                     |       |       |            |
|   4 |     NESTED LOOPS OUTER                   |                     |    28 |  3892 |    26   (0)|
|   5 |      NESTED LOOPS                        |                     |    61 |  6832 |    20   (0)|
|   6 |       NESTED LOOPS                       |                     |    52 |  3068 |    15   (0)|
|   7 |        PARTITION LIST SINGLE             |                     |    51 |  1530 |    10   (0)|
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| FV_ENTITY_ATTR      |    51 |  1530 |    10   (0)|
|*  9 |          INDEX RANGE SCAN                | IDX_FVEA_PA001      |  2036 |       |     1   (0)| -- индекс FV_ENTITY_ATTR
|* 10 |        INDEX RANGE SCAN                  | IDX_FV_ENTITY_ATTR2 |     1 |    29 |     1   (0)| -- индекс FV_ENTITY_ATTR
|* 11 |       INDEX RANGE SCAN                   | IDX_ENTITY_ATTR2    |     1 |    53 |     1   (0)| -- индекс ENTITY_ATTR
|  12 |      PARTITION LIST SINGLE               |                     |     1 |    27 |     1   (0)|
|  13 |       TABLE ACCESS BY LOCAL INDEX ROWID  | ENTITY_ATTR         |     1 |    27 |     1   (0)|
|* 14 |        INDEX RANGE SCAN                  | IDX_EA_PA002        |     1 |       |     1   (0)| -- индекс ENTITY_ATTR
-----------------------------------------------------------------------------------------------------

Учитывая наличие ограничений Foreign Key для столбцов ENTITY_ATTR.ENTITY_ID -> ENTITY.ENTITY_ID и FV_ENTITY_ATTR.ENTITY_ID -> FV_ENTITY.ENTITY_ID:

TABLE_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE COLUMN_NAME REF_TABLE_NAME REF_CONSTRAINT_NAME REF_CONSTRAINT_TYPE REF_COLUMN_NAME
-------------- --------------- --------------- ----------- -------------- ------------------- ------------------- ---------------
ENTITY_ATTR    FK_EAP_E_01     R               ENTITY_ID   ENTITY         SYS_C004278         P                   ENTITY_ID
FV_ENTITY_ATTR FK_FV_EA_E_01   R               ENTITY_ID   FV_ENTITY      SYS_C0028857        P                   ENTITY_ID

исключение таблиц ENTITY и FV_ENTITY из плана запроса вполне логично и отражено в трейсе оптимизатора:

JE:   eliminate table: FV_ENTITY
 JE:   Replaced column: FV_ENTITY.ENTITY_ID with column: FV_ENTITY_ATTR.ENTITY_ID
 Registered qb: SEL$3838834B 0xafa12a48 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "E"@"SEL$1")
 ...
 JE:   eliminate table: ENTITY
 JE:   Replaced column: ENTITY.ENTITY_ID with column: ENTITY_ATTR.ENTITY_ID
 Registered qb: SEL$A280CC65 0xafa052d0 (JOIN REMOVED FROM QUERY BLOCK SEL$3; SEL$3; "E"@"SEL$3")

В результате транформаций у оптимизатора получается запрос, естественно не использующий таблицы ENTITY и FV_ENTITY:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VM_NWVW_2"."$vm_col_2" "ENTITY_ID_NULL",
        "VM_NWVW_2"."$vm_col_3" "ENTITY_ID",
        "VM_NWVW_2"."$vm_col_4" "LOCAL_REGION_ID"
FROM (SELECT DISTINCT "RID".ROWID "ROWID",
                      "ISDEL".ROWID "ROWID",
                      TO_NUMBER("EA2"."ATTR_AS_STR") "$vm_col_1",
                      "RID"."ENTITY_ID" "$vm_col_2",             -- выбираются для ENTITY_ID_NULL
                      "RID"."ENTITY_ID" "$vm_col_3",             -- и тот же столбец для ENTITY_ID
                      "RID"."ATTR_AS_STR" "$vm_col_4"
      FROM ... ) "VM_NWVW_2"

— и этот окончательно-трансформированный запрос при выполнении возвращает безошибочный результат, что хорошо заметно из текста. Исключение таблиц из плана выполнения фиксируется в секции Outline соответствующими подсказками:

/*+
BEGIN_OUTLINE_DATA
...
ELIMINATE_JOIN(@"SEL$1" "E"@"SEL$1")
...
ELIMINATE_JOIN(@"SEL$3" "E"@"SEL$3")
...
END_OUTLINE_DATA
*/

Т.о. судя по трейсу, операция JOIN ELIMINATION в процессе простых (не Cost-Based) преобразований запроса выполняется корректно, ошибка появляется впоследствие, на этапах компиляции-выполнения запроса, имхо

Ну и поскольку понятно, какого типа Join Elimination может приводить к ошибке, можно найти соответствующий fix оптимизатора, включающий эту полезную фичу при optimizer_features_enable = 11.1.0.7Bug 6167716 — Improve ability to eliminate tables from query [ID 6167716.8]

With this fix CBO checks if it is possible to eliminate a table from a query by replacing primary key column references with foriegn key references

+ многочисленные связанные с этим фиксом баги, типа: Bug 9011590: JOIN ELIMINATION CAUSING INCORRECT RESULTS,…

На уровне запроса с помощью подсказки /*+ OPT_PARAM(‘_fix_control’ ‘6167716:0’)*/ этот фикс в версии 11.1.0.7 не отключается, но можно успешно использовать подсказки (в порядке уменьшения масштаба влияния):

  • opt_param(‘optimizer_features_enable’ ‘11.1.0.6’)
  • opt_param(‘_optimizer_join_elimination_enabled’ ‘false’)
  • NO_ELIMINATE_JOIN(@»SEL$3″ «E»@»SEL$3») — обратную подсказке из Outline
  • или просто NO_ELIMINATE_JOIN(e)

Отключение фикса на уровне сессии, естественно, работает отлично:

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

BUGNO   VALUE SQL_FEATURE              DESCRIPTION                                             OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
------- ----- ------------------------ ------------------------------------------------------- ------------------------- ----- ----------
6167716     1 QKSFM_TABLE_ELIM_6167716 replace primary key references during table elimination 11.1.0.7                      0          1

SQL> alter session set "_fix_control"="6167716:OFF";

Session altered.

SQL> select
2      e.entity_id     as entity_id_null,
3         rid.entity_id,
4         rid.attr_as_str as local_region_id
...
23  /

ENTITY_ID_NULL  ENTITY_ID LOCAL_REGION_ID
-------------- ---------- ---------------
       3423213    3423213 47
    5516812018 5516812018 213
       2742086    2742086 51
...

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                     |    28 | 40684 |    25   (4)|
|   1 |  VIEW                                      | VM_NWVW_2           |    28 | 40684 |    25   (4)|
|   2 |   HASH UNIQUE                              |                     |    28 |  4144 |    25   (4)|
|   3 |    NESTED LOOPS                            |                     |    28 |  4144 |    24   (0)|
|*  4 |     FILTER                                 |                     |       |       |            |
|   5 |      NESTED LOOPS OUTER                    |                     |    24 |  2448 |    22   (0)|
|   6 |       NESTED LOOPS                         |                     |    51 |  3825 |    17   (0)|
|   7 |        NESTED LOOPS                        |                     |    51 |  3366 |    16   (0)|
|   8 |         NESTED LOOPS                       |                     |    51 |  1887 |    11   (0)|
|   9 |          PARTITION LIST SINGLE             |                     |    51 |  1530 |    10   (0)|
|* 10 |           TABLE ACCESS BY LOCAL INDEX ROWID| FV_ENTITY_ATTR      |    51 |  1530 |    10   (0)|
|* 11 |            INDEX RANGE SCAN                | IDX_FVEA_PA001      |  2036 |       |     1   (0)|
|* 12 |          INDEX UNIQUE SCAN                 | SYS_C0028857        |     1 |     7 |     1   (0)|-- PK индекс FV_ENTITY
|* 13 |         INDEX RANGE SCAN                   | IDX_FV_ENTITY_ATTR2 |     1 |    29 |     1   (0)|
|* 14 |        INDEX UNIQUE SCAN                   | SYS_C004278         |     1 |     9 |     1   (0)|-- PK индекс ENTITY
|  15 |       PARTITION LIST SINGLE                |                     |     1 |    27 |     1   (0)|
|  16 |        TABLE ACCESS BY LOCAL INDEX ROWID   | ENTITY_ATTR         |     1 |    27 |     1   (0)|
|* 17 |         INDEX RANGE SCAN                   | IDX_EA_PA002        |     1 |       |     1   (0)|
|* 18 |     INDEX RANGE SCAN                       | IDX_ENTITY_ATTR2    |     1 |    46 |     1   (0)|
-------------------------------------------------------------------------------------------------------

— план с избыточными операциями доступа к индексам «ненужных» таблиц и стоимостью меньшей, чем план после Join Elimination, что неявно ещё раз подтверждает, что эта трансформация не имеет приоритетом уменьшение стоимости — не Cost-Based

Однако на этапе разработки лучшим workaround’ом будет простое исключение «ненужных» таблиц из запроса, естественно основанное на «глубоком знании схемы данных»)

SQL> select rid.entity_id   as entity_id_null,
 2         rid.entity_id,
 3         rid.attr_as_str as local_region_id
 4    from entity_attr rid
 5    left join entity_attr isdel
 6      on rid.entity_id = isdel.entity_id
 7     and isdel.attr_name = 'is-del'
 8   where nvl(isdel.attr_as_str, '0') = '0'
 9     and rid.entity_id in
 10         (select to_number(ea2.attr_as_str)
 11            from fv_entity_attr ea2
 12           inner join fv_entity_attr ea3
 13              on ea2.entity_id = ea3.entity_id
 14             and ea3.attr_name = 'supplier-id'
 15             and ea3.attr_as_str in ('1902056404')
 16           where ea2.attr_name = 'company-id')
 17     and rid.attr_name = 'local-region-id'
 18  /

ENTITY_ID_NULL  ENTITY_ID LOCAL_REGION_ID
-------------- ---------- ---------------
       3423213    3423213 47
    5516812018 5516812018 213
       2742086    2742086 51
...

-----------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                     |    28 | 40320 |    27   (4)|
|   1 |  VIEW                                    | VM_NWVW_2           |    28 | 40320 |    27   (4)|
|   2 |   HASH UNIQUE                            |                     |    28 |  3696 |    27   (4)|
|*  3 |    FILTER                                |                     |       |       |            |
|   4 |     NESTED LOOPS OUTER                   |                     |    28 |  3696 |    26   (0)|
|   5 |      NESTED LOOPS                        |                     |    61 |  6405 |    20   (0)|
|   6 |       NESTED LOOPS                       |                     |    52 |  3068 |    15   (0)|
|   7 |        PARTITION LIST SINGLE             |                     |    51 |  1530 |    10   (0)|
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| FV_ENTITY_ATTR      |    51 |  1530 |    10   (0)|
|*  9 |          INDEX RANGE SCAN                | IDX_FVEA_PA001      |  2036 |       |     1   (0)|
|* 10 |        INDEX RANGE SCAN                  | IDX_FV_ENTITY_ATTR2 |     1 |    29 |     1   (0)|
|* 11 |       INDEX RANGE SCAN                   | IDX_ENTITY_ATTR2    |     1 |    46 |     1   (0)|
|  12 |      PARTITION LIST SINGLE               |                     |     1 |    27 |     1   (0)|
|  13 |       TABLE ACCESS BY LOCAL INDEX ROWID  | ENTITY_ATTR         |     1 |    27 |     1   (0)|
|* 14 |        INDEX RANGE SCAN                  | IDX_EA_PA002        |     1 |       |     1   (0)|
-----------------------------------------------------------------------------------------------------

— запрос выполняется без ошибок, план возвращается к исходному

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

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

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