Oracle11.2新特性之使用DBMS_PARALLEL_EXECUTE包實現並行

使用DBMS_PARALLEL_EXECUTE包實現並行

該包支援insert、update、delete、merge、匿名包自動以scheduler job 方式並行執行。
支援的chunk方式包括：
CREATE_CHUNKS_BY_NUMBER_COL Procedure
CREATE_CHUNKS_BY_ROWID Procedure
CREATE_CHUNKS_BY_SQL Procedure

測試：
SQL> create table employees
  2  as
  3  select * from dba_objects;
 
Table created

SQL> select count(*) from mh.employees;
 
  COUNT(*)
----------
     72787
 
SQL>
 
SQL> select count(*) from mh.employees where object_id=data_object_id;
 
  COUNT(*)
----------
      7253

DECLARE
  l_sql_stmt VARCHAR2(1000);
  l_try NUMBER;
  l_status NUMBER;
BEGIN
 
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
 
  -- Chunk the table by ROWID
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'MH', 'EMPLOYEES', true, 100);
 
  -- Execute the DML in parallel
  l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e
      SET e.object_id = e.object_id + 10
      WHERE rowid BETWEEN :start_id AND :end_id';
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
                                 parallel_level => 10);
 
  -- If there is an error, RESUME it for at most 2 times.
  L_try := 0;
  L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  LOOP
    L_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
    L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
  END LOOP;
 
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
  
END;
/

執行期間在另一個session中查詢：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as
 
SQL>
SQL> select * from user_parallel_execute_chunks;
 
CHUNK_ID TASK_NAME STATUS     START_ROWID        END_ROWID            START_TS            END_TS             
-------- --------- ---------- ------------------ ------------------ - ------------------- -------------------
       1 mytask    PROCESSED  AAAVBvAAGAAAAEgAAA AAAVBvAAGAAAAEnCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       2 mytask    PROCESSED  AAAVBvAAGAAAAEoAAA AAAVBvAAGAAAAEvCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       3 mytask    PROCESSED  AAAVBvAAGAAAAEwAAA AAAVBvAAGAAAAE3CcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       4 mytask    PROCESSED  AAAVBvAAGAAAAE4AAA AAAVBvAAGAAAAE/CcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       5 mytask    PROCESSED  AAAVBvAAGAAAAFAAAA AAAVBvAAGAAAAFHCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       6 mytask    PROCESSED  AAAVBvAAGAAAAFIAAA AAAVBvAAGAAAAFPCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       7 mytask    PROCESSED  AAAVBvAAGAAAAFQAAA AAAVBvAAGAAAAFXCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       8 mytask    PROCESSED  AAAVBvAAGAAAAFYAAA AAAVBvAAGAAAAFfCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
       9 mytask    PROCESSED  AAAVBvAAGAAAAFgAAA AAAVBvAAGAAAAFnCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      10 mytask    PROCESSED  AAAVBvAAGAAAAFoAAA AAAVBvAAGAAAAFvCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      11 mytask    PROCESSED  AAAVBvAAGAAAAFwAAA AAAVBvAAGAAAAF3CcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      12 mytask    PROCESSED  AAAVBvAAGAAAAF4AAA AAAVBvAAGAAAAF/CcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      13 mytask    PROCESSED  AAAVBvAAGAAAAGAAAA AAAVBvAAGAAAAGHCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      14 mytask    PROCESSED  AAAVBvAAGAAAAGIAAA AAAVBvAAGAAAAGPCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      15 mytask    PROCESSED  AAAVBvAAGAAAAGQAAA AAAVBvAAGAAAAGXCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      16 mytask    PROCESSED  AAAVBvAAGAAAAGYAAA AAAVBvAAGAAAAGfCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      17 mytask    PROCESSED  AAAVBvAAGAAAAIAAAA AAAVBvAAGAAAAIxCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      18 mytask    PROCESSED  AAAVBvAAGAAAAIyAAA AAAVBvAAGAAAAJjCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      19 mytask    PROCESSED  AAAVBvAAGAAAAJkAAA AAAVBvAAGAAAAJ/CcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      20 mytask    PROCESSED  AAAVBvAAGAAAAKAAAA AAAVBvAAGAAAAKxCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      21 mytask    PROCESSED  AAAVBvAAGAAAAKyAAA AAAVBvAAGAAAALjCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.40
      22 mytask    PROCESSED  AAAVBvAAGAAAALkAAA AAAVBvAAGAAAAL/CcP   05-1月 -14 02.27.39 05-1月 -14 02.27.39
      23 mytask    PROCESSED  AAAVBvAAGAAAAMAAAA AAAVBvAAGAAAAMxCcP   05-1月 -14 02.27.39 05-1月 -14 02.27.40
      24 mytask    PROCESSED  AAAVBvAAGAAAAMyAAA AAAVBvAAGAAAANjCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      25 mytask    PROCESSED  AAAVBvAAGAAAANkAAA AAAVBvAAGAAAAN/CcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      26 mytask    PROCESSED  AAAVBvAAGAAAAOAAAA AAAVBvAAGAAAAOxCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      27 mytask    PROCESSED  AAAVBvAAGAAAAOyAAA AAAVBvAAGAAAAPjCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      28 mytask    PROCESSED  AAAVBvAAGAAAAPkAAA AAAVBvAAGAAAAP/CcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      29 mytask    PROCESSED  AAAVBvAAGAAAAQAAAA AAAVBvAAGAAAAQxCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      30 mytask    PROCESSED  AAAVBvAAGAAAAQyAAA AAAVBvAAGAAAARjCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      31 mytask    PROCESSED  AAAVBvAAGAAAARkAAA AAAVBvAAGAAAAR/CcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      32 mytask    PROCESSED  AAAVBvAAGAAAASAAAA AAAVBvAAGAAAASxCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      33 mytask    PROCESSED  AAAVBvAAGAAAASyAAA AAAVBvAAGAAAATjCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      34 mytask    PROCESSED  AAAVBvAAGAAAATkAAA AAAVBvAAGAAAAT/CcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      35 mytask    PROCESSED  AAAVBvAAGAAAAUAAAA AAAVBvAAGAAAAUxCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      36 mytask    PROCESSED  AAAVBvAAGAAAAUyAAA AAAVBvAAGAAAAVjCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      37 mytask    PROCESSED  AAAVBvAAGAAAAVkAAA AAAVBvAAGAAAAV/CcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      38 mytask    PROCESSED  AAAVBvAAGAAAAWAAAA AAAVBvAAGAAAAWxCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      39 mytask    PROCESSED  AAAVBvAAGAAAAWyAAA AAAVBvAAGAAAAXjCcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
      40 mytask    PROCESSED  AAAVBvAAGAAAAXkAAA AAAVBvAAGAAAAX/CcP   05-1月 -14 02.27.40 05-1月 -14 02.27.40
 
40 rows selected
 
SQL>
SQL> select * from user_parallel_execute_tasks;
 
TASK_NAME CHUNK_TYPE   STATUS    TABLE_OWNER  TABLE_NAME   JOB_PREFIX  LANGUAGE_FLAG EDITION   FIRE_APPLY_TRIGGER PARALLEL_LEVEL JOB_CLASS
--------- ------------ --------- ------------ -----------  ----------  ------------- --------  ------------------ -------------- ------------------------------
mytask    ROWID_RANGE  FINISHED  MH           EMPLOYEES    TASK$_506               1 ORA$BASE  TRUE                           10 DEFAULT_JOB_CLASS
                                                                                                                                                                                                                                                                                                                     SET e.object_id = e.object_id + 10                                                                                                                                                      

執行結束後：
SQL> select count(*) from mh.employees where object_id=data_object_id;
 
  COUNT(*)
----------
         1

SQL> select * from user_parallel_execute_tasks;
 
TASK_NAME CHUNK_TYPE   STATUS    TABLE_OWNER  TABLE_NAME   JOB_PREFIX  LANGUAGE_FLAG EDITION   FIRE_APPLY_TRIGGER PARALLEL_LEVEL JOB_CLASS
--------- ------------ --------- ------------ -----------  ----------  ------------- --------  ------------------ -------------- ------------------------------

SQL>

SQL> select * from user_parallel_execute_chunks;
 
CHUNK_ID TASK_NAME STATUS     START_ROWID        END_ROWID            START_TS            END_TS             
-------- --------- ---------- ------------------ ------------------ - ------------------- -------------------
 
SQL>