Оптимизируйте массовое обновление с помощью распараллеливания

У меня есть процедура, используемая для шифрования транзакционных данных, перемещающих живые данные в тестовую среду. Рассматриваемый стол вмещает ок. 100 миллионов строк распределены по 50 разделам. Каждый месяц добавляется новый раздел. По мере увеличения громкости процедура выполняется медленнее, чем раньше.

Я пытаюсь ввести некоторую степень параллелизма в свой код. Это новая территория, и мне интересно, есть ли какие-либо лучшие практики. Возможно, используйте dbms_parallel_execute для разделения обновления на куски?

Любые рекомендации по оптимизации моего кода очень ценятся!

PROCEDURE Scramble_Transactions
AS
    vSeed              BINARY_INTEGER;

    CURSOR Transactions_cur
    IS
        SELECT T.ID,
               T.MONTH_PARTITION,
               T.TRACE_NUM,
               T.TXTDATA
          FROM TRANSACTIONS T;

    TYPE TBL IS TABLE OF Transactions_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    Transactions_Rec   TBL;

    vCounter           NUMBER (10);
    vString            VARCHAR2 (300);
    vLen               NUMBER (5);
    vFromRange         VARCHAR2 (25);
    vToRange           VARCHAR2 (25);
BEGIN
    vCounter := 0;

    SELECT SUBSTR (TO_CHAR (SYSDATE, 'ddmmyyyyhhmiss'), 11)
      INTO vSeed
      FROM DUAL;

    DBMS_RANDOM.initialize (vSeed);
    DBMS_RANDOM.SEED (vSeed);
    vFromRange := 0;

    OPEN Transactions_cur;

    LOOP
        FETCH Transactions_cur BULK COLLECT INTO Transactions_Rec LIMIT 10000;

        FOR I IN 1 .. Transactions_Rec.COUNT
        LOOP
            IF Transactions_Rec (i).TRACE_NUM IS NOT NULL
            THEN
                vString := Transactions_Rec (i).TRACE_NUM;
                vLen := LENGTH (TRIM (vString));
                vToRange := POWER (10, vLen) - 1;
                Transactions_Rec (i).TRACE_NUM :=
                    LPAD (TRUNC (DBMS_RANDOM.VALUE (vFromRange, vToRange)),
                          6,
                          '1');
            END IF;

            IF Transactions_Rec (i).TXTDATA IS NOT NULL
            THEN
                vString := Transactions_Rec (i).TXTDATA;
                vLen := LENGTH (TRIM (vString));
                vToRange := POWER (10, vLen) - 1;
                Transactions_Rec (i).TXTDATA :=
                    LPAD (TRUNC (DBMS_RANDOM.VALUE (vFromRange, vToRange)),
                          12,
                          '3');
            END IF;

            vCounter := vCounter + 1;
        END LOOP;

        FORALL rec IN 1 .. Transactions_Rec.COUNT
            UPDATE Transactions
               SET TRACE_NUM = Transactions_Rec (rec).TRACE_NUM,
                   TXTDATA = Transactions_Rec (rec).TXTDATA
             WHERE ID = Transactions_Rec (rec).ID
               AND MONTH_PARTITION = Transactions_Rec (rec).MONTH_PARTITION;

        EXIT WHEN Transactions_cur%NOTFOUND;
    END LOOP;

    DBMS_RANDOM.TERMINATE;

    CLOSE Transactions_cur;

    COMMIT;
END Scramble_Transactions;

Редактировать, мое решение основано на приведенных ниже отзывах: Перепишите часть процедуры так, чтобы скремблирование данных выполнялось как часть SQL, а не PL/SQL. Процедура теперь также принимает раздел из/в в качестве параметров, позволяющих выполнять параллельную обработку.

CREATE OR REPLACE PROCEDURE Scramble_Transactions(P_MONTH_PARTITION_FROM VARCHAR2, P_MONTH_PARTITION_FROM VARCHAR2)
AS

CURSOR Transactions_cur (V_MONTH_PARTITION_FROM TRANSACTIONS.MONTH_PARTITION%TYPE, 
V_MONTH_PARTITION_TO TRANSACTIONS.MONTH_PARTITION%TYPE) IS

  SELECT T.ID,
               T.MONTH_PARTITION,
               REGEXP_REPLACE(T.TRACE_NUM,'[0-9]','9') TRACE_NUM,
               REGEXP_REPLACE(T.TXTDATA,'[0-9]','9') TXTDATA
          FROM TRANSACTIONS T WHERE T.MONTH_PARTITION BETWEEN P_MONTH_PARTITION_FROM AND P_MONTH_PARTITION_FROM ;

    TYPE TBL IS TABLE OF Transactions_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    Transactions_Rec   TBL;

BEGIN
OPEN Transactions_cur(P_MONTH_PARTITION_FROM,P_MONTH_PARTITION_FROM);
LOOP
   FETCH Transactions_cur BULK COLLECT INTO Transactions_Rec LIMIT 10000;

       /*Some additional processing*/

       FORALL rec IN 1 .. Transactions_Rec.COUNT
            UPDATE Transactions
               SET TRACE_NUM = Transactions_Rec (rec).TRACE_NUM,
                   TXTDATA = Transactions_Rec (rec).TXTDATA
             WHERE ID = Transactions_Rec (rec).ID
               AND MONTH_PARTITION = Transactions_Rec (rec).MONTH_PARTITION;

  EXIT WHEN  Transactions_cur%NOTFOUND;
END LOOP;
CLOSE Transactions_cur;
COMMIT;
END;
/

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

DECLARE
  L_TASK_SQL CLOB;
  V_TASKNAME USER_PARALLEL_EXECUTE_TASKS.TASK_NAME%TYPE;
  V_STATUS   USER_PARALLEL_EXECUTE_TASKS.STATUS%TYPE;
  C_TASK_NAME VARCHAR2(50) := 'TRANSACTIONS_TASK';
BEGIN
  L_TASK_SQL := 'SELECT PARTITION_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ''TRANSACTIONS''';
  DBMS_PARALLEL_EXECUTE.CREATE_TASK(C_TASK_NAME);
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(
        TASK_NAME => 'TRANSACTIONS_TASK',
        SQL_STMT  => L_TASK_SQL,
        BY_ROWID  => FALSE);
  DBMS_PARALLEL_EXECUTE.RUN_TASK(
        TASK_NAME      => C_TASK_NAME,
        SQL_STMT => 'BEGIN SCRAMBLE_TRANSACTIONS( :START_ID, :END_ID ); END;',
        LANGUAGE_FLAG  => DBMS_SQL.NATIVE,
        PARALLEL_LEVEL => 6);

  SELECT TASK_NAME, STATUS INTO V_TASKNAME,V_STATUS FROM USER_PARALLEL_EXECUTE_TASKS WHERE TASK_NAME = C_TASK_NAME; 
  DBMS_OUTPUT.PUT_LINE('TASK:'|| 'V_TASKNAME' ||' , STATUS:'|| V_STATUS);

  DBMS_PARALLEL_EXECUTE.DROP_CHUNKS(TASK_NAME => 'TRANSACTIONS_TASK');
  DBMS_PARALLEL_EXECUTE.DROP_TASK(TASK_NAME  => 'TRANSACTIONS_TASK');
END;
/

Общее общее время выполнения снижено до 30 минут по сравнению с 13-14 часами ранее.