Параллельное выполнение хранимых процедур Oracle

Можно получить выигрыш в производительности, если выполнять обработку данных параллельно несколькими потоками — в нескольких сеансах Oracle. При помощи пакета DBMS_JOB этого можно было добиться и в более ранних версиях Oracle. Однако Oracle Scheduler, появившийся в версии 10 и усовершенствованный в версии 11, предоставляет для этого более развитые возможности. В этой статье

  • кратко рассмотрю цепочки (chains) Oracle Scheduler, 
  • продемонстрирую создание цепочки для параллельной обработки данных, 
  • продемонстрирую организацию параллельной обработки данных без создания цепочки. 

Цепочка Oracle Scheduler

  • позволяет скоординировать выполнение нескольких программ, 
  • имеет имя, 
  • создается с помощью dbms_scheduler.create_chain, 
  • запускается на выполнение с помощью dbms_scheduler.run_chain, 
  • удаляется с помощью dbms_scheduler.drop_chain. 

Цепочка содержит шаги (chain steps) и правила (chain rules).

Шаг цепочки

  • определяет, какую программу (или цепочку) необходимо запустить, 
  • имеет имя, 
  • создается с помощью dbms_scheduler.define_chain_step, 
  • запускается на выполнение согласно правилам цепочки, 
  • удаляется с помощью dbms_scheduler.drop_chain_step. 

Правило цепочки

  • содержит условие и действие, 
  • однократно при выполнении условия выполняет действие, 
  • имеет имя, 
  • создается с помощью dbms_scheduler.define_chain_rule, 
  • удаляется с помощью dbms_scheduler.drop_chain_rule. 

Обычно, условия правил проверяют завершение выполнения шагов цепочки, а действия правил — запускают на выполнение шаги цепочки. В каждой правильно построенной цепочке должны быть

  • правило с условием, которое выполняется безусловно(!), например, 1=1; это правило запускает на выполнение первый логический шаг цепочки; 
  • правило с действием END, которое завершает выполнение цепочки. 

Прежде чем построить и выполнить демонстрационную цепочку, нужно познакомиться еще с одним объектом Oracle Scheduler — программой (program). Программа

  • определяет PL/SQL блок, 
  • имя PL/SQL процедуры или внешнюю команду ОС, которую необходимо выполнить, 
  • имеет имя, 
  • создается с помощью dbms_scheduler.create_program, 
  • может иметь параметры, 
  • создаваемые с помощью dbms_scheduler.define_program_argument, 
  • запускается на выполнение из шага цепочки или из задания (scheduler job), 
  • удаляется с помощью dbms_scheduler.drop_program. 

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

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

Для демонстрационных целей создам таблицу demo_data:

  1. SQL> CREATE TABLE demo_data (data VARCHAR2(#cc66cc;">50));
  2. Table created
  3.  

Прежде всего, определю программы, которые будут использоваться цепочкой:

  1. SQL> BEGIN
  2. #cc66cc;">2 -- подготовка почвы для других программ
  3. #cc66cc;">3 dbms_scheduler.create_program(
  4. #cc66cc;">4 program_name => 'DEMO_P_START',
  5. #cc66cc;">5 program_type => 'PLSQL_BLOCK',
  6. #cc66cc;">6 program_action => 'BEGIN DELETE FROM demo_data; END;',
  7. #cc66cc;">7 enabled => TRUE
  8. #cc66cc;">8 );
  9. #cc66cc;">9 -- эти программы параллельно заполняют таблицу demo_data результатами работы
  10. #cc66cc;">10 dbms_scheduler.create_program(
  11. #cc66cc;">11 program_name => 'DEMO_P_0',
  12. #cc66cc;">12 program_type => 'PLSQL_BLOCK',
  13. #cc66cc;">13 program_action =>
  14. #cc66cc;">14 'INSERT INTO demo_data SELECT ''0''||LEVEL FROM dual CONNECT BY LEVEL <= 5;',
  15. #cc66cc;">15 enabled => TRUE
  16. #cc66cc;">16 );
  17. #cc66cc;">17 dbms_scheduler.create_program(
  18. #cc66cc;">18 program_name => 'DEMO_P_1',
  19. #cc66cc;">19 program_type => 'PLSQL_BLOCK',
  20. #cc66cc;">20 program_action =>
  21. #cc66cc;">21 'INSERT INTO demo_data SELECT ''1''||LEVEL FROM dual CONNECT BY LEVEL <= 5;',
  22. #cc66cc;">22 enabled => TRUE
  23. #cc66cc;">23 );
  24. #cc66cc;">24 dbms_scheduler.create_program(
  25. #cc66cc;">25 program_name => 'DEMO_P_2',
  26. #cc66cc;">26 program_type => 'PLSQL_BLOCK',
  27. #cc66cc;">27 program_action =>
  28. #cc66cc;">28 'INSERT INTO demo_data SELECT ''2''||LEVEL FROM dual CONNECT BY LEVEL <= 5;',
  29. #cc66cc;">29 enabled => TRUE
  30. #cc66cc;">30 );
  31. #cc66cc;">31 -- последний штрих, используя подготовленные данные
  32. #cc66cc;">32 dbms_scheduler.create_program(
  33. #cc66cc;">33 program_name => 'DEMO_P_FINALLY',
  34. #cc66cc;">34 program_type => 'PLSQL_BLOCK',
  35. #cc66cc;">35 program_action => 'UPDATE demo_data SET data = ''#''||data;',
  36. #cc66cc;">36 enabled => TRUE
  37. #cc66cc;">37 );
  38. #cc66cc;">38 END;
  39. #cc66cc;">39 /
  40. PL/SQL procedure successfully completed
  41.  

Для программы типа PLSQL_BLOCK в качестве выполняемого действия можно указать PL/SQL блок, как для DEMO_P_START выше, или фрагмент кода на PL/SQL, не заключенный в ключевые слова BEGIN и END. Перед запуском на выполнение Oracle Scheduler поместит код программы внутрь PL/SQL блока.

Посмотреть созданные программы можно следующим запросом:

  1. SQL> SELECT program_name, program_type, program_action
  2. #cc66cc;">2 FROM dba_scheduler_programs
  3. #cc66cc;">3 WHERE program_name LIKE 'DEMO_P_%';
  4. PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION
  5. -------------------- ---------------- --------------------------------------------------------------------------------
  6. DEMO_P_0 PLSQL_BLOCK INSERT INTO demo_data SELECT '0'||LEVEL FROM dual CONNECT BY LEVEL <= #cc66cc;">5;
  7. DEMO_P_1 PLSQL_BLOCK INSERT INTO demo_data SELECT '1'||LEVEL FROM dual CONNECT BY LEVEL <= #cc66cc;">5;
  8. DEMO_P_2 PLSQL_BLOCK INSERT INTO demo_data SELECT '2'||LEVEL FROM dual CONNECT BY LEVEL <= #cc66cc;">5;
  9. DEMO_P_FINALLY PLSQL_BLOCK UPDATE demo_data SET data = '#'||data;
  10. DEMO_P_START PLSQL_BLOCK BEGIN DELETE FROM demo_data; END;
  11.  

Создам цепочку DEMO_CHAIN:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.create_chain(
  3. #cc66cc;">3 chain_name => 'DEMO_CHAIN'
  4. #cc66cc;">4 );
  5. #cc66cc;">5 END;
  6. #cc66cc;">6 /
  7. PL/SQL procedure successfully completed
  8.  

Каждый шаг цепочки будет связан с одной из программ, определенных выше. В связи с этим, можно автоматизировать создание шагов цепочки:

  1. SQL> BEGIN
  2. #cc66cc;">2 #b1b100;">FOR x IN (
  3. #cc66cc;">3 SELECT program_name
  4. #cc66cc;">4 FROM dba_scheduler_programs
  5. #cc66cc;">5 WHERE owner = USER
  6. #cc66cc;">6 AND program_name LIKE 'DEMO_P_%')
  7. #cc66cc;">7 LOOP
  8. #cc66cc;">8 dbms_scheduler.define_chain_step(
  9. #cc66cc;">9 chain_name => 'DEMO_CHAIN',
  10. #cc66cc;">10 step_name => 'DEMO_S_' || REGEXP_SUBSTR(x.program_name, '[^_]+$'),
  11. #cc66cc;">11 program_name => x.program_name
  12. #cc66cc;">12 );
  13. #cc66cc;">13 END LOOP;
  14. #cc66cc;">14 END;
  15. #cc66cc;">15 /
  16. PL/SQL procedure successfully completed
  17.  

В результате определены следующие шаги:

  1. SQL> SELECT step_name, step_type, program_name
  2. #cc66cc;">2 FROM dba_scheduler_chain_steps
  3. #cc66cc;">3 WHERE chain_name='DEMO_CHAIN';
  4. STEP_NAME STEP_TYPE PROGRAM_NAME
  5. -------------------- -------------- --------------------
  6. DEMO_S_0 PROGRAM DEMO_P_0
  7. DEMO_S_START PROGRAM DEMO_P_START
  8. DEMO_S_FINALLY PROGRAM DEMO_P_FINALLY
  9. DEMO_S_2 PROGRAM DEMO_P_2
  10. DEMO_S_1 PROGRAM DEMO_P_1
  11.  

Для завершения определения цепочки нужно создать правила. Первое правило запускает на выполнение шаг DEMO_S_START:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.define_chain_rule(
  3. #cc66cc;">3 chain_name => 'DEMO_CHAIN',
  4. #cc66cc;">4 condition => '1=1',
  5. #cc66cc;">5 action => 'START DEMO_S_START',
  6. #cc66cc;">6 rule_name => 'DEMO_R_START'
  7. #cc66cc;">7 );
  8. #cc66cc;">8 END;
  9. #cc66cc;">9 /
  10. PL/SQL procedure successfully completed
  11.  

Следующее правило проверяет, завершен ли шаг DEMO_S_START, и запускает шаги DEMO_S_0, DEMO_S_1 и DEMO_S_2 параллельно:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.define_chain_rule(
  3. #cc66cc;">3 chain_name => 'DEMO_CHAIN',
  4. #cc66cc;">4 condition => 'DEMO_S_START SUCCEEDED',
  5. #cc66cc;">5 action => 'START DEMO_S_0, DEMO_S_1, DEMO_S_2',
  6. #cc66cc;">6 rule_name => 'DEMO_R_CONTINUE'
  7. #cc66cc;">7 );
  8. #cc66cc;">8 END;
  9. #cc66cc;">9 /
  10. PL/SQL procedure successfully completed
  11.  

Третье правило проверяет, завершены ли шаги DEMO_S_0, DEMO_S_1 и DEMO_S_2, и запускает шаг DEMO_S_FINALLY:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.define_chain_rule(
  3. #cc66cc;">3 chain_name => 'DEMO_CHAIN',
  4. #cc66cc;">4 condition => 'DEMO_S_0 COMPLETED AND DEMO_S_1 COMPLETED AND DEMO_S_2 COMPLETED',
  5. #cc66cc;">5 action => 'START DEMO_S_FINALLY',
  6. #cc66cc;">6 rule_name => 'DEMO_R_FINALLY'
  7. #cc66cc;">7 );
  8. #cc66cc;">8 END;
  9. #cc66cc;">9 /
  10. PL/SQL procedure successfully completed
  11.  

Четвертое, и последнее, правило завершает выполнение цепочки после завершения шага DEMO_S_FINALLY:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.define_chain_rule(
  3. #cc66cc;">3 chain_name => 'DEMO_CHAIN',
  4. #cc66cc;">4 condition => 'DEMO_S_FINALLY COMPLETED',
  5. #cc66cc;">5 action => 'END',
  6. #cc66cc;">6 rule_name => 'DEMO_R_END'
  7. #cc66cc;">7 );
  8. #cc66cc;">8 END;
  9. #cc66cc;">9 /
  10. PL/SQL procedure successfully completed
  11.  

Посмотреть созданные правила можно следующим запросом:

  1. SQL> select rule_name, condition, action
  2. #cc66cc;">2 from dba_scheduler_chain_rules
  3. #cc66cc;">3 where chain_name = 'DEMO_CHAIN';
  4. RULE_NAME CONDITION ACTION
  5. -------------------- ---------------------------------------------------------------------- --------------------------------------------------------------------------------
  6. DEMO_R_FINALLY DEMO_S_0 COMPLETED AND DEMO_S_1 COMPLETED AND DEMO_S_2 COMPLETED START "DEMO_S_FINALLY"
  7. DEMO_R_END DEMO_S_FINALLY COMPLETED END
  8. DEMO_R_START #cc66cc;">1=#cc66cc;">1 START "DEMO_S_START"
  9. DEMO_R_CONTINUE DEMO_S_START SUCCEEDED START "DEMO_S_0","DEMO_S_1","DEMO_S_2"
  10.  

Условия правил используют либо специальный синтаксис (scheduler chain condition syntax), либо синтаксис выражений SQL WHERE. В условиях можно проверять не только завершение шагов как таковое (ИМЯШАГА COMPLETED), но и успешное (ИМЯШАГА SUCCEEDED) или ошибочное завершение шага (ИМЯШАГА FAILED), а также анализировать код ошибки, переданный из программы через RAISE_APPLICATION_ERROR (ИМЯШАГА ERROR_CODE). Об условиях и действиях правил см. описание процедуры dbms_scheduler.define_chain_rule в документации по Oracle 11g.

Прежде, чем цепочку пожно будет запустить на выполнение, необходимо ее активировать:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.enable('DEMO_CHAIN');
  3. #cc66cc;">3 END;
  4. #cc66cc;">4 /
  5. PL/SQL procedure successfully completed
  6.  

Теперь запущу цепочку и проверю результат ее работы:

  1. SQL> BEGIN
  2. #cc66cc;">2 dbms_scheduler.run_chain('DEMO_CHAIN', '');
  3. #cc66cc;">3 END;
  4. #cc66cc;">4 /
  5. PL/SQL procedure successfully completed
  6. SQL> select * from demo_data;
  7. DATA
  8. --------------------------------------------------------------------------------
  9. #21
  10. #22
  11. #23
  12. #24
  13. #25
  14. #11
  15. #12
  16. #13
  17. #14
  18. #15
  19. #31
  20. #32
  21. #33
  22. #34
  23. #35
  24. #cc66cc;">15 rows selected
  25.  

Успешно! Посмотреть подробности выполнения шагов цепочки можно следующим запросом:

  1. SQL> SELECT job_name, job_subname, status, actual_start_date, run_duration
  2. #cc66cc;">2 FROM dba_scheduler_job_run_details
  3. #cc66cc;">3 WHERE job_subname LIKE 'DEMO%'
  4. #cc66cc;">4 AND log_date > SYSDATE - #cc66cc;">1/#cc66cc;">96
  5. #cc66cc;">5 ORDER BY actual_start_date;
  6. JOB_NAME JOB_SUBNAME STATUS ACTUAL_START_DATE RUN_DURATION
  7. ------------------------------ ------------------------------ --------------- -------------------------------------------------- --------------------------------------
  8. RUN_CHAIN$DEMO_CHA42864 DEMO_S_START SUCCEEDED #cc66cc;">22-JAN-#cc66cc;">15 12.16.43.194386 PM ASIA/VLADIVOSTOK +000 00:00:00
  9. RUN_CHAIN$DEMO_CHA42864 DEMO_S_0 SUCCEEDED #cc66cc;">22-JAN-#cc66cc;">15 12.16.43.291043 PM ASIA/VLADIVOSTOK +000 00:00:00
  10. RUN_CHAIN$DEMO_CHA42864 DEMO_S_1 SUCCEEDED #cc66cc;">22-JAN-#cc66cc;">15 12.16.43.293827 PM ASIA/VLADIVOSTOK +000 00:00:00
  11. RUN_CHAIN$DEMO_CHA42864 DEMO_S_2 SUCCEEDED #cc66cc;">22-JAN-#cc66cc;">15 12.16.43.310581 PM ASIA/VLADIVOSTOK +000 00:00:00
  12. RUN_CHAIN$DEMO_CHA42864 DEMO_S_FINALLY SUCCEEDED #cc66cc;">22-JAN-#cc66cc;">15 12.16.43.745810 PM ASIA/VLADIVOSTOK +000 00:00:00
  13.  

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

  1. SQL> SELECT chain_name, step_name, state, error_code, completed, start_date, end_date