Процедура Oracle PL SQL занимает много времени после обработки самого последнего оператора в процедуре.

Мы используем Oracle 11.2.0.4.0. Мы создали процедуру для обновления столбца CLOB в таблице для всех доступных строк.

У нас есть оператор DBMS_OUTPUT.PUT_LINE для печати SYSDATE в самой последней строке процедуры.

Когда мы выполняем эту процедуру, для ее завершения требуется много времени после того, как последняя строка напечатает SYSDATE.

Не уверен, что я что-то упускаю. Любая помощь приветствуется

Мы попытались выполнить процедуру с помощью операторов DBMS_OUPUT.PUT_LINE(SYSDATE) внутри процедуры для отладки/нахождения болевых точек.

Вот как мы выполнили процедуру и вставили вывод в конец:

SET TIMING ON
SET SERVEROUTPUT ON

SELECT 'Outside block Start time: ' || to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') OUTSIDEBLOCKSTARTTIME from dual;
DECLARE
  I_LIMIT BINARY_INTEGER;
  I_STR_TO_REPLACE VARCHAR2(200);
  I_REPLACEMENT_STR VARCHAR2(200);
BEGIN
  I_LIMIT := 10000;
  I_STR_TO_REPLACE := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890';
  I_REPLACEMENT_STR := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890';

  P_TEST_CLOB_MASK(
    I_LIMIT => I_LIMIT,
    I_STR_TO_REPLACE => I_STR_TO_REPLACE,
    I_REPLACEMENT_STR => I_REPLACEMENT_STR
  );

END;
/
SELECT 'Outside block End time: ' || to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') OUTSIDEBLOCKENDTIME from dual;

Ниже приведен код процедуры:

CREATE OR REPLACE PROCEDURE p_test_clob_mask (
   i_limit             IN   PLS_INTEGER DEFAULT 1000,
   i_str_to_replace    IN   VARCHAR2
         DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890',
   i_replacement_str   IN   VARCHAR2
         DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'
)
IS
   CURSOR cur_fetch_cddaddcomphistory
   IS
      SELECT ROWID row_id, ua4cf_cddaddcomphistory ua4cf_cddaddcomphistory
        FROM generic_set11
       WHERE ua4cf_cddaddcomphistory IS NOT NULL;
   l_limit                NUMBER         := 0;
   l_counter              NUMBER         := 0;
   l_rowid                ROWID;
   l_hold_clob            CLOB;
   l_masked_clob          CLOB;
   l_length_clob          NUMBER         := 0;
   l_str_to_replace       VARCHAR2 (500);
   l_replacement_str      VARCHAR2 (500);
   l_overall_start_dt     DATE;
   l_batch_start_dt       DATE           := NULL;
   l_batch_time_taken     NUMBER         := 0;
   l_overall_time_taken   NUMBER         := 0;
BEGIN
   l_overall_start_dt := SYSDATE;
   DBMS_OUTPUT.put_line (   'Overall Start: '
                         || TO_CHAR (l_overall_start_dt,
                                     'DD-MON-RR HH24:MI:SS'
                                    )
                        );
   l_str_to_replace := i_str_to_replace;
   l_replacement_str := i_replacement_str;
   l_limit           := i_limit;

   OPEN cur_fetch_cddaddcomphistory;

   LOOP
      FETCH cur_fetch_cddaddcomphistory
       INTO l_rowid, l_hold_clob;

      EXIT WHEN cur_fetch_cddaddcomphistory%NOTFOUND;
      l_counter := l_counter + 1;
      l_length_clob := DBMS_LOB.getlength (l_hold_clob);
      DBMS_LOB.createtemporary (l_masked_clob, TRUE);
      DBMS_LOB.writeappend (l_masked_clob,
                            l_length_clob,
                            TRANSLATE (l_hold_clob,
                                       i_str_to_replace,
                                       i_replacement_str
                                      )
                           );

      UPDATE generic_set11
         SET ua4cf_cddaddcomphistory = l_masked_clob
       WHERE ROWID = l_rowid;

      IF MOD (l_counter, l_limit) = 0
      THEN
         COMMIT;
      END IF;
   END LOOP;

   CLOSE cur_fetch_cddaddcomphistory;

   COMMIT;
   l_overall_time_taken :=
                        TRUNC (((SYSDATE - l_overall_start_dt) * 24 * 60), 2);
   DBMS_OUTPUT.put_line (   'Overall End: '
                         || TO_CHAR (SYSDATE, 'DD-MON-RR HH24:MI:SS')
                         || ' - Time taken in Mins: '
                         || l_overall_time_taken
                         || ' - Counters: '
                         || l_counter
                        );
END p_test_clob_mask;
/

Мы ожидаем, что после завершения последнего DBMS_OUPUT.PUT_LINE процедура должна завершиться.

Но прошедшее время говорит о 20+ минутах, где, согласно последнему утверждению DBMS_OUPUT.PUT_LINE, затраченное время составляло всего 3 минуты.

Ниже приведен вывод:

OUTSIDEBLOCKSTARTTIME
-------------------------------------------------------
Outside block Start time: 27-DEC-2018 15:33:45

Elapsed: 00:00:00.118
Overall Start: 27-DEC-18 15:33:45
Overall End: 27-DEC-18 15:37:37 - Time taken in Mins: 3.86 - Counters: 138913


PL/SQL procedure successfully completed.

Elapsed: 00:20:19.313

OUTSIDEBLOCKENDTIME
-----------------------------------------------------
Outside block End time: 27-DEC-2018 15:54:04

Elapsed: 00:00:00.095