Под Новый Год решил поделиться нашим решением проблемы быстрого создания нескольких индексов для таблицы, может кому пригодится.
Версия тогда была кажется 11.2.
Итак, проблема:
Каждые полчаса образуется очередная секция таблицы фактов, как - неважно. Важно, что секция не имеет локальных bitmap-индексов, и их нужно построить, всего 27 штук. При этом даже просто полное, серийное, чтение секции занимает 8 минут. Простая арифметика показывает, что потребуется как минимум 27*8 = 3.5 часа чтобы просто прочитать таблицу для каждого индекса. Не вариант.
Естественно, первым делом попробовали parallel, но он не сильно помог. Если я правильно помню, параллелизм работает (работал?) на уровне партиций, а читать одну секцию параллельно Оракл не мог. Или были другие причины о которых я теперь забыл, в общем - все равно было слишком медленно.
Тогда пришла мысль запускать создание индексов параллельно в разных процессах, через job. Один процесс будет читать блоки в кэш-буфер, а остальные читать оттуда. Главное, чтобы всем хватило памяти для одновременной работы, и чтобы блоки не вытеснялись до того, как будут использованы всеми. И действительно, скорость существенно возросла: 20 индексов строились за 22 минуты, 27 - за 24.
В процессе, одна задача читала блоки, ожидая на "scattered read", а другие ждали ее на "read by other sesion". Время от времени какая-то задача отставала на сортировке "sort output wait", но потом быстро догоняла остальных.
Была пара ньюансов:
- по умолчанию, чтение использовало "direct path read" в PGA сессии задачи, и блоки были недоступны для других сессий. Пришлось установить параметр _very_large_object_threshold и event 10949, чтобы чтение шло в SGA.
- чтобы все задачи запускались на одном и том же экземпляре RAC, мы создали job class и привязали его к сервису, работающем на нужном узле:
1.
2.
EXEC DBMS_SCHEDULER.create_job_class('CONCURRENT_INDEX_BUILD', service => 'index_builder_svc');
GRANT EXECUTE ON sys.CONCURRENT_INDEX_BUILD TO index_builder_user;
Сам код, запускающий индексирование, очень прост, он просто создает по задаче для каждого индекса. Вся хитрость в установке параметров сессии:
код
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
DECLARE
v_tmpl VARCHAR2(1000) := q'[
begin
execute immediate 'alter session set db_file_multiblock_read_count=256';
execute immediate 'alter session set workarea_size_policy=manual';
execute immediate 'alter session set sort_area_size = '||(75*1024*1024); /* customize */
execute immediate 'alter session set "_serial_direct_read" = false';
execute immediate 'alter session set "_very_large_object_threshold" = '||(100*1024);
execute immediate 'alter session set events = ''10949 trace name context forever, level 1''';
execute immediate 'create bitmap index [IDX] on changeme_table_name([COL]) nologging noparallel local compute statistics tablespace change_me';
end;
]';
BEGIN
FOR r IN (
SELECT 'IDX_TZ_ID' AS idx_name, 'TZ_ID' AS col_name FROM dual UNION ALL
SELECT 'IDX_FUNC_ID' AS idx_name, 'FUNCTION_ID' AS col_name FROM dual UNION ALL
... перечислены все 27 индексов
SELECT 'IDX_DEAL_ID' AS idx_name, 'DEAL_ID' AS col_name FROM dual
) LOOP
--dbms_scheduler.drop_job('IDX_BLD_'||r.idx_name, TRUE);
dbms_scheduler.create_job(
job_name => 'IDX_BLD_'||r.idx_name
, job_type => 'PLSQL_BLOCK'
, job_action => REPLACE(REPLACE(v_tmpl, '[IDX]', r.idx_name), '[COL]', r.col_name)
, enabled => TRUE
, job_class => 'CONCURRENT_INDEX_BUILD' /* bind to RAC node */
, comments => 'Build index '||r.idx_name||' on changeme_table_name.'||r.col_name
);
END LOOP;
END;
Надо заметить, что на узле кластера нежелательна другая full-scan активность: блоки нашей секции живут в LRU и легко легко вытесняются.
И вообще катастрофой окончилась попытка индексирования несколько таблиц или секций одновременно: они начали драться за LRU, некоторые задачи стали опаздывать и повторно читать блоки с диска опять-же в кэш -- в общем это была война всех против всех. Скорость упала до полутора часов.
Навскидку я не нашел похожих решений, впрочем я давно не занимаюсь Ораклом напрямую. Возможно, сейчас появились способы получше? Любопытно было бы узнать.
С Новым Годом!
