Oracle mechanics

20.03.2011

Проблема индексного доступа при наличии unusable index subpartition в версиях до 11g

Filed under: CBO,Oracle,parameters — Игорь Усольцев @ 18:31

В Oracle 10.2.0.4 замечено интересное поведение оптимизатора: при отключении (unusable) локальных индексов одной subpartition

SQL> alter table big_table modify subpartition part_1_subpart_2 unusable local indexes;
Table altered.

план выполнения запроса, работающего с совершенно другой subpartition, который прежде успешно использовавшего индексный доступ (INDEX RANGE SCAN), неожиданно начинал делать полное сканирование subpartition (TABLE ACCESS FULL) с соответствующим увеличением кол-ва обрабатываемых блоков (consistent gets)

SELECT ...
FROM big_table s
WHERE s.partition_id = 0  and s.subpartition_id = 7
and s.item_id in (...

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |     1 |   112 |    99   (2)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE SINGLE|                    |     1 |   112 |    99   (2)| 00:00:02 |     1 |     1 |
|   2 |   PARTITION LIST SINGLE|                    |     1 |   112 |    99   (2)| 00:00:02 |     8 |     8 |
|*  3 |    TABLE ACCESS FULL   | BIG_TABLE          |     1 |   112 |    99   (2)| 00:00:02 |     8 |     8 |
-------------------------------------------------------------------------------------------------------------
...
Statistics
----------------------------------------------------------
...
3484  consistent gets
...

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

Если  одна из партиций [или subpartition индекса] помечена непригодной к употреблению (marked UNUSABLE), прочие партиции индекса по-прежнему остаются действующими (valid)

В трейсе 10053 можно найти основание для неиспользования индекса — весь индекс рассматривается (по непонятной причине) как непригодный к использованию (UNUSABLE)

BASE STATISTICAL INFORMATION
***********************
Table Stats::
 Table: BIG_TABLE  Alias:  S  (Using composite stats)
 #Rows: 227727  #Blks:  29474  AvgRowLen:  1307.00
Index Stats::
 Index: SUBPART_IDX  Col#: 1 2 7
 USING COMPOSITE STATS
 LVLS: 2  #LB: 2612  #DK: 225730  LB/K: 1.00  DB/K: 1.00  CLUF: 225720.00
 UNUSABLE

В то же время при явном указании индекса в подсказке /*+ INDEX(table_name index_name)*/ оказывается, что Oracle может и использует (судя по статистике consistent gets) валидные партиции индекса — в полном соответствии с документацией:

SQL> SELECT /*+ index(s subpart_idx)*/
2 ...
3     FROM big_table s
/
...
Execution Plan
----------------------------------------------------------
Plan hash value: 3184409394
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |   112 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                        |     1 |   112 |     1   (0)| 00:00:01 |     1 |     1 |
|   2 |   PARTITION LIST SINGLE             |                        |     1 |   112 |     1   (0)| 00:00:01 |     8 |     8 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE              |     1 |   112 |     1   (0)| 00:00:01 |     8 |     8 |
|*  4 |     INDEX RANGE SCAN                | SUBPART_IDX            |     1 |       |     1   (0)| 00:00:01 |     8 |     8 |
------------------------------------------------------------------------------------------------------------------------------
...
Statistics
----------------------------------------------------------
...
8  consistent gets
...

При этом трейс 10053 меняет описание ситуации: вместо простой констатации факта UNUSABLE, появляется в высшей степени забавная фраза User hint to use this index

Index: SUBPART_IDX  Col#: 1 2 7
USING COMPOSITE STATS
LVLS: 2  #LB: 2598  #DK: 225730  LB/K: 1.00  DB/K: 1.00  CLUF: 225720.00
User hint to use this index

т.е. оптимизатор «проводит гибкую политику»: пользователь попросил — будем использовать индекс, не указал — считаем весь индекс негодным ;)

Понятно, что какую-то роль в таком поведении может играть понятие unusable index, и единственный лингвистически подходящий параметр, принимаемый в расчёт оптимизатором — skip_unusable_indexes — установленный в значение по умолчанию TRUE, которое «… позволяет выполнять все операции (inserts, deletes, updates, and selects) на таблицах с непригодными к использованию (unusable) индексами и индексными партициями«:

SQL> show parameter unusable
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE

При изменении параметра план выполнения начинает использовать ожидаемый индексный доступ без всяких подсказок

SQL> alter session set skip_unusable_indexes=false;
Session altered.

SQL> SELECT
...

Execution Plan
----------------------------------------------------------
Plan hash value: 3184409394
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |   113 |     1   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                        |     1 |   113 |     1   (0)| 00:00:01 |     1 |     1 |
|   2 |   PARTITION LIST SINGLE             |                        |     1 |   113 |     1   (0)| 00:00:01 |     8 |     8 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE              |     1 |   113 |     1   (0)| 00:00:01 |     8 |     8 |
|*  4 |     INDEX RANGE SCAN                | SUBPART_IDX            |     1 |       |     1   (0)| 00:00:01 |     8 |     8 |
------------------------------------------------------------------------------------------------------------------------------

и в трейсе пропадают замечания про UNUSABLE индексы

Причинно-следственная связь есть и теперь можно легко найти описание соответствующего бага на Metalink: Bug 4389673: SKIP_UNUSABLE_INDEXES DOES NOT TAKE EFFECT FOR UNUSABLE SUBPARTITIONS, со следующей диагностикой:

DIAGNOSTIC ANALYSIS:
--------------------
When skip_unusable_index is true,

Case 1:
------
if  all subpartitions of a partition are made unusable,the select query goes for the index

Case 2:
------
If the entire index is made unusable or if only a subpartition of a partition is unusable the query does not go for unusable index

— как раз рассматриваемый случай. Баг зафиксирован в 9.2.0.6, а исправлен — в 11.0, хотя в описаниях Patch Set 9.2.0.8 и 10.2.0.2 можно также найти ссылки на исправление — информация противоречива :(

На практике проблема (Bug 4389673) наблюдается в версииях 10.2.0.4, 11.1.0.7 и не наблюдается в 11.2

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

  1. […] локальными индексами, т.е. таким способом решали проблему индексного доступа при наличии unusable index subpartiti… […]

    Уведомление от ORA-00600: [kglUnKeepHandle] при превышении лимита количества дочерних курсоров « Oracle mechanics — 11.11.2012 @ 22: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 такие блоггеры, как: