вторник, 28 апреля 2015 г.

Некоторые особенности индексного поиска в Oracle

1. Зачем создавать индексы на ссылочных полях (FK – foreign key) ?

* Очевидно для оптимизации процессов соединения таблиц.
* Но есть и другое, не менее важное, предназначение:
Если таблица фактов в схеме «звезда» ссылается (FK) на таблицу измерений, и на ссылочном поле нет индекса, то DML (Insert / Update / Delete ) операция над таблицей измерений заблокирует таблицу фактов на изменение целиком.
Если на ссылочном поле есть индекс, то произойдет блокировка только нужных строк из таблицы фактов, тех которые затрагивает изменение в таблице измерений.
Пример: таблица с данными продаж (факты) и таблица кассовых мест (измерения), с некоторой периодичностью происходит изменение списка кассовых мест, то на ссылочном поле кассовое место / таблица продаж, желательно создать индекс.
Если этого не сделать, то данные в продажах будут заблокированы до commit / rollback обновления списка кассовых мест.

2. Compressed index

Сжатие индексов может пригодится, если индекс состоит из нескольких столбцов, несколько первых из которых мало селективны.
В этом случае при создании индекса можно указать:
compressed N
где N – кол-во колонок. Пример: индекс по 3 полям, первые 2 из которых малоселективны (online, status)
Индекс без сжатия
Online,0,AAAPvCAAFAAAAFaAAa
Online,0,AAAPvCAAFAAAAFaAAg
Online,0,AAAPvCAAFAAAAFaAAl
Online,2,AAAPvCAAFAAAAFaAAm
Online,3,AAAPvCAAFAAAAFaAAq
Online,3,AAAPvCAAFAAAAFaAAt
Индекс со сжатием
Online,0
AAAPvCAAFAAAAFaAAa
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
Online,2
AAAPvCAAFAAAAFaAAm
Online,3
AAAPvCAAFAAAAFaAAq
AAAPvCAAFAAAAFaAAt
В этом случае группа одинаковых данных N первых колонок будут сжаты в одну запись в индексе, а ROWID самих записей будут помещены в вспомогательную структуру. Уменьшение числа листов индекса соответственно ускорит доступ к данным.
Хочу заметить, что данный способ применим только для низко селективных столбцов. Если данные достаточно уникальны, то создание доп. структур на хранение записей только создаст дополнительные накладные расходы!
Так же стоит заметить, что без compressed первый столбцы индекса наоборот должны содержать наиболее селективные данные, чтобы Oracle за меньшее кол-во операций мог дойти до уникальных данных.

3. Advanced compress - oracle 12

Advanced compress - новый шаг развития компрессии индексов в Oracle 12.
Oracle автоматически подбирает размер компрессии на уровне каждого блока индекса, но порядок столбцов нужно все также соблюдать самостоятельно.

Пример:
0. Обычный индекс
1. Индекс с компрессией 1 - занимает 3200 блоков
2. Индекс с компрессией 2 - занимает 2816 блоков
3. ADVANCED LOW - занимает 2816 блоков, т.е. Oracle самостоятельно подобрал уровень компрессии = 2
4. ADVANCED LOW - но в начале плохосжимаемый селективный столбец. Oracle не применяет компрессии, выходит теже 3584 блоков.
Т.е. за порядком столбцов все также надо следить, можно только не делать analyze index, чтобы узнать оптимальный уровень компрессии.
drop table t$t purge;
  
create table t$t as select 1 c1, round( dbms_random.VALUE(1, 10)) c2, level as c3
from dual connect by level < 1000000;
  
create index idx_t$t_0      on t$t(c1, c2, c3, 1);
create index idx_t$t_1      on t$t(c1, c2, c3, 2)  COMPRESS 1;
create index idx_t$t_2      on t$t(c1, c2, c3, 3)  COMPRESS 2;
create index idx_t$t_low    on t$t(c1, c2, c3, 4)  COMPRESS ADVANCED LOW;
create index idx_t$t_c3_low on t$t(c3, c1, c2, 5)  COMPRESS ADVANCED LOW;

 
select segment_type, segment_name, bytes, blocks from SYS.USER_SEGMENTS where segment_name like '%T$T%';

SEGMENT_TYPE       SEGMENT_NAME             BYTES     BLOCKS
------------------ ----------------------- ---------- ----------
TABLE              T$T                      18874368       2304
INDEX              IDX_T$T_0                29360128       3584
INDEX              IDX_T$T_1                26214400       3200
INDEX              IDX_T$T_2                23068672       2816
INDEX              IDX_T$T_LOW              23068672       2816
INDEX              IDX_T$T_C3_LOW           29360128       3584

 6 rows selected 

Скрипт определения индексов для сжатия со списком колонок.
Формула определения необходимости сжатия колонки: произведение кол-ва уникальных значений в текущей и предыдущих колонках < 200 (значение случайное)
Индексы сортируются по значению: кол-во строк в индексе / произведение кол-ва уникальных значений в текущей и предыдущих колонках * кол-во колонок для сжатия
Таким образом вначале будут индексы, которым сильней необходимо сжатие
with t as (
  select /*+ MATERIALIZE */ I.TABLE_NAME, i.index_name, I.NUM_ROWS, C.COLUMN_POSITION, C.COLUMN_NAME, s.NUM_DISTINCT,
    CASE WHEN
     -- EXP (SUM (LN ( col )) ==  MULTPL(col)
     EXP (SUM (LN (s.NUM_DISTINCT)) OVER(PARTITION BY I.TABLE_NAME, i.index_name ORDER BY C.COLUMN_POSITION))  <= 200
    THEN 
      1
    END as NEED_COMPRESS
  from dba_indexes i 
  join DBA_IND_COLUMNS c on C.INDEX_OWNER = i.OWNER AND C.INDEX_NAME = i.index_name AND C.TABLE_NAME = I.TABLE_NAME
  join DBA_TAB_COL_STATISTICS s on S.OWNER = i.OWNER AND S.TABLE_NAME = I.TABLE_NAME AND S.COLUMN_NAME = C.COLUMN_NAME
  WHERE i.OWNER = :OWN AND i.COMPRESSION = 'DISABLED' AND i.INDEX_TYPE = 'NORMAL'
  AND i.LEAF_BLOCKS > 0
  order by I.TABLE_NAME, i.index_name, C.COLUMN_POSITION
)
select TABLE_NAME, index_name, NUM_ROWS, NUM_DISTINCT, COMPR_COLS, COMPR_FACTOR
from (
  select TABLE_NAME, index_name, MAX(NUM_ROWS) as NUM_ROWS, 
    ROUND( EXP (SUM (LN (CASE WHEN NEED_COMPRESS = 1 THEN NUM_DISTINCT END))) ) as NUM_DISTINCT,
    LISTAGG(CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_NAME END, ', ') WITHIN GROUP (ORDER BY COLUMN_POSITION) as COMPR_COLS,
    ROUND( MAX(NUM_ROWS) / SUM(CASE WHEN NEED_COMPRESS = 1 THEN NUM_DISTINCT END) ) * COUNT(DISTINCT CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_POSITION END) as COMPR_FACTOR,
    COUNT(DISTINCT CASE WHEN NEED_COMPRESS = 1 THEN COLUMN_POSITION END) as COLUMN_COUNT
  from t
  WHERE NEED_COMPRESS > 0
  GROUP BY TABLE_NAME, index_name
)
order by COMPR_FACTOR DESC nulls last, TABLE_NAME, index_name
FETCH FIRST 500 ROWS ONLY;

Предварительный результат сжатия индекса можно определить средствами Oracle:
analyze index "IDX" validate structure;
select * from index_stats;
Последние 2 столбца index_stats дадут информацию о желательном уровне сжатия и % уменьшения индекса после компрессии.

4. reverse index

Обычный btree index, но данные этого индекса развернуты наоборот.
Такой индекс хорошо подходит для последовательно генерируемых данных, к примеру для первичного ключа, создаваемого по sequence.
Если использовать обычный индекс то данные будут писаться последовательно в блоки, что может увеличить конкуренцию за диск при вставке или выборке.
Пример:
Первичный ключ:
* Обычный индекс: 12345, 12346, 12347
Данные пишутся на диск последовательно.
* Реверсивный индекс: 54321, 64321, 74321
Видно, что reverse данные хорошо будут раскиданы по диску.
Что должно уменьшить число событий «buffer busy» и «read by other session»

5. Scip scan

Уникальная фича Oracle, которой нет почти ни у одной субд. Возможность поиска по правой части индекса.
Scip scan индекса может быть применен, если первые (левые или лидирующие) столбцы малоселективны. Тогда Oracle может создать логические подиндексы для каждой записи левой части индекса.
Пример:
Индекс по полям: GENDER, EMAIL – GENDER малоселективная левая часть индекса, EMAIL высокоселективная правая часть.
При запросе вида
SELECT * FROM T WHERE EMAIL = ?
Будет использоваться scip scan, и запрос на внутреннем уровне будет преобразован к виду:
select * from T where gender = 'F' AND EMAIL = ?
union all
select * from T WHERE gender = 'M' AND EMAIL = ?
Если первый столбец достаточно селективен, что встречается чаще (см. п.2), то scip scan использоваться не будет.

6. Способы доступа к индексу.

a. index scan - последовательное чтение по связанному списку. Данные в этом случае будут отсортированы по индексу.
b. index fast full scan - многоблочно читается сегмент целиком и выбираются блоки индекса. Может быть применено только если not null фильт или колонка. В этом случае данные получаются неотсортированными по индексу.
c. index range scan - сканирование по диапазону. Может использоваться только одно условие отличное от =


7. Характеристики индексов.

a. clustering factor index - как записи в индексе соответствуют (упорядочены) данным в таблице
* в идеале clustering factor = числу блоков, т.е. при чтении из индекса не надо прыгать по разным блокам
* если clustering factor = числу строк, то при запросе из индекса будет много переходов в разные блоки, что приведет к огромному числу чтений


Дополнительная информация по внутреннему устройству индекса в статье Oracle: реализация btree индекса

8. Оптимизация доступа по индексу при Nested Loops.

** Обычный NL без оптимизации (Oracle 9)
Наиболее долгая операция при поиске по индексу, это рандомный доступ (scaterred) к таблице по rowid из последовательного индекса (sequencial).
В случае hdd большую часть времени будет выполняться позиционирование головки винта, чем собственно чтение данных.
----------------------------------------------
| Operation                 |  Name    |  Rows |
------------------------------------------------
| SELECT STATEMENT          |          |   225 |
|  NESTED LOOPS             |          |   225 |
|   TABLE ACCESS BY INDEX RO|T2        |    15 |
|    INDEX FULL SCAN        |T2_I1     |    15 |
|   TABLE ACCESS BY INDEX RO|T1        |     3K| 
|    INDEX RANGE SCAN       |T1_I1     |     3K|
------------------------------------------------

** Prefetching (Oracle 10) - читает в буферный кэш смежные данные, в надежде, что они пригодятся
Чем хуже фактор кластеризации (на основе статистики), тем больше блоков читается за раз (multy block read)
-----------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      0 |        |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T1    |      1 |     15 |
|   2 |   NESTED LOOPS                |       |      1 |    225 | --225 строк, но всего 15 запросов из T1_I1 (блоки читаются не по одному, а по mbrc за раз)
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |
-----------------------------------------------------------------

** batching (Oracle 11-12) - накапливается rowid и читает их потом скопом и многопоточно (multy block read)
Чем хуже фактор кластеризации (на основе реальных запросов из индекса-таблицы), тем больше блоков читается за раз (mbrc)
-----------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |
|   1 |  NESTED LOOPS                 |       |      1 |    225 | --накапиливается несколько rowid
|   2 |   NESTED LOOPS                |       |      1 |    225 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     15 |  -- выполняется параллельный селект
|   4 |     INDEX FULL SCAN           | T2_I1 |      1 |   3000 |
|*  5 |    INDEX RANGE SCAN           | T1_I1 |     15 |     15 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T1    |    225 |     15 |  -- выполняется параллельный селект
-----------------------------------------------------------------

Batching на HDD диске может дать 10 кратное ускорение, а на SSD до 2 раз.

9. Утилиты мониторинга выполнения запроса.

Просто интересная возможность, напрямую не относящаяся к индексам.
Если на базе включен мониторинг и запрос выполняется с хинтом /*+ MONITOR */ , то можно выполнять такие запросы:
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '7cgk3nrg7mprm', session_id =>434, type=>'ACTIVE') from dual; 
Получим красивую статистику выполнения запроса, последовательность вызова, время выполнения, число чтений, этапы и т.д. в виде html, который просто нужно открыть в браузере
 
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK') FROM dual; 
Получим рекомендации по созданию индексов на данных, по которым часто выполняются выборки.

В 12.2 добавилась возможность мониторинга группы операций:
v_xDBOpId := dbms_sql_monitor.begin_operation('Расчет функции calc', forced_tracking => 'Y');
calc();
dbms_sql_monitor.end_operation('Расчет функции calc', v_xDBOpId); 
В мониторинге будут видны все операции объединенные в группу "Расчет функции calc"

Настроить мониторинг долгой операции также можно с помощью вызова функции "DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS". Прогресс выполнения можно будет смотреть в "v$session_longops"

На основе книги "Expert Indexing In Oracle Database 11g"