Oracle mechanics

01.07.2011

Индекс в статусе unusable, подсказка INDEX и обновлённый RBO в Oracle 11.2

Filed under: CBO,hints,Oracle,RBO — Игорь Усольцев @ 01:04

in english

Oracle показывает интересное (imho) поведение во время выполнения запросов с подсказками к таблицами с имеющимися unusable, т.е. непригодными к использованию индексами

Пока в запросе не используются подсказки, можно наблюдать ожидаемый («разумный») выбор оптимизатора — использовать индексный доступ INDEX RANGE SCAN когда индекс доступен (статус VALID), и не использовать индекс в статусе UNUSABLE (при этом для доступа к данным используется TABLE FULL SCAN):

10.2.0.4.0#SQL> select count(rowid) from t where owner = 'OUTLN';
 ---------------------------------------------------------------------------
 | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
 |   1 |  SORT AGGREGATE   |       |     1 |    18 |            |          |
 |*  2 |   INDEX RANGE SCAN| T_IDX |    80 |  1440 |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------

10.2.0.4.0#SQL> alter index t_idx unusable;

Index altered.

10.2.0.4.0#SQL> select count(rowid) from t where owner = 'OUTLN';
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |       |       |    12 (100)|          |
 |   1 |  SORT AGGREGATE    |      |     1 |    18 |            |          |
 |*  2 |   TABLE ACCESS FULL| T    |    80 |  1440 |    12   (0)| 00:00:01 |
 ---------------------------------------------------------------------------

И в трейсе 10053 можно найти причины такого выбора:

BASE STATISTICAL INFORMATION
 ***********************
 ...
 Index Stats::
 Index: T_IDX  Col#: 1
 LVLS: 1  #LB: 4  #DK: 20  LB/K: 1.00  DB/K: 5.00  CLUF: 116.00
 UNUSABLE

— т.е. индекс UNUSABLE => значит индексный доступ (INDEX SCAN) невозможен. so far, so good

Если в запросе появляется подсказка INDEX (форсирующая использование unusable индекс) — получаем ошибку и в Oracle 10.2, и в версии 11.2:

10.2.0.4.0#SQL> select/*+ index(t t_idx) */ count(rowid) from t where owner = 'OUTLN';
select/*+ index(t t_idx) */ count(rowid) from t where owner = 'OUTLN'
*
ERROR at line 1:
ORA-01502: index 'SYSTEM.T_IDX' or partition of such index is in unusable state

несмотря на значение параметра skip_unusable_indexes = TRUE (используется по умолчанию в 10.2 и в 11.2), который согласно документации:

Отключает сообщения об ошибках о том, что индексы или партиции индексов находятся в статусе UNUSABLE. Этот параметр допускает выполнение любых операций (inserts, deletes, updates, и selects) с таблицами, у которых имеются индексы или партиции индексов в статусе UNUSABLE

Т.е. подсказка INDEX при выполнении запроса играет более важную роль, чем параметр skip_unusable_indexes, что также отражается в трейсе 10053 оптимизатора:

Index Stats::
Index: T_IDX  Col#: 1
LVLS: 1  #LB: 4  #DK: 20  LB/K: 1.00  DB/K: 5.00  CLUF: 116.00
User hint to use this index

...

Best:: AccessPath: IndexRange  Index: T_IDX

Что само по себе достаточно странно: Oracle настойчиво пытается использовать индексный доступ в соответствии с пользовательской подсказкой, несмотря на «плохой» статус индекса (хорошо известный оптимизатору, что видно из выполнения запроса без подсказки) !

То же самое происходит при использовании в запросе подсказки RULE (т.е. давно официально не поддерживаемого RBO = Rule Based Optimizer), но только в Oracle 10.2:

10.2.0.4.0#SQL> select/*+ rule*/ count(rowid) from t where owner = 'OUTLN';
select/*+ rule*/ count(rowid) from t where owner = 'OUTLN'
*
ERROR at line 1:
ORA-01502: index 'SYSTEM.T_IDX' or partition of such index is in unusable state

Появление последней ошибки как-то можно объяснить: RBO давно не поддерживается, действует точно в соответствии с правилами, по которым индексный доступ считался более приоритетным, чем сканирование таблицы,… и т.д.

НО тот же запрос с подсказкой RULE в Oracle 11.2 выполняется без ошибок(!):

11.2.0.1.0#SQL> select status from dba_indexes where index_name = 'T_IDX';

STATUS
--------
VALID

11.2.0.1.0#SQL> select/*+ rule*/ count(rowid) from t where owner = 'OUTLN';

-----------------------------------
| Id  | Operation         | Name  |
-----------------------------------
|   0 | SELECT STATEMENT  |       |
|   1 |  SORT AGGREGATE   |       |
|*  2 |   INDEX RANGE SCAN| T_IDX |
-----------------------------------

11.2.0.1.0#SQL> alter index t_idx unusable;

Index altered.

11.2.0.1.0#SQL> select/*+ rule*/ count(rowid) from t where owner = 'OUTLN';

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|*  2 |   TABLE ACCESS FULL| T    |
-----------------------------------

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

2 - filter("OWNER"='OUTLN')

Note
-----
- rule based optimizer used (consider using cbo)

Кроме того, что в версии 11.2 rule based optimizer научился определять статус индекса, трейс события 10053 (т.е. трейс CBO, как я всегда полагал) показывает сформированную секцию OUTLINE с комментарием RBO_OUTLINE (последнее — только в случае установки параметра optimizer_mode=RULE на уровне сессии, без подсказки RULE в запросе)

  /*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
RBO_OUTLINE
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER"))  or  FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/

В том же трейсе можно найти записи в секции Query Transformations — при этом большая часть трансформаций игнорируются по причине использования rule-based mode:

Query transformations (QT)
**************************
CBQT bypassed for query block SEL$1 (#0): rule-based mode.

но не все — ORDER BY ELIMINATION (OBYE) не отвергается для RBO, хотя в рассматриваемом случае и не применяется:

Order-by elimination (OBYE)
***************************
OBYE:     OBYE bypassed: no order by to eliminate.

Похоже, что механизм rule-based оптимизации был обновлён (и получил развитие) в Oracle 11g R2: «научился» определять состояние индексов, пишет 10053 трейс и, возможно, умеет использовать некоторые операции трансформации запросов (Query Transformations), ранее доступные только интеллигентному Cost-Based оптимизатору?

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

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

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