Сегодня я хотел бы поделиться с вами своими наработками в области автоматизации процесса индексного сжатия. Теоретическая составляющая процесса, а также плюсы и минусы сжатия описаны в предыдущем посте, а сегодня более подробно остановимся на практической части.
Задача была в следующем - разработать инструментарий для сжатия индексов в фоновом режиме, без привязки к ОС. Решил написать PL/SQL пакет, а сжатие запускать в фоне через dbms_scheduler. Общая концепция - создание заданий по анализу либо сжатию индексов с указанием необходимых параметров. Задания запускаются последовательно, а вся информация по текущим заданиям, их прогрессу, статистике размера индексов и т.д. доступна для чтения в соответствующих таблицах и динамически обновляется. Для управления заданиями используется набор процедур PL/SQL пакета.
Пакет получил название INDX_COMP и следующий набор процедур:
- ADD_JOB
- RUN_JOB
- STOP_JOB
- DELETE_JOB
Названия говорят сами за себя). Процедуры RUN_JOB, STOP_JOB и DELETE_JOB предельно просты, остановимся более подробно на ADD_JOB. Аргументы:
Argument Name Type Default?
---------------- ---------- ---------------
ACTION VARCHAR2
BEGIN_TIME DATE DEFAULT SYSDATE
INDEX_NAME VARCHAR2 DEFAULT NULL
OWNER VARCHAR2 DEFAULT NULL
IN_TS VARCHAR2 DEFAULT NULL
IN_PAR_DEGREE NUMBER DEFAULT NULL
OUT_TS VARCHAR2 DEFAULT NULL
OUT_PAR_DEGREE NUMBER DEFAULT NULL
ADD_JOB - основная процедура пакета INDX_COMP, через неё происходит добавление заданий с указанием всех необходимых параметров. Единственный обязательный аргумент - ACTION - действие, которое необходимо выполнить. Доступны четыре варианта:
- ANALYZE - проанализировать набор индексов с целью поиска оптимального значения префикса
- COMPRESS - проанализировать и сжать набор индексов с оптимальным значением префикса
- ANALYZE_ALL_<MB> и COMP_ALL_<MB> - аналогично ANALYZE и COMPRESS, но применимо ко всем индексам в БД размером более <MB>
При использовании значений COMPRESS и ANALYZE набор индексов определяется полями INDEX_NAME,OWNER и IN_TS (input tablespace), то есть будут выбраны все индексы в БД, удовлетворяющие всем трём полям одновременно, а при значениях ANALYZE_ALL_<MB> и COMP_ALL_<MB> эти поля игнорируются. Поля INDEX_NAME,OWNER и IN_TS должны содержать названия через запятую, например:
INDEX_NAME => 'IND_1,IND_2,IND3',
OWNER => 'SYS,SCOTT',
IN_TS => 'DATATS1,DATATS2'
Для перечисленного набора аргументов будет сформирован набор индексов, возвращаемый запросом:
SELECT OWNER,INDEX_NAME
FROM DBA_INDEXES
WHERE INDEX_NAME IN ('IND_1','IND_2','IND3')
AND OWNER IN ('SYS','SCOTT')
AND TABLESPACE_NAME IN ('DATATS1','DATATS2')
BEGIN_TIME - время запуска задания, по умолчанию задание запускается сразу после создания, а при указании значения NULL не запускается и может быть запущено только вручную процедурой RUN_JOB.
Аргументы IN_PAR_DEGREE и OUT_PAR_DEGREE определяют степень параллелизма при анализировании и сжатии соответственно.
OUT_TS - целевое табличное пространство для сжатия. Если OUT_TS не задано, то используется исходное табличное пространство.
С пакетом разобрались, теперь перейдём к таблицам, содержащим информацию о заданиях.
- INDX_COMP_JOBS - таблица, содержащая исходные данные о заданиях, которые задаются при выполнении процедуры ADD_JOB. Её поля аналогичны аргументам процедуры ADD_JOB. Единственное новое поле - JOB_N, уникальный номер задания, который мы получаем из объекта INDX_COMP_JOB_N типа sequence.
- INDX_COMP_JOB_STATUS - таблица с динамической информацией о заданиях. Содержит такую информацию как фактическое время запуска задания, время его завершения, прогресс и т.д.
- INDX_COMP_DETAIL - содержит информацию о всех индексах всех созданных заданий, такую как текущий статус, время начала и окончания обработки, информацию о размерах (начальный, предполагаемый, конечный).
- Служебная таблица INDX_COMP_LOG - журнальная информация о выполнении заданий.
Запуск заданий происходит процедурой RUN_JOB, которая создаёт DBMS_SCHEDULER JOB под названием INDX_COMP_JOB. В пакете есть процедура MAIN, которая и вызывается JOB-ом INDX_COMP_JOB и выполняет задания.
Вернёмся немного к теории. Что касается самого анализа индекса на предмет нахождения оптимального префикса, то тут методика заключается в подсчёте ожидаемого размера индекса для каждого значения префикса и выборе наименьшего. Как посчитать размер индекса? Просуммировать размер всех префиксов и суффиксов.
Размер префикса считается по формуле: (vsize(column)+1)*num_cols+6. Функция vsize возвращает размер столбца в байтах, +1 - байт длины столбца, num_cols - количество столбцов в префиксе (длина префикса), +6 - 1 байт длины префикса, 1 lock байт и 4 байта в prefix row directory.
Формула для размера всех суффиксов: sum((vsize(column)+1)*num_cols+10). Vsize - размер столбца в байтах, +1 - байт длины столбца, num_cols - количество столбцов в суффиксе, +6 - 1 байт длины префикса, 1 lock байт и 4 байта в prefix row directory.
Ещё нужно учесть что префикс может повторяться, если все его суффиксы не влезли в 1 блок, тогда один префикс будет встречаться в каждом блоке, где есть его суффиксы. Чтобы посчитать количество повторений префикса просуммируем размер его самого и всех его суффиксов и разделим на размер блока.
Для индекса по трём столбцам с длиной префикса 2 запрос будет выглядеть примерно так:SELECT (SUM(PREFIX_SIZE * prefix_count) + SUM(SUFFIX_SUM_BYTES))
FROM (SELECT
VSIZE(Col1) + 1 + VSIZE(Col2) + 1 + 6 PREFIX_SIZE,
SUM(VSIZE(Col3) + 1 + 10) SUFFIX_SUM_BYTES,
round((VSIZE(Col1) + 1 + VSIZE(Col2) + 1 + 6 +
SUM(VSIZE(Col3) + 1 + 10)) / 8192) + 1 PREFIX_COUNT
FROM "TABLE"
GROUP BY Col1, Col2)
На самом деле этот запрос не точно определяет размер индекса, он не учитывает то, что часть блока занимает header, часть места уходит на PCT_FREE, также не учитываются branch блоки индекса, пустые блоки и т.д, но это не главное, так как цель не в подсчёте размера, а определении оптимальной длины префикса, сравнивая ожидаемые размеры. Так как погрешность для каждого значения префикса будет одинакова, то сравнение корректно.
В связи с этим значение поля EST_BYTES в таблице INDX_COMP_DETAIL может не точно отражать размер, который получится после сжатия.
На этом пожалуй всё. А вот собственно и сам пакет INDX_COMP и инструкция по его использованию, пользуйтесь на здоровье.
Если обнаружатся косяки - пишите, будем исправлять.
Комментариев нет:
Отправить комментарий