Oracle-dbms_parallel_execute包批量dml使用

前言:

dbms_parallel_execute包是Oracle11G的新功能，通过使用dbms_parallel_execute对DML批量操作按rowid,列，sql语句进行chunk切片，并发处理，从而提高批量操作的速度以及避免大事务的发生。

DBMS_PARALLEL_EXECUTE介绍:

使用限制:

1 参数job_queue_processe必须要大于0

2 用户需要CREATE JOB,execute on DBMS_SQL 的权限

3 parallel参数设置大于0

dbms_parallel_execute模块介绍:

CREATE_CHUNKS_BY_NUMBER_COL

说明：CREATE_CHUNKS_BY_NUMBER_COL，将表通过指定的字段进行切片，这个字段必须是数字型，找出这个字段的最大值和最小值，然后根据分块值平均将其分成多个部分。
参数: 
TASK_NAME      VARCHAR2    IN
TABLE_OWNER    VARCHAR2    IN
TABLE_NAME      VARCHAR2    IN
TABLE_COLUMN    VARCHAR2    IN
CHUNK_SIZE      NUMBER      IN

CREATE_CHUNKS_BY_ROWID

说明：CREATE_CHUNKS_BY_ROWID，通过rowid来切分表，被切分的表必须是物理存放的表，需要含有物理rowid地址，所以不支持索引组织表之类。
参数:
TASK_NAME      VARCHAR2    IN
TABLE_OWNER    VARCHAR2    IN
TABLE_NAME      VARCHAR2    IN
BY_ROW         BOOLEAN     IN
CHUNK_SIZE      NUMBER      IN

CREATE_CHUNKS_BY_SQL

说明：CREATE_CHUNKS_BY_SQL，通过指定的SELECT语句结果进行切分
参数:
TASK_NAME      VARCHAR2    IN
SQL_STMT      CLOB      IN
BY_ROWID      BOOLEAN     IN

CREATE_TASK

说明：创建执行的任务。
参数:
TASK_NAME      VARCHAR2    IN
COMMENT      VARCHAR2    IN     DEFAULT

DROP_TASK

说明：删除执行的任务，通过dba_parallel_execute_chunks查询TASK_NAME
参数:
TASK_NAME      VARCHAR2    IN

DROP_CHUNKS

说明：删除执行的任务的CHUNK
参数:
TASK_NAME      VARCHAR2    IN

CREATE_CHUNKS_BY_ROWID切片示例代码

SET SERVEROUTPUT ON
BEGIN
  DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/
DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
  -- Chunk the table by the ROWID
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
  (
    TASK_NAME   => l_task,
    TABLE_OWNER => 'SCOTT', /* Replace the TABLE_OWNER as appropriately */
    TABLE_NAME  => 'TEST_TAB',
    BY_ROW      => TRUE,
    CHUNK_SIZE  => 2500
  );
  -- DML to be execute in parallel
  l_sql_stmt := 'UPDATE test_tab t SET t.num_col = t.num_col + 10 WHERE rowid BETWEEN :start_id AND :end_id';
  -- Run the task
  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;
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/
​

CREATE_CHUNKS_BY_NUMBER_COL切片示例代码

---创建出来函数，dbms_parallel_execute可以调用函数处理数据
CREATE OR REPLACE PROCEDURE
process_update
(
  p_start_id IN NUMBER,
  p_end_id IN NUMBER
)
AS
BEGIN
  UPDATE TEST_TAB T
  SET T.NUM_COL = T.NUM_COL + 10
  WHERE REC_ID BETWEEN p_start_id AND p_end_id;
END;
/
---进行切片
SET SERVEROUTPUT ON
DECLARE
  l_task     VARCHAR2(30) := 'test_task';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
  -- Chunk the table by the ID
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL
  (
    TASK_NAME    => l_task,
    TABLE_OWNER  => 'SCOTT', /* Replace the TABLE_OWNER value as appropriately */
    TABLE_NAME   => 'TEST_TAB',
    TABLE_COLUMN => 'REC_ID',
    CHUNK_SIZE   => 2500
  );
  -- Procedure to be execute in parallel
  l_sql_stmt := 'BEGIN PROCESS_UPDATE(: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;
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/
​

CREATE_CHUNKS_BY_SQL切片示例代码

SET SERVEROUTPUT ON
DECLARE
  l_chunk_sql VARCHAR2(1000);
  l_sql_stmt  VARCHAR2(1000);
  l_try       NUMBER;
  l_status    NUMBER;
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK ('test_task');
  -- Chunk the table by NUM_COL
  l_chunk_sql := 'SELECT DISTINCT NUM_COL, NUM_COL FROM TEST_TAB';
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL
  (
    TASK_NAME => 'test_task',
    SQL_STMT  => l_chunk_sql,
    BY_ROWID  => false
  );
  -- Execute the DML in parallel
  -- the WHERE clause contain a condition on num_col, which is the chunk
  -- column. In this case, grouping rows is by num_col.
  l_sql_stmt := 'UPDATE test_tab t SET t.num_col = t.num_col + 10 WHERE num_col BETWEEN :start_id AND :end_id';
  DBMS_PARALLEL_EXECUTE.RUN_TASK
  (
    TASK_NAME      => 'test_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('test_task');
  WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED)
  LOOP
    L_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.RESUME_TASK('test_task');
    L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('test_task');
  END LOOP;
  -- Done with processing; drop the task
  DBMS_PARALLEL_EXECUTE.DROP_TASK('test_task');
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/
​

dbms_parallel_execute使用案例

JOB服务权限配置

创建job_class配置高可用服务所在节点，避免job跨节点执行，只需要在RAC环境，单实例不需要
---在sys用户下创建 
begin
dbms_scheduler.create_job_class(
job_class_name=>'batch_job_class',
service =>'testsrv');
end;
/

用户权限

grant ADM_PARALLEL_EXECUTE_TASK,create job to test;
grant execute on sys.batch_job_class to test;

使用案例：创建存储过程process_batch_delete通过调用dbms_parallel_execute对输入的批量操作SQL进行rowid切片拆小事务，再并发执行

create or replace procedure TEST.process_batch_delete
(p_task_name in varchar2,p_table_owner in varchar2,p_table_name in varchar2,p_sql in varchar2,p_parallel in number)
is
 l_task varchar2(30);
 l_sql_stmt varchar2(32767);
 l_try number;
 l_status number;
 l_owner varchar2(30);
 l_tablename varchar2(50);
 l_parallel number;
begin
 l_task:=p_task_name;
 l_owner:=p_table_owner;
 l_tablename:=p_table_name;
 l_sql_stmt:=p_sql||'and rowid between :start_id and :end_id';
 l_parallel:=p_parallel;
 dbms_parallel_execute.create_task(task_name=>l_task);
 dbms_parallel_execute.create_chunks_by_rowid
 (task_name=>l_task,
 table_owner=>l_owner,
 table_name=>l_tablename,
 by_row=>TRUE,
 chunk_size =>10000
 );
 dbms_parallel_execute.run_task(
 task_name=>l_task,
 sql_stmt=>l_sql_stmt,
 language_flag=>dbms_sql.native,
 parallel_level=>p_parallel,
 JOB_CLASS=>'SYS.BATCH_JOB_CLASS'
 );
 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(task_name=>l_task);
  l_status:=dbms_parallel_execute.task_status(l_task);
 end loop;
 dbms_parallel_execute.drop_task(l_task);
 exception when others then
 dbms_output.put_line('Error in the code'||SQLERRM);
 end;
 /
​

使用process_batch_delete

--输入变量:
p_task_name:任务名
p_table_owner:用户名
p_table_name:表名(切片的表)
p_sql:执行批量delete的SQL，sql需要指定hint/*+rowid() */走rowid访问
p_parallel:并行进程数，设置不建议超过8
--全表删除执行例子:
exec TEST.process_batch_delete('DELETE_TEST_TASK','TEST','TAB_TEST','delete /*+rowid(asd) */ from test.TAB_TEST asd where 1=1',8);
--按条件删除执行例子:
exec TEST.process_batch_delete('DELETE_TEST_TASK','TEST','TAB_TEST','delete /*+rowid(asd) */ from test.TAB_TEST asd where asd.bookdate not between  ''2022/06/13'' and ''2022/06/15''',8);