Oracle mechanics

25.06.2014

Особенности преобразования Table Expansion

Filed under: bugs,Oracle,Partitioning — Игорь Усольцев @ 00:03
Tags: ,

Полезное стоимостное (CBQT) преобразование Table Expansion (TE), доступное с версии 11.2, кроме несомненных достоинств имеет ряд недоработок-багов особенностей реализации и применения, периодически омрачающих радость столкновений с лучшим из стоимостных оптимизаторов (CBO)

Далее — краткий обзор случаев неоднозначного поведения TE, доступных в версиях 11.2 — 12.1.0.1

И вначале — о хорошем: для случая простого Range Partitioning технология успешно отрабатывает независимо от количества и порядка расположения Unusable Index Partitions:

12.1.0.1.ORCL1201@SCOTT SQL> create table t (x int, y int)
  2   partition by range (y) (partition p_1 values less than (2), -- возьмём, например, 5 партиций
  3                           partition p_2 values less than (3),
  4                           partition p_3 values less than (4),
  5                           partition p_4 values less than (5),
  6                           partition p_5 values less than (6))
  7  /

Table created.

SQL> create index t_i on t(x) local
  2  /

Index created.

SQL> insert into t select level, mod(level,5) + 1
  2  from dual connect by level <= 100000
  3  /

100000 rows created.

SQL> alter index t_i MODIFY partition p_2 unusable
  2  /

Index altered.

SQL> alter index t_i MODIFY partition p_4 unusable
  2  /

Index altered.

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL> select partition_name, high_value, status from dba_ind_partitions where index_name = 'T_I'
  2  /

PARTITION_NAME  HIGH_VALUE  STATUS
--------------- ----------- --------
P_5             6           USABLE    -- вот с таким распеределением партиций индекса
P_4             5           UNUSABLE
P_3             4           USABLE
P_2             3           UNUSABLE
P_1             2           USABLE

SQL> select * from t where x = 10
  2  /

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |     1 |     8 |    28   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |    28   (0)| 00:00:01 |       |       | -- TE отлично отрабатывает,
|   2 |   UNION-ALL                                  |         |       |       |            |          |       |       |
|   3 |    PARTITION RANGE OR                        |         |     1 |     8 |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T       |     1 |     8 |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  5 |      INDEX RANGE SCAN                        | T_I     |     1 |       |     1   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|   6 |    PARTITION RANGE OR                        |         |     1 |     8 |    27   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  7 |     TABLE ACCESS FULL                        | T       |     1 |     8 |    27   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
------------------------------------------------------------------------------------------------------------------------

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

   4 - filter("T"."Y">=3 AND "T"."Y"<4 OR "T"."Y"<2 OR "T"."Y">=5 AND "T"."Y"<6)
   5 - access("X"=10)
   7 - filter("X"=10 AND ("T"."Y"<3 AND "T"."Y">=2 OR "T"."Y">=4 AND "T"."Y"<5))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        104  consistent gets                                                                                             -- вдвое уменьшая кол-во чтений

Однако в случае применения крайне удобного Interval Partitioning, технология с момента появления не работает независимо от кол-ва партиций:

SQL> create table t (x int, y int)
  2   partition by range (y)
  3   INTERVAL  (1)
  4                          (partition p_1 values less than (2),
  5                           partition p_2 values less than (3))
  6  /
SQL> create index t_i on t(x) local
  2  /
SQL> insert into t select level, mod(level,2) + 1
  2  from dual connect by level <= 100000
  3  /
SQL> exec dbms_stats.gather_table_stats('','T')

SQL> alter index t_i MODIFY partition p_2 unusable
  2  /

Index altered.

SQL> select partition_name, high_value, status from dba_ind_partitions where index_name = 'T_I'
  2  /

PARTITION_NAME  HIGH_VALUE  STATUS
--------------- ----------- --------
P_1             2           USABLE
P_2             3           UNUSABLE

SQL> select * from t where x = 10
  2  /

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     8 |    62   (2)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |     8 |    62   (2)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T    |     1 |     8 |    62   (2)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------

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

   2 - filter("X"=10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        225  consistent gets -- и кол-во читаемых блоков увеличилось вдвое

— что задокументировано в виде Bug 14558315 (TABLE EXPANSION DOES NOT WORK WITH INTERVAL PARTITIONING) на 12.1.0.1 с обещанием пофиксить в 12.2

List Partitioning на 2-х партициях:

SQL> create table t (x int, y int)
  2   partition by list (y) (partition p_1 values(1),
  3                          partition p_2 values(2))
  4  /
SQL> create index t_i on t(x) local
  2  /
SQL> insert into t select level, mod(level,2) + 1
  2  from dual connect by level <= 100000
  3  /
SQL> exec dbms_stats.gather_table_stats('','T')
SQL> alter index t_i MODIFY partition p_1 unusable
  2  /

Index altered.

SQL> select * from t where x = 10
  2  /

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |     1 |     8 |    34   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |    34   (0)| 00:00:01 |       |       | -- *
|   2 |   UNION-ALL                                  |         |       |       |            |          |       |       |
|   3 |    PARTITION LIST SINGLE                     |         |     1 |     8 |     2   (0)| 00:00:01 |     2 |     2 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T       |     1 |     8 |     2   (0)| 00:00:01 |     2 |     2 |
|*  5 |      INDEX RANGE SCAN                        | T_I     |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
|   6 |    PARTITION LIST SINGLE                     |         |     1 |     8 |    32   (0)| 00:00:01 |     1 |     1 |
|*  7 |     TABLE ACCESS FULL                        | T       |     1 |     8 |    32   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------

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

   5 - access("X"=10)
   7 - filter("X"=10)


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

*) — TE применяется и штатно работает, но если увеличить число партиций хотя бы до 3-х:

SQL> create table t (x int, y int)
  2   partition by list (y) (partition p_1 values(1),
  3                          partition p_2 values(2),
  4                          partition p_3 values(3))
  5  /
SQL> create index t_i on t(x) local
  2  /
SQL> insert into t select level, mod(level,3) + 1
  2  from dual connect by level <= 100000
  3  /
SQL> exec dbms_stats.gather_table_stats('','T')
SQL> alter index t_i MODIFY partition p_2 unusable -- и сделать невалидной партицию в середине
  2  /

Index altered.

SQL> select partition_name, high_value, status from dba_ind_partitions where index_name = 'T_I'
  2  /

PARTITION_NAME   HIGH_VALUE  STATUS
---------------- ----------- --------
P_1              1           USABLE
P_2              2           UNUSABLE
P_3              3           USABLE

SQL> select * from t where x = 10
  2  /

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |    92   (2)| 00:00:01 |       |       | -- **
|   1 |  PARTITION LIST ALL|      |     1 |     8 |    92   (2)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     8 |    92   (2)| 00:00:01 |     1 |     3 |
-------------------------------------------------------------------------------------------

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

   2 - filter("X"=10)

**) — никакого Table Expansion уже не происходит!
А при попытке форсировать TE соответствующей подсказкой:

SQL> select /*+ EXPAND_TABLE(t)*/ * from t where x = 10
  2  /

---------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |     2 |    52 |    94   (2)| 00:00:01 |       |       |
|   1 |  VIEW                   | VW_TE_1 |     2 |    52 |    94   (2)| 00:00:01 |       |       | -- ***
|   2 |   UNION-ALL             |         |       |       |            |          |       |       |
|   3 |    PARTITION LIST INLIST|         |     1 |     8 |    61   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  4 |     TABLE ACCESS FULL   | T       |     1 |     8 |    61   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   5 |    PARTITION LIST SINGLE|         |     1 |     8 |    32   (0)| 00:00:01 |     2 |     2 |
|*  6 |     TABLE ACCESS FULL   | T       |     1 |     8 |    32   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------

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

   4 - filter("X"=10)
   6 - filter("X"=10)

— порождается достаточно странный план с применением TE в виде раздельных FULL SCAN-ов по разным наборам партиций и стоимостью, немного превышающей предыдущий план с PARTITION LIST ALL (**) за счёт незначительных «накладных» расходов

Тот же странный план можно получить при попытке искусственной имитации (через внешний UNION ALL) преобразования Table Expansion:

SQL> select * from t partition (p_2) where x = 10
  2  union all
  3  select * from t where x = 10 and y in (1,3)
  4  /

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     2 |    16 |    94  (66)| 00:00:01 |       |       |
|   1 |  UNION-ALL             |      |       |       |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     1 |     8 |    32   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL   | T    |     1 |     8 |    32   (0)| 00:00:01 |     2 |     2 |
|   4 |   PARTITION LIST INLIST|      |     1 |     8 |    61   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  5 |    TABLE ACCESS FULL   | T    |     1 |     8 |    61   (0)| 00:00:01 |KEY(I) |KEY(I) |
-----------------------------------------------------------------------------------------------

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

   3 - filter("X"=10)
   5 - filter("X"=10)

Более того, даже простой запрос по партициям с исключительно валидными локальными индексами выбирает дорогой TABLE ACCESS FULL:

SQL> select * from t where x = 10 and y in (1,3)
  2  /

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     8 |    63   (2)| 00:00:01 |       |       |
|   1 |  PARTITION LIST INLIST|      |     1 |     8 |    63   (2)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL   | T    |     1 |     8 |    63   (2)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

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

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T  (Using composite stats)                                      -- композитная статистика для таблицы
  (making adjustments for partition skews)
  ORIGINAL VALUES::    #Rows: 100000  #Blks:  332  AvgRowLen:  8.00  ChainCnt:  0.00 -- на основе сумм.данных из DBA_TAB_PARTITIONS
  PARTITIONS::
  PRUNED: 2
  ANALYZED: 2  UNANALYZED: 0
    #Rows: 100000  #Blks:  222  AvgRowLen:  8.00  ChainCnt:  0.00                    -- правильно вычисляет #Blks, и неправильно #Rows, что некритично для этого запроса
Index Stats::
  Index: T_I  Col#: 1
    USING COMPOSITE STATS                                                            -- композитная статистика для индекса в принципе не допускает индексного доступа
    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00                      -- и выставляет нулевые значения (видимо, в предположении бессмысленности расчёта)
    UNUSABLE                                                                         -- как только встречает одну UNUSABLE LIST PARTITION
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH 
...
  Table: T  Alias: T
    Card: Original: 100000.000000  Rounded: 1  Computed: 0.67  Non Adjusted: 0.67
  Access Path: TableScan
    Cost:  62.79  Resp: 62.79  Degree: 0
      Cost_io: 62.00  Cost_cpu: 16291949
      Resp_io: 62.00  Resp_cpu: 16291949
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan                                                       -- что и предопределяет выбор [full] TableScan
         Cost: 62.79  Degree: 1  Resp: 62.79  Card: 0.67  Bytes: 0

***************************************

Добавляя к искусственной имитации Table Expansion прямое указание использовать индекс:

SQL> select * from t partition (p_2) where x = 10
  2  union all
  3  select /*+ INDEX(t)*/ * from t where x = 10 and y in (1,3)
  4  /

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |      |     2 |    16 |    33   (4)| 00:00:01 |    |          |
|   1 |  UNION-ALL                                  |      |       |       |            |          |    |          |
|   2 |   PARTITION LIST SINGLE                     |      |     1 |     8 |    32   (0)| 00:00:01 |  2 |        2 |
|*  3 |    TABLE ACCESS FULL                        | T    |     1 |     8 |    32   (0)| 00:00:01 |  2 |        2 |
|   4 |   PARTITION LIST INLIST                     |      |     1 |     8 |     1   (0)| 00:00:01 |KEY(I) |KEY(I) | -- (****) Cost=1 для 2-х партиций
|   5 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T    |     1 |     8 |     1   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  6 |     INDEX RANGE SCAN                        | T_I  |     1 |       |     1   (0)| 00:00:01 |KEY(I) |KEY(I) |
--------------------------------------------------------------------------------------------------------------------

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

   3 - filter("X"=10)
   6 - access("X"=10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        117  consistent gets

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

Index Stats::
  Index: T_I  Col#: 1
    USING COMPOSITE STATS
    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00 -- here be dragonz
    User hint to use this index
...
  Access Path: index (AllEqRange)
    Index: T_I
    resc_io: 1.00  resc_cpu: 200
    ix_sel: 0.000010  ix_sel_with_filters: 0.000010 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: T_I
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 0.67  Bytes: 0

— и индекс используется лишь «по многочисленным просьбам публики» с очевидно заниженной стоимостью (****), что можно видеть из сравнения со стоимостью запроса по одной валидной партиции:

SQL> select * from t where x = 10 and y = 1
  2  /

no rows selected

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |     1 |     8 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION LIST SINGLE             |      |     1 |     8 |     2   (0)| 00:00:01 |     1 |  1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T    |     1 |     8 |     2   (0)| 00:00:01 |     1 |  1 |    -- Cost=2 для одной партиции
|*  3 |    INDEX RANGE SCAN                | T_I  |     1 |       |     1   (0)| 00:00:01 |     1 |  1 |
-----------------------------------------------------------------------------------------------------------

В завершении нужно упомянуть забавную ошибку ORA-01795: maximum number of expressions in a list is 1000, которую можно получить при попытках использовать технологию TE на большом кол-ве партиций (>1001) — обсудили/расписали с Дмитрием Никифоровым в его блоге

При этом, из-за старого ограничения размера SQL-конструкции IN (LIST) для таблиц с большим количеством партиций та же ошибка наблюдается не только в процессе подготовки плана выполнения, но и при выполнении процедур пакета DBMS_STATS

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

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

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