Как запустить хранимую процедуру в пакетном режиме или запустить в параллельной обработке

Question

Мы перебираем 100 000 записей из глобальной временной таблицы. Ниже хранимая процедура будет перебирать все записи из глобальной временной таблицы одну за другой и должна обрабатывать три шага.

1. чтобы увидеть, существует ли продукт или нет
2. чтобы увидеть, есть ли у продукта внутри активов "категория" или нет.
3. чтобы увидеть, имеют ли ресурсы имена файлов, начинается с "%pdf%" или нет.

Таким образом, каждая запись должна обработать эти 3 шага, и окончательные имена документов будут сохранены в таблице для успешной записи. Если на каком-либо из этапов возникнет какая-либо ошибка, сообщение об ошибке будет сохранено для этой записи.

Ниже хранимая процедура занимает много времени для обработки, потому что ее обработка последовательно.

1. Есть ли способ ускорить этот процесс в самой хранимой процедуре, выполнив пакетный процесс?
2. Если это невозможно в хранимой процедуре, можем ли мы изменить этот код на Java и запустить этот код в многопоточном режиме? например, создание 10 потоков, и каждый поток будет принимать одну запись одновременно и обрабатывать этот код. Я был бы счастлив, если бы кто-нибудь дал какой-нибудь псевдокод.

какой подход предложит?

DECLARE
V_NODE_ID  VARCHAR2(20);
V_FILENAME VARCHAR2(100);
V_CATEGORY_COUNT INTEGER :=0;  
FINAL_FILNAME VARCHAR2(2000);
V_FINAL_ERRORMESSAGE VARCHAR2(2000);


CURSOR C1 IS
SELECT isbn FROM GT_ADD_ISBNS GT;

CURSOR C2(v_isbn in varchar2) IS
SELECT ANP.NODE_ID NODE_ID
        FROM 
        table1 ANP,
        table2 ANPP,
        table3 AN
        WHERE 
      ANP.NODE_ID=AN.ID AND
    ANPP.NODE_ID=ANP.NODE_ID AND
    AN.NAME_ID =26 AND
    ANP.CATEORGY='category' AND
    ANP.QNAME_ID='categories'  AND
        ANP.NODE_ID IN(SELECT CHILD_NODE_ID 
                  FROM TABLE_ASSOC START WITH PARENT_NODE_ID IN(v_isbn) 
                      CONNECT BY PRIOR CHILD_NODE_ID = PARENT_NODE_ID);


BEGIN
--Iterating all Products
FOR R1 IN C1 
LOOP

FINAL_FILNAME :='';
BEGIN


--To check whether Product is exists or not
SELECT AN.ID INTO V_NODE_ID 
FROM TABLE1 AN,
TABLE2 ANP
WHERE
AN.ID=ANP.NODE_ID AND
ANP.VALUE in(R1.ISBN);


V_CATEGORY_COUNT :=0;
V_FINAL_ERRORMESSAGE :='';

--To check Whether Product inside the assets are having the 'category' is applied or not
FOR R2 IN C2(R1.ISBN) 
LOOP

V_CATEGORY_COUNT := V_CATEGORY_COUNT+1;  

BEGIN
--In this Logic Product inside the assets have applied the 'category' But those assets are having documents LIKE '%pdf%' or not
SELECT ANP.STRING_VALUE  into V_FILENAME
        FROM 
        table1 ANP,
        table2 ANPP,
        table3 ACD
        WHERE 
       ANP.QNAME_ID=21  AND 
       ACD.ID=ANPP.LONG_VALUE 
       ANP.NODE_ID=ANPP.NODE_ID AND
       ANPP.QNAME_ID=36 AND
       ANP.STRING_VALUE LIKE '%pdf%'  AND 
       ANP.NODE_ID=R2.NODE_ID; 

    FINAL_FILNAME := FINAL_FILNAME  || V_FILENAME ||',';

   EXCEPTION WHEN
     NO_DATA_FOUND THEN
     V_FINAL_ERRORMESSAGE:=V_FINAL_ERRORMESSAGE|| 'Category is applied for this Product But for the asset:'||  R2.NODE_ID || ':Documents[LIKE %pdf%] were not found ;';
     UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=  V_FINAL_ERRORMESSAGE  WHERE ISBN= R1.ISBN;


     END;--Iterating for each NODEID

END LOOP;--Iterating the assets[Nodes] for each product of catgeory

  --  DBMS_OUTPUT.PUT_LINE('R1.ISBN:' || R1.ISBN ||'::V_CATEGORY_COUNT:' || V_CATEGORY_COUNT);

 IF(V_CATEGORY_COUNT  = 0) THEN
     UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=  'Category is not applied to none of the Assets for this Product'  WHERE ISBN= R1.ISBN;
   END IF;  


EXCEPTION WHEN
NO_DATA_FOUND THEN
      UPDATE GT_ADD_ISBNS SET ERROR_MESSAGE=   'Product is not Found:' WHERE ISBN= R1.ISBN;
END;

  -- DBMS_OUTPUT.PUT_LINE( R1.ISBN || 'Final documents:'||FINAL_FILNAME);
      UPDATE GT_ADD_ISBNS SET FILENAME=FINAL_FILNAME WHERE ISBN= R1.ISBN;

COMMIT;
END LOOP;--looping gt_isbns
END;

Answer 1

У вас есть несколько потенциальных хитов производительности. Вот один из них:

"Мы перебираем 100 000+ записей из глобальной временной таблицы"

Глобальные временные таблицы могут быть довольно медленными. Заполнение их означает запись всех этих данных на диск; чтение с них означает чтение с диска. Это много операций ввода-вывода, которых можно избежать. Кроме того, GTT используют временное табличное пространство, поэтому вы можете вступать в конфликт с другими сеансами, выполняющими большие сортировки.

Вот еще один красный флаг:

FOR R1 IN C1 LOOP
... FOR R2 IN C2(R1.ISBN) LOOP

SQL - это язык на основе множеств. Он оптимизирован для объединения таблиц и возврата наборов данных с высокой производительностью. Вложенные циклы курсора означают построчную обработку, которую, несомненно, легче кодировать, но она может быть на несколько порядков медленнее, чем операция эквивалентного набора.

--To check whether Product is exists or not

У вас есть несколько запросов, выбирающих из одной и той же таблицы (AN, АНП) using the same criteria (isbn`). Возможно, все эти дубликаты являются единственным способом проверки ваших бизнес-правил, но это кажется маловероятным.

FINAL_FILNAME := FINAL_FILNAME || V_FILENAME ||',';

Может быть, вы могли бы переписать свой запрос, чтобы использовать listagg() вместо того, чтобы использовать процедурную логику для объединения строк?

UPDATE GT_ADD_ISBNS

Опять же, все ваши обновления являются однострочными, а не заданными.

"Есть ли способ ускорить этот процесс в самой хранимой процедуре, выполнив пакетный процесс?"

Не зная ваших правил и контекста, мы не сможем переписать вашу логику для вас, но 15-16 часов - это слишком много для этого, так что вы определенно можете сократить затраченное время.

Что нужно учитывать:

1. Замените запись и чтение во временную таблицу запросом, который вы используете для ее заполнения.
2. Перепишите циклы, чтобы использовать BULK COLLECT с высоким LIMIT (например, 1000) для повышения эффективности выбора. Узнайте больше
3. Заполните массивы и используйте FORALL для повышения эффективности обновлений. Узнайте больше
4. Попробуйте удалить все эти отдельные поиски, включив логику в основной запрос, используя синтаксис OUTER JOIN для проверки существования.

Это все догадки. Если вы действительно хотите знать, где процедура тратит время - и это знание является корнем всей успешной настройки, поэтому вам следует знать - вы должны запустить процедуру под PL/SQL Profiler. Это скажет вам, какие линии стоят больше всего времени, и именно на них обычно нужно сконцентрировать свои усилия по настройке. Если у вас еще нет доступа к DBMS_PROFILER, вам понадобится администратор БД для запуска сценария установки. Узнайте больше

"Можем ли мы изменить этот код на Java и запустить этот код в многопоточном режиме?"

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