Немного полезностей об индексах и их реорганизации

dbstalker, 03 июня

Без рассмотрения контекста, в котором используется индекс, проанализируем некоторые величины, чтобы оценить состояние конкретного индекса.

1. INDEX_STATS

Полезным представлением из словаря данных, в котором предлагается детализированная информация об индексах, является INDEX_STATS. Такое словарное представление заполняется при выполнении команды analyze index index_name validate structure. Эта команда применима для любого конкретного индекса.

При выполнении команды analyze index index_name validate structure к представлению INDEX_STATS добавляется одна заполненная строка, но эта строка не сохраняется в интервале между различными сеансами. Строка представления будет немедленно удалена, как только вы выйдете из сеанса.

Сразу же стоит обратить внимание на проблему использования validate structure – эта команда пробует заблокировать таблицу. Если кто-то обновляет таблицу, то наша команда потерпит неудачу (resource busy and acquire with NOWAIT specified). Еще хуже, если validate structure будет продолжаться некоторое время, а те, кто, попытается обновить таблицу, будут заблокированы. Таким образом , validate structure нужно выполнять или в нерабочее время, или на копии вашей базы.

В версиях 9i и более команда analyze получила конструкцию online, но, к сожалению, в этом случае таблица index_stats не заполняется.

В этом представлении нас больше всего будут интересовать столбцы:

br_rows – число строк во всех блоках ветвей (branch blocks); 
br_rows_len – сумма длин всех строк в блоках ветвей (branch blocks); 
br_blks – блоки ветвей (branch blocks); 
br_blk_len – пространство, используемое в блоке ветви (размер блока - заголовок); 
lf_blk_len – пространство, используемое в листовом блоке (размер блока - заголовок); 
lf_rows – число листовых строк; 
lf_rows_len – сумма длин всех строк в листовых блоках; 
del_lf_rows – число удаленных строк, все еще находящихся в листовых блоках; 
del_lf_rows_len – сумма длин всех удаленных строк в листовых блоках; 
height – текущая высота дерева 
MOST_REPEATED_KEY – количество повторений наиболее повторяющегося ключа (может включать повторяющиеся строки)
pre_rows_len  - суммарная длина всех префиксных строк

Первое, на что нужно обратить внимание в INDEX_STATS это столбец Height - высота индекса. Высота индекса является одним из ключей к уменьшению количества операций ввода/вывода для индекса, поэтому за ней необходимо постоянное наблюдение. По значению этого столбца до реорганизации индекса и после нее можно определить уменьшилась ли высота индекса.

Теперь приступим к анализу:

  • Первым делом нужно найти процент использования, pct_used – это просто used_space/btree_space
  • Оценим показатель del_lf_rows_len - размер пространства, которое будет доступно в существующих блоках листа для новых записей, если блоки листа были почищены. Эта величина не должна составлять большую долу от lf_rows_len. Находим коэффициент del_lf_rows_len/lf_rows_len
analyze index analiz.pk_zdata_n validate structure
select round(used_space/btree_space,3) pct_used , round(del_lf_rows_len/lf_rows_len,4)  from index_stats

2.index_histogram

Когда выполняется validate structure индекс, то также заполняется index_histogram, которое дает представление о неравномерности числа повторений различных ключей в индексе ( об этом же в index_stats дает представление колонка MOST_REPEATED_KEY – количество повторений наиболее часто повторяющегося ключа. Колонка может содержать удаленные строки.)

Столбцы представления:

REPEAT_COUNT – сколько раз в таблице повторяются один или более индексных ключей

KEYS_WITH_REPEAT_COUNT – количество повторяемых индексных ключей

Предположим, что мы узнали, что плотность - эффективность нашего индекса 75%. Можно было бы успокоиться и дальше уже этим индексом не заниматься. Он и так для нас хорош. Но как уже упоминалось выше, за этим благополучным коэффициентом спрятаны детали. А вся суть в деталях. Не так ли? Поэтому было бы полезно получить информацию о реальном количестве записей по каждому блоку.

3. sys_op_lbid

В Оракле 9i и выше мы имеем возможность подсчитать, сколько же в каждом листовом блоке есть индексных записей. Для этого можно использовать неописанную функцию sys_op_lbid(). Предлагаю использовать следующую команду:

select rows_per_block, count(*) blocks  from   
               (select sys_op_lbid( ХХХХХХ,'L', MY_TABLE.rowid) as block_id,count(*)as rows_per_block
                from MY_TABLE
                group by sys_op_lbid( ХХХХХХ,'L', MY_TABLE.rowid))
          group by rows_per_block 

ХХХХХХ – найдем запросом:
select object_id  from dba_objects
where object_type = 'INDEX' and     object_name = 'MY_TABLE'

Если мы получим необычное распределение записей по блокам, то это может являться причиной перестройки индекса. Если вы видите, то в очень большом числе блоков находится малое количество индексных записей или в малом числе блоков - большое количество записей. Такой случай может считать примером деградации индекса. В этом случае можно выполнить перестройку индекса. Но если на протяжении нескольких часов интенсивной работы вернулось прежнее состояние индекса, не стоит тогда вообще заниматься его перестройкой.

4. pctfree

Если вы реально ощущаете пользу от перестройки индекса, то вам стоит поработать над определением правильного pctfree. PCTFREE - количество свободного места (в процентах от емкости блока), чтобы избежать неиспользования (свободной) памяти в блоке индекса во время создания. Это полезно, когда индекс создается на непустой таблице, и в последующем вставки в индекс ожидаются случайными.

5. рекомендации металинка

Несмотря на то, что Великий Tom Kyte утверждает, что перестройка индексов из-за их естественной деградации – бесполезная трата времени, сил и ресурсов, все ж таки металинк дает следующий совет:

ANALYZE INDEX &index_name VALIDATE STRUCTURE; 

Вычислите отношение удаленных записей в индексе к существующим записям. И если оно превышает 20%, то нужно перестраивать индекс:

ANALYZE INDEX &index_name REBUID;

drop table my_index_stats;
create table my_index_stats as select * from index_stats;
truncate table my_index_stats;
Генерируем скрипт:
select 'analyze index '||owner||'.' || index_name || ' validate structure; '||chr(10)||
'insert into my_index_stats (select * from index_stats);' from dba_indexes 
where index_type = 'NORMAL' and owner='MY_USER';

Выполняем полученный скрипт. Он заполнить нашу таблицу my_index_stats данными из index_stats по каждому проанализированному индексу.
После этого делаем оценку пространства, занимаемого удаленными записями.

SELECT name, del_lf_rows, lf_rows - del_lf_rows lf_rows_used,round(del_lf_rows /decode(lf_rows, 0, 1, lf_rows)*100,1) ratio 
FROM my_index_stats where (to_number(del_lf_rows)/to_number(decode(lf_rows, 0, 1, lf_rows)))*100 >= 20 --20- выбраный нами плохой процент

И теперь полученные индексы можно перестраивать.

Почитайте на досуге еще немного об индексах

Немного полезностей об индексах и их реорганизации К вопросу о реорганизации индексов. Несколько простых советов К вопросу о реорганизации индексов Индексы на основе B* - дерева Как найти листовой индексный блок для конкретной записи таблицы? sys_op_lbid

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

Прокоментировать

blacksaifer
8 июня 2010 г. в 06:56

> При выполнении команды analyze index index_name validate structure к представлению INDEX_STATS добавляется одна заполненная строка

Ваше утверждение верно только для непартицированных индексов. В случае анализа партицированного индекса в таблицу INDEX_STATS будут вставлены записи для партиций \ сабпартиций индекса, коих, в подавляющем большинстве случаев, больше одной.

 

Новый комментарий

Я не спамер: введите суму 0+1



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Товары для взрослых
24 мая, 1 ответа
Выделенный сервер
23 мая, 3 ответа