Oracle mechanics

22.09.2013

Уникальные индексы для Join Predicate Push-Down

Filed under: CBO,heuristics,hints,Oracle — Игорь Усольцев @ 00:26
Tags: , ,

Обратил внимание на важность наличия / использования уникальных индексов во внешнем блоке запроса для применения JPPD с обзорами (Inline View) типа UNION ALL в случаях когда не для всех соединяемых в обзоре таблиц возможен индексный доступ

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

Итак, тестовая схема:

11.2.0.3.@ SQL> create table t1(l1, l2 not null, char_column)
     as select level l1,
               level l2,
               lpad('x',100,level) char_column
          from dual connect by level <= 1000
  2  /

Table created.

SQL> create index t1_l2_idx on t1(l2) -- столбец L2 специально создан уникальным, но индекс пока создаю обычный
  2  /

Index created.

SQL> create table t21 as select level l1, lpad('x',100,level) char_column from dual connect by level <= 1000
  2  /

Table created.

SQL> create index t21_l1_idx on t21(l1)
  2  /

Index created.

SQL> create table t22 as select level l1, lpad('x',100,level) char_column from dual connect by level <= 1000
  2  /

Table created.

SQL> create index t22_l1_idx on t22(l1)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats('','T1')
SQL> exec dbms_stats.gather_table_stats('','T21')
SQL> exec dbms_stats.gather_table_stats('','T22')

PL/SQL procedure successfully completed.

Тестовый запрос:

SQL> select t2.char_column
  2    from t1,
  3         (select *                                                                         -- UNION ALL inline view
  4            from t21
  5          union all
  6          select * from t22) t2
  7   where t1.l1 = t2.l1
  8     and t1.l2 = 555
  9  /

2 rows selected.

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |       |       |     6 (100)|          |
|   1 |  NESTED LOOPS                  |            |     2 |   428 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T1         |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | T1_L2_IDX  |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                         |            |     1 |   206 |     4   (0)| 00:00:01 |
|   5 |    UNION ALL PUSHED PREDICATE  |            |       |       |            |          | -- , в который замечательно пропихивается (PUSH)
|   6 |     TABLE ACCESS BY INDEX ROWID| T21        |     1 |   105 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T22        |     1 |   105 |     2   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T22_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("T1"."L2"=555)
   7 - access("T21"."L1"="T1"."L1")                                                           -- условие из внешнего блока (T1)
   9 - access("T22"."L1"="T1"."L1")                                                           -- --//--

— идеальная картина работы JPPD — только NESTED LOOPS и INDEX RANGE SCAN, оптимально низкая стоимость

Что произойдёт, если для одной из таблиц UNION ALL обзора индексный доступ станет по каким-то причинам невозможен — например, если обзор используется как раз для того, чтобы скрыть временную недоступность одной из таблиц или мат.вью на период перестроения?

SQL> alter index t22_l1_idx unusable
  2  /

Index altered.

SQL> select t2.char_column
  2    from t1,
  3         (select *
  4            from t21
  5          union all
  6          select * from t22) t2
  7   where t1.l1 = t2.l1
  8     and t1.l2 = 555
  9  /

2 rows selected.

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    17 (100)|          |
|*  1 |  HASH JOIN                   |           |     2 |   446 |    17   (6)| 00:00:01 | --***
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |     8 |     2   (0)| 00:00:01 | -- query block SEL$1
|*  3 |    INDEX RANGE SCAN          | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       |           |  2000 |   419K|    14   (0)| 00:00:01 |
|   5 |    UNION-ALL                 |           |       |       |            |          |
|   6 |     TABLE ACCESS FULL        | T21       |  1000 |   102K|     7   (0)| 00:00:01 | --***
|   7 |     TABLE ACCESS FULL        | T22       |  1000 |   102K|     7   (0)| 00:00:01 | --***
------------------------------------------------------------------------------------------

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

   1 - access("T1"."L1"="T2"."L1")
   3 - access("T1"."L2"=555)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         40  consistent gets

— увы, JPPD уже не используется — сплошной HASH JOIN и TABLE ACCESS FULL — несмотря на то, что индекс на таблице T21
по-прежнему находится в прекрасном состоянии и примение JPPD с индексным доступом только к этой таблице дало бы заметный выигрыш (как будет показано далее)

Трейс 10053 показывет, что уже после создания «нужного» плана с применением JPPD и стоимостью 11(почаемого ниже с помощью хинта PUSH_PRED), использование JPPD было отвергнуто по причине несоблюдения некого правила, относящегося к индексам:

JPPD: Index heuristic failed : Better Cost = 11.02
JPPD: Will not use JPPD from query block SEL$1 (#1)

Судя по статистике выполнения неудивительно, что Oracle выбрал для соединения более дорогой HASH JOIN против формально более дешёвого NESTED LOOPS, стимулированный подсказкой выбор которого не влияет на применение JPPD:

SQL> select /*+ USE_NL(t1 t2)*/ t2.char_column
  2    from t1,
  3         (select * from t21
  4          union all
  5          select * from t22) t2
  6   where t1.l1 = t2.l1
  7     and t1.l2 = 555
  8  /

2 rows selected.

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |   446 |    16   (0)| 00:00:01 | -- при меньшей формальной стоимости -
|   1 |  NESTED LOOPS                |           |     2 |   446 |    16   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_L2_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   VIEW                       |           |     2 |   430 |    14   (0)| 00:00:01 |
|   5 |    UNION-ALL                 |           |       |       |            |          |
|   6 |     TABLE ACCESS FULL        | T21       |  1000 |   102K|     7   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | T22       |  1000 |   102K|     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   3 - access("T1"."L2"=555)
   4 - filter("T1"."L1"="T2"."L1")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         41  consistent gets                                                               -- - худшая статистика

Использование соответствующей подсказки позволяет обойти вышеупомянутое правило / Index heuristic, использовать JPPD и получить действительно оптимальный план стоимостью 11 у.е.:

SQL> select /*+ PUSH_PRED(t2)*/ t2.char_column
  2    from t1,
  3         (select *
  4            from t21
  5          union all
  6          select * from t22) t2
  7   where t1.l1 = t2.l1
  8     and t1.l2 = 555
  9  /

2 rows selected.

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |       |       |    11 (100)|          |
|   1 |  NESTED LOOPS                  |            |     2 |   428 |    11   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T1         |     1 |     8 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN            | T1_L2_IDX  |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                         |            |     1 |   206 |     9   (0)| 00:00:01 |
|   5 |    UNION ALL PUSHED PREDICATE  |            |       |       |            |          | -- при использовании JPPD
|   6 |     TABLE ACCESS BY INDEX ROWID| T21        |     1 |   105 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL          | T22        |     1 |   105 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("T1"."L2"=555)
   7 - access("T21"."L1"="T1"."L1")
   8 - filter("T22"."L1"="T1"."L1")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         26  consistent gets                                                                 -- получили лучшую статистику выполнения

Влияние хинта отражено в трейсе и, очевидно, отменяет не только лишние поиски-перестановки, но и некоторые правила:

JPPD: All views have PUSH_PRED hint, terminating search.
JPPD: Will use JPPD from SEL$1 (#1) to SET$1 (#2).

Однако применение подсказки — не единственный вариант, при котором Oracle выбирает лучший план с JPPD:

SQL> drop index t1_l2_idx
  2  /

Index dropped.

SQL> create unique index t1_l2_idx on t1(l2) -- поскольку L2 уникален
  2  /

Index created.

SQL> select t2.char_column
  2    from t1,
  3         (select *
  4            from t21
  5          union all
  6          select * from t22) t2
  7   where t1.l1 = t2.l1
  8     and t1.l2 = 555
  9  /

2 rows selected.

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |       |       |    11 (100)|          |
|   1 |  NESTED LOOPS                  |            |     2 |   428 |    11   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T1         |     1 |     8 |     2   (0)| 00:00:01 | -- SEL$1 (#1)
|*  3 |    INDEX UNIQUE SCAN           | T1_L2_IDX  |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                         |            |     1 |   206 |     9   (0)| 00:00:01 |
|   5 |    UNION ALL PUSHED PREDICATE  |            |       |       |            |          | -- SET$1 (#2)
|   6 |     TABLE ACCESS BY INDEX ROWID| T21        |     1 |   105 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T21_L1_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL          | T22        |     1 |   105 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("T1"."L2"=555)
   7 - access("T21"."L1"="T1"."L1")
   8 - filter("T22"."L1"="T1"."L1")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets

— факт наличия уникального индекса на том же столбце (с неизменными содержимым!) удовлетворяет всем условиям оптимизатра и допускает корректное использование JPPD при частично-индексном доступе к таблицам UNION ALL inline view:

JPPD: Updated best state, Cost = 11.02
JPPD: Will use JPPD from SEL$1 (#1) to SET$1 (#2).

Т.о. упомянутое индесное правило (Index heuristic) можно сформулировать так:

При частичной [не]возможности индексного доступ к таблицам обзора типа UNION ALL, JPPD не применяется если во внешнем блоке запроса используется доступ по неуникальному индексу независимо от распределения значений столбца. При доступе по уникальному индексу JPPD может применяться в обычном порядке на основе стоимостного подхода

В версии 12.1.0.1 поведение оптимизатора в этом месте не меняется

1 комментарий »

  1. В дополнение, при попытке практического применения на версии 11.2.0.3 столкнулся с:

    SQL> SELECT--+ push_pred(H)
    ...
     19  /
       AND H.CLIENT_ID = BC.BRAND_CLIENT_ID
                         *
    ERROR at line 16:
    ORA-00600: internal error code, arguments: [15570], [], [], [], [], [], [], [], [], [], [], []

    — ошибка хорошо документирована — Bug 12876939 ORA-600 [15570] running a Nested Loop over parallel view,
    причиной, согласно описанию, является «параллельность обзора» (точнее составляющих объектов):

    SQL> select distinct degree from dba_tables where table_name like 'MV_DIBER_GET_%';
    
    DEGREE
    ------
         5

    В качестве workaround-а официально предлагается отключить JPPD параметром:

    SQL> @param_ _push_join_union_view
     
    NAME                    VALUE  DSC
    ----------------------- ------ -----------------------------------------------------
    _push_join_union_view   TRUE   enable pushing join predicate inside a union all view
    _push_join_union_view2  TRUE   enable pushing join predicate inside a union view

    , которых в 11.2 — два, для обоих вариантов UNION / UNION ALL

    В вышеописанном случае применение JPPD как раз и являлось целью оптимизации, поэтому в качестве альтернативного workaround-а можно на уровне запроса отключить параллельное выполнение:

    SQL> SELECT--+ push_pred(H) noparallel
    ...
     19  /
    
    Elapsed: 00:00:00.17
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3559745357
    
    ------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------------
    ...
    |  24 |    VIEW                              | MV_DIBER_GET              |     1 |    32 |  9596   (2)| 00:01:46 |
    |  25 |     UNION ALL PUSHED PREDICATE       |                           |       |       |         |             |
    |* 26 |      FILTER                          |                           |       |       |         |             |
    |* 27 |       MAT_VIEW ACCESS FULL           | MV_DIBER_GET_1            |     1 |    32 |  9587   (2)| 00:01:46 | -- на время перестроения не используется / недоступен
    |* 28 |      FILTER                          |                           |       |       |         |             |
    |* 29 |       MAT_VIEW ACCESS BY INDEX ROWID | MV_DIBER_GET_2            |     1 |    32 |     9   (0)| 00:00:01 |
    |* 30 |        INDEX RANGE SCAN              | IDX_DIBER_GET_2_CLIENT_ID |     6 |       |     3   (0)| 00:00:01 | -- запрос выполняется по этому пути
    ------------------------------------------------------------------------------------------------------------------

    — получаем искомое быстрое выполнение с JPPD доступом по индексу

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


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