Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle 11gR2: Can I use DBMS_PARALLEL_EXECUTE to run my custom PL/SQL code?

I prepared some fiddle:

CREATE TABLE t_process
    ("process_number" int, "process_status" varchar2(12))
;

INSERT ALL 
    INTO t_process ("process_number", "process_status")
         VALUES (1, 'PROCESSING')
    INTO t_process ("process_number", "process_status")
         VALUES (2, 'PROCESSING')
    INTO t_process ("process_number", "process_status")
         VALUES (3, 'TO_BE_KILLED')
    INTO t_process ("process_number", "process_status")
         VALUES (4, 'PROCESSING')
    INTO t_process ("process_number", "process_status")
         VALUES (5, 'PROCESSING')
    INTO t_process ("process_number", "process_status")
         VALUES (6, 'TO_BE_KILLED')
    INTO t_process ("process_number", "process_status")
         VALUES (7, 'TO_BE_KILLED')
    INTO t_process ("process_number", "process_status")
         VALUES (8, 'WAITING')
    INTO t_process ("process_number", "process_status")
         VALUES (9, 'KILLED')
SELECT * FROM dual
;

This is my processing procedure:

CREATE OR REPLACE PROCEDURE MY_PROCEDURE(IN_ID IN NUMBER) IS
BEGIN
  UPDATE T_PROCESS SET process_status = 'KILLING' WHERE process_number = IN_ID;
  COMMIT;
  -- DO SOME STUFF
  UPDATE T_PROCESS SET process_status = 'KILLED' WHERE process_number = IN_ID;
  COMMIT;
END MY_PROCEDURE;

Now I want to use DBMS_PARALLEL_EXECUTE to run this custom SQL:

DECLARE
  id1 number = :id1;
  id2 number = :id2;
BEGIN
  MY_PROCEDURE(id1);
END;

So, my question is:

Can I use DBMS_PARALLEL_EXECUTE to execute above SQL statement?

Because I only found UPDATE examples. Maybe CREATE_CHUNKS_BY_SQL to select only TO_BE_KILLED and then RUN_TASK with above statement?

like image 641
WBAR Avatar asked Nov 21 '13 20:11

WBAR


People also ask

What is Dbms_parallel_execute?

The DBMS_PARALLEL_EXECUTE package allows you to manually code the task run. The GET_ROWID_CHUNK and GET_NUMBER_COL_CHUNK procedures return the next available unassigned chunk. You can than manually process the chunk and set its status. The example below shows the processing of a workload chunked by rowid.

How do I run PL SQL code in Toad?

TOAD enables you to develop and run code without having to leave one environment and go to another (as you would using an editor and SQL*Plus, for example). Code can easily be run by clicking the Run button or pressing the F11 key.

What is parallel processing in Oracle?

By dividing the work necessary to run a statement among multiple processes, Oracle can run the statement more quickly than if only a single process ran it. This is called parallel execution or parallel processing.


1 Answers

YES, You can very well do that 11g onwards. I am surprised why it was not suggested here.

You can execute a procedure inside run_task like begin MY_PROCEDURE( :start_id, :end_id ); end;

You may need to modify procedure to accept two parameters :start_id, :end_id

Here is the sample code (based on ‘create_chunks_by_rowid’).

DECLARE
  l_task     VARCHAR2(30) := 'parallel_processing';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => l_task,
                                               table_owner => 'SCHEMANAME',
                                               table_name  => 'T_PROCESS',
                                               by_row      => TRUE,
                                               chunk_size  => 10000);

  l_sql_stmt := 'begin MY_PROCEDURE( :start_id, :end_id ); end;';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 10);

  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  END LOOP;

  DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/

You can create chunks by following means as well based upon your situation / comfort.

CREATE_CHUNKS_BY_NUMBER_COL -- If you want to update by 'process_number'

CREATE_CHUNKS_BY_SQL -- If you think BY_SQL is going to give you a very smaller set of chunks to be processed. Beware of the fact that each chunk will be able to process just 1 row per chunk (start_id and end_id will same for each chunk) in this approach.

like image 96
pahariayogi Avatar answered Jan 03 '23 12:01

pahariayogi