К ВОЗМОЖНОСТИ ПАРАЛЛЕЛЬНОГО ВЫПОЛНЕНИЯ КОМАНДЫ DELETE В ORACLE И MYSQL/MARIADB БЕЗ ИСПОЛЬЗОВАНИЯ PAR

добавлено: 27 ноя 14
понравилось:0
просмотров: 3042
комментов: 0

теги:

Автор: AlexeiF

ORACLE имеет возможность выполнение параллельного выполнения команды DELELE используя HINT PARALLEL. Но из практического опыта выполнения данной команды следует его ограниченность в динамическом выполнении (количество параллельных Threads) и достаточно больших системных издержек,а также возможностях динамического управления количества удаляемых рядов за один "прогон", т.е. это можно осуществить, но каждый раз необходимо читать таблицу полностью ( run FULL table scan), что приводит драматически к потери производительности.

В данной работе будет рассмотрен общий подход и идеи как можно выполнять независимое параллельное удаление рядов из таблицы, причем количество параллельных потоков выполнения (Threads) можно динамически менять ,в зависимости от количества удаляемых рядов/строк и скажем требуем времени выполнения всей работы вцелом или же ресурсов выделяемых на выполнения работы.

Весь подход предполагает три основных этапа: на основании логики удаления тех или иных рядов строится SELECT по выборы идентификации этих рядов которые нужно удалить; создается и заполняется таблица с идентификационными номерами удаляемых рядов из основной таблицы и запускают параллельно работы по удалению рядов.

1. Допустим нам надо удалить определенное количество рядов из таблицы с деталями , скажем ОRDER_DTL, на основании удаленных уже рядов из мастер таблицы ORDER_MASTER. Связь между таблицами идет через ORDER_ID атрибут. Понятно, что взаимосвязь между таблицами ODRED_MASTER & ORDER_DTL есть "One-To-Many". Каждая из таблиц обладает колонками ORDER_MASTER_ID & ODRED_DTL_ID с уникальными значениями в них.

Здесь и далее мы будем использовать ORACLE и потом я сделаю дополнения связанные с MySQL/MariaDB.
Создаем таблицу ORDER_DTL_DEL:

create table ORDER_DTL_DEL
as
select x.*,dense_rank() over (order by block_id) block_group_nbr from (
select rownum rn, ri, (dbms_rowid.rowid_block_number(ri)||'-'||dbms_rowid.rowid_relative_fno (ri)) Block_code
from
(
select /*+ PARALLEL (ORDER_DTL,4) */ rowid ri, from ORDER_DTL
where ORDER_ID not in (select ORDER_ID from ODRED_MASTER)
)
) x
order by 4;

Т.о. мы создали таблицы, где у нас будут находится ROWID рядов, что нам надо удалить из таблицы ORDER_DTL, код блока где этот ряд находится (Block_code) и номер разбивки всех блоков. Основная идея последнего состоит в том , что бы при выполнении команды DELETE в разных Thread у нас не попадались удаляемые ряды из одного и того же блока. Тем самым мы значительно уменьшаем вероятность Block's Lock.

2. Для удаления рядов из ORDER_DTL таблицы по ROWID мы использовали следующий код, позволяющий делать BULK DELETE некими "порциями" по 100000 рядов. Число 100000 величина чисто эмпирическая в данном случае и была выбрана из конкретной задачи по удалению. Основанная идея ее состоит в том чтобы найти оптимальное время, в пересчете на время удаления одного ряда. То что эта величина существует было видно из эксперимента. Удаление скажем, 50000 рядов и 200000 в пересчете на на одно удаление дала величину большую , чем при удалении 100000. Все зависит от железа, а точнее скорости CPU, наличие и количества индексов на таблице ORDER_DTL, распределения рядов по блокам и в особенности блоков индексов. Очевидно, что если надо производить удаление рядов в конце дня, я данные вставлялись в таблицу в течении дня и естественным образом отсортированные по времени, то чтение и UPDAET index block требует совсем другого времени, чем если бы данные были отреставрированные другим образом. И более того, прежде чем UPDATE index BLOCK, база данных (ORACLE) должна отсортировать новые значения по блокам и потом уже работать с обновлением INDEX block. Время сортировка является величиной явно не линейной, т.е. сортировка 100000 рядов и 50000 рядов не в два раза больше, посему и можно найти некую эмпирическую оптимальную величины для BULK DELETE.
Итак вот этот код:
Create or replace procedure ORDER_DTL_DEL(THR Number, THR_SIZE Number)
as
TYPE t_id IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;


v_id t_id;

CURSOR c1
IS
select ri from ORDER_DTL_DEL
where block_group_nbr > (thr-1)*THR_SIZE
and block_group_nbr <= (thr)*THR_SIZE;

BEGIN
OPEN c1;

LOOP
FETCH c1 BULK COLLECT INTO v_id LIMIT 100000;

EXIT WHEN v_id.COUNT = 0;

FORALL i IN v_id.FIRST .. v_id.LAST
DELETE FROM ORDER_DTL
WHERE ROWID = v_id (i);

COMMIT;
DBMS_OUTPUT.put_line (
'deleted rows: '
|| v_id.COUNT
|| ' at: '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd hh24:mi:ss'));
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('encountered an exception: ' || SQLERRM);
END;
/
3. Далее идет код , который позволяет удалить 9800000 рядов используя 10 параллельно выполняемых работ.
Declare
jName VARCHAR2(2);
BEGIN
FOR i IN 1..10 LOOP
jName := 'J' || TO_CHAR(i);
dbms_job.submit(jName, 'PLSQL_BLOCK',
'begin ORDER_DTL_DEL('||TO_CHAR(i)||', 85000); end;',
start_date=>SYSDATE);
END LOOP;
DBMS_JOB.RUN(jName);
COMMIT;
END run_parallel;
/
Здесь величина 85000 была получина из Мах(block_group_nbr)/< среднее число рядов в блоке >/< количество Threads(10) >. Понятно что величина 85000*< среднее число рядов в блоке >*< количество параллельных Threads(10) > должно быть больше или равно количеству удаляемых рядов (9800000).

Замечания.
1.В данных примерах используются методы хранения данных в блоках для ORACLE ( rowid) и ORACLE system package (dbms_rowid, dbms_job), чтов общем-то не обязательно. Если брать в качестве rowid -> ODRED_DTL_ID и иметь уникальный индекс на эту колонку, а в качестве block_group_nbr -> значение хаш функции на ODRED_DTL_ID ( к примеру mod(ODRED_DTL_ID, 10)+1 ) то вполне данный подход можно использовать для баз данных MySQL/MariaDB.
2. Для параллельного выполнения можно легко приспособить JAVA.
3. Наилучшая производительность будет достигнута если до того как выполнять работу по DELETE рядов, сделать все индексы UNUSABLE, удалить ряды и перестроить индексы используя опцию PARALLEL.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии