11.2.0.4中有严重抑郁症的DBMS_PARALLEL_EXECUTE

我们有很多数据迁移的方式, 如升级中用的xtts,tts,  impdp/expdp, imp/exp, copy from, insert+dblink.还有底层存储刷新 等等, 针对不同的场景各有选择。

而在一些单独的大表迁移时候, 如和库分库, 用GG迁移同步的初始化, insert + dblink 还是很常用的方法。 当然你也可以直接使用impdp,但若迁移的数据需要使用SQL进行多表关联过滤。Impdp可能帮不上忙。另外因为impdp没有像imp中commit的设置，所以对于那些超大的单表, （例如超过百GB）。使用impdp还得考虑对于undo的影响，

在大表使用insert+dblink的方式时候—

n   以前我们根据表的时间字段把表划分为小块. 或者利用表的分区, 这样可进行并行处理和分段提交。

n   后来我们也利用extents来把表数据分为小块, 从extents中计算起止rowid,使用insert SQL+dblink来迁移和分段提交。这样就不用管表是不是分区, 是否可以划分为小块的字段,甚至是查询那一小块数据的性能问题。因为已经获得了起止rowid, 可直接在表中扫描。

n   再后来,到了11.2 我们利用DBMS_PARALLEL_EXECUTE package, 大大简化这种迁移方式的开发工作, 而且更为安全,也利于监控。 相对于自己编写的处理程序 (读取dba_extents,解析rowid,分为”小数据块”, 写procedure 处理逻辑, 写job处理. 处理日志), 也更适合不同的开发人员理解和掌握。

DBMS_PARALLEL_EXECUTE基本上也是利用extents的方式, 但还可以对一个extents中分得更细. 它按照row number和blocknumber两种方式来分为“小数据块”。（也可以按照表中某个字段的值来划分, 不讨论这种）

参考: 11.2 New Feature : UsingDBMS_PARALLEL_EXECUTE to Update Large Tables in Parallel (文档 ID 1066555.1)

大概的过程如下。借用上面文档中的例子

BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
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);

（先创建一个task name）

-- Chunk the table by the ROWID
DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => l_task,
table_owner => 'USR',
table_name => 'TEST_TAB',
by_row => TRUE,
chunk_size => 10000);

( 指定一个表， 并按照rowid方式来划分。注意这里的chunk_size：

chunk_size指定了分为每个“小数据块”的大小, 如果by_row 为true, 那么这里chunk_size单位是行数, 如果by_row为false,那么chunk_size的单位是blocks。

那么, 如果我有一个表, 假设extents有128个block, 每个block大概是50行数据。也就是128*50=6400,如果chunk_size指定为100000, 会出现什么情况. 多个extents会被划分到一个“小数据块”中么。

     答: 不会的, DBMS_PARALLEL_EXECUTE方法处理的最小的单位会是一个extents的数据. 也就是说,不会出现start_rowid, end_rowid跨两个extents的情况, 这样被认为是不安全的。所以这个例子中, 虽然指定chunk_size指定为100000,但实际执行后, 你可以看到每个“小数据块”的行数只包括一个extents的实际大小。

（DBMS_PARALLEL_EXECUTE中的end_rowid是用最后一个blocks加上9999的行号来构建的 ）

    （在dba_parallel_execute_chunks 中查看）

-- 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';

（ 定义SQL ）

-- 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);

使用 DBMS_PARALLEL_EXECUTE.run_task运行. parallel_level指定并发程度, 这里的例子中会启动10个job来处理

-- 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;

在一次执行完后, 可能会出现某些“小数据块”报错而处理失败, 需要检查和使用resume_task直到全部处理完。

（这一步不是必须的, 但你一定需要自己检查dba_parallel_execute_chunks表中所有chunk的处理结果,比较常出现的是ORA-02049错误,这个错误时属于分布式事务的错误,可以搜索一下ORA-02049和distributed_lock_timeout的相关文章。

-- Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/
-----------------------------------------------------------------------------------------------------------------

好了, 下面是重点, 如果你在11.2.中使用DBMS_PARALLEL_EXECUTE, 会发现DBMS_PARALLEL_EXECUTE就像是一位有忧郁症的朋友。为啥呢?

举个例子, 你指定40个并发运行。那在开始运行后, 你可以从dba_jobs中看到40个job, 刚开始跑得挺正常。跑着跑着, 就只剩了30个job, 隔断时间再看, 就只剩了20个, 再隔一段时间, 10个, 气息越来越弱, 只剩5个。最后,只剩一口气,就一个job在运行了。完全不能在预计的时间内完成。

这是由于Bug18966843造成的

Bug 18966843 - Poor performanceof DBMS_PARALLEL_EXECUTE ( 文档 ID18966843.8)

( fixed  in 12.1.0.2.5)

  Bug 18966843 - Poor performance of DBMS_PARALLEL_EXECUTE (文档 ID 18966843.8).rar (31.79 KB, 下载次数: 1)

那么, 怎么治疗呢?

可以继续利用CREATE_TASK和CREATE_CHUNKS_BY_ROWID方法来创建任务和划分为”小数据块“， 而自己写一个创建job的procedure来运行。

例如下面的例子中 MY_RUN_TASK 即使自己写的一个存储过程, 实现起来也很简单。

-----------------------------------------------------------------------------------------------------------------

DECLARE

  L_SQL_STMT VARCHAR2(1000);

  L_TRY NUMBER;

  L_STATUS NUMBER;

BEGIN

  -- CREATE THE TASK

  BEGIN

DBMS_PARALLEL_EXECUTE.DROP_TASK('MIG_PSCP_SMS_SENT');

  EXCEPTION WHEN OTHERS THEN

    NULL;

  END;

  DBMS_PARALLEL_EXECUTE.CREATE_TASK('MIG_PSCP_SMS_SENT');

  -- CHUNK THE TABLE BY ROWID

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('MIG_PSCP_SMS_SENT','PSCPDATA', 'PSCP_SMS_SENT', FALSE, 100000);

   -- EXECUTE THE DML IN PARALLEL

  L_SQL_STMT := 'INSERT INTOPSCPDATA.PSCP_SMS_SENT@TO_SMS

                   SELECT *

                     FROMPSCPDATA.PSCP_SMS_SENT A

                    WHERE A.ROWID BETWEEN:START_ID AND :END_ID';

  MY_RUN_TASK('MIG_PSCP_SMS_SENT',L_SQL_STMT,50);

END;

/

-----------------------------------------------------------------------------------------------------------------

下面附件是实现存储过程的代码。仅供参考, 仅供参考, 仅供参考。

（若要在断点重做, 需要稍微修改一下, 我这里没有写成通用的）

  自己创建job对DBMS_PARALLEL_EXECUTE生成的TASK进行处理.rar (2.06 KB, 下载次数: 5)

最后还有一点, 如果你处理的表中有lob字段, 那么你在DBMS_PARALLEL_EXECUTE.run_task设置并发是没有作用的, 并发的session会报错, 这是由于lob字段对并发的限制,也就是说,对lob字段的表, 只能有一个session处理。（即使不是多个job 的处理方式，而是在单个dml语句中加上并发设置,也是同样的问题。但次情况下对分区表稍有些不同） 。 具体可以测试一下。

但好像在12c中, 已经有所不同，但没有测试过。