Как оптимизировать конкурентную вставку записей с индексом по ключу из последовательности?

Question

Есть такая таблица и к ней последовательность:

create table t1 (id number constraint pkt1 primary key, name varchar2 (16))
/
create sequence t1seq 
/

Для вставки в таблицу есть процедура, которая упрощённо выглядит так:

create or replace procedure populateTable as
begin
    for i in 1..100000 loop 
        insert into t1 values (t1seq.nextval, dbms_random.string ('U', 16)); 
        commit;
    end loop;
end;
/

Таблица постоянно запополняется. Причём, источников данных обычно несколько, они часто запускают процедуру одновременно в разных сессиях.

Значение первичного ключа, которое монотонно увеличивается, приводит к ряду проблем:

Процедуры в разных сессиях постоянно пытаются записать один и тот же блок с самым правым листочным узлом индекса, что приводит к поочерёдному ожиданию доступа к блоку (т.н. hot block), и как результат, снижению проиизводительности.
значение PCT_USED в статистике индекса довольно высокое - более 90%. В первую очередь потому, что частая запись максимального значения индексируемого ключа приводит к тому, что при запоненнии блока с самым правым листочным узлом, он не разбивается (split 50-50), а добавляется новый блок (split 90-10).

Какие есть пути решения?

Answer 1

В 18c для создания последовательностей добавлены две новые опции: SCALE и EXTEND.
Подробнее в гл. 24.2.4.3 Making a Sequence Scalable. Предистория в базе идей.

Они были добавлены для устранения негативных эффектов с монотонно увеличивающимися последовательностями:

A scalable sequence is particularly efficient when used to generate unordered primary or unique keys for data ingestion workloads having high level of concurrency. [...] Scalable sequences significantly reduce the sequence and index block contention and provide better data load scalability [...]

Сравним на примере. Подготовка таблиц, последовательностей и тестовой процедуры:

create table t1 (id number constraint pkt1 primary key, name varchar2 (16))
/
create sequence t1seq 
/
create table t2 (id number constraint pkt2 primary key, name varchar2 (16))
/
create sequence t2seq scale extend
/
create or replace procedure populate (tabnum int) as
begin
    for i in 1..500000 loop case tabnum 
        when 1 then insert into t1 values (t1seq.nextval, dbms_random.string ('U', 16)); 
        when 2 then insert into t2 values (t2seq.nextval, dbms_random.string ('U', 16)); end case;
        commit;
    end loop;
end;
/

Тестовую процедуру запустить одновременно в двух или более сессиях:

exec populate (1) --exec populate (2)

Посмотреть в истории сессий, какие события наступали чаще
(более релевантно для вопроса: enq: TX - index contention):

define sessions=sid1,sid2, ... sidn
select nvl (sh.event, 'on CPU') event, o.object_name object, count (*) waits
from v$active_session_history sh 
join user_objects o on o.object_id = sh.current_obj# 
where sh.session_id in (&sessions) and event is not null
and sh.sample_time > sysdate-60/(24*60) 
group by sh.event, o.object_name 
order by waits desc

После выполнения, провести анализ индексов:

analyze index pkt1 validate structure; -- pkt2 
 
select name, lf_blks, pct_used 
from index_stats
/

Результаты показывают, что значительно увеличилось кол-во листочных узлов при снижении заполнености индекса, что свидетельствует о преобладании нормального разбиения листочных блоков (split 50-50):

NAME    LF_BLKS   PCT_USED
---- ---------- ----------
PKT1       2013         94
PKT2       5342         69

PS В выпуске 12c это расширение было добавлено, но было не документированно и оффициально не поддерживалось. Те, кто работает с 12c, могут попробовать эти новые опции в тестовой среде.

БЛОГ НА HUSL

Как оптимизировать конкурентную вставку записей с индексом по ключу из последовательности?

Ответы (1 шт):