ITPub博客

11g包dbms_parallel_execute在海量数据处理过程中的应用

原创 Oracle 作者:lhrbest 时间:2015-06-03 17:32:56 0 删除 编辑

11gdbms_parallel_execute在海量数据处理过程中的应用





1.1  BLOG文档结构图

 

wpsE42C.tmp 

 

1.2  前言部分

 

1.2.1  导读

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① 11g包dbms_parallel_execute在海量数据处理过程中的应用

 

注意:本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

1.2.2  实验环境介绍

 

11.2.0.1  RHEL6.5

 

 

1.2.3  相关参考文章链接

 

Oracle中如何更新一张大表记录

http://blog.itpub.net/26736162/viewspace-1684095/

使用11g dbms_parallel_execute执行并行更新(下)

http://blog.itpub.net/26736162/viewspace-1683913/

使用11g dbms_parallel_execute执行并行更新(上)

http://blog.itpub.net/26736162/viewspace-1683912/

 

 

1.2.4  本文简介

 

一个朋友own_my要处理批量数据,但是脚本跑的太慢了,于是网上搜到了dbms_parallel_execute这个包,用完后给我说这个包非常强大,于是我也学习学习,关于优化一直是我喜欢的内容,在参考了大神realkid4 的blog后,我自己也做了做实验,感觉很强大,记录在此。

 

 

1.3  相关知识点扫盲

 

参考大神的bloghttp://blog.itpub.net/17203031/

 

 

1.4  实验部分

 

1.4.1  实验目标

 

测试dbms_parallel_execute包在海量数据处理过程中的应用。

1.4.2  实验过程

 

 

[oracle@etlhost206 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 3 13:40:34 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> CONN  LHR/lhr

Connected.

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

 

Table created.

 

SQL> insert into t select * from t;

 

76369 rows created.

 

SQL> insert into t select * from t;

 

152738 rows created.

 

SQL> insert into t select * from t;

 

305476 rows created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> insert into t select * from t;

 

610952 rows created.

 

SQL> insert into t select * from t;

 

1221904 rows created.

 

SQL> insert into t select * from t;

 

2443808 rows created.

 

SQL> insert into t select * from t;

 

4887616 rows created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> insert into t select * from t;

 

9775232 rows created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> insert into t select * from t;

 

19550464 rows created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> select bytes/1024/1024 from dba_segments a where a.segment_name='T';

 

BYTES/1024/1024

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

           4341

 

SQL> SELECT COUNT(1) FROM T;

 

  COUNT(1)

----------

  39100928

 

SQL> show parameter job

 

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000

SQL> show parameter cpu

 

NAME                                 TYPE        VALUE

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

cpu_count                            integer     8

parallel_threads_per_cpu             integer     2

resource_manager_cpu_allocation      integer     8

 

SQL> set timing on

SQL> set time on;

15:50:01 SQL>

15:50:02 SQL> show parameter job

 

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000

15:50:09 SQL>  select bytes/1024/1024 from dba_segments a where a.segment_name='T';

 

BYTES/1024/1024

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

           4341

 

Elapsed: 00:00:00.41

15:50:31 SQL> declare

15:50:39   2    vc_task  varchar2(100);

15:50:39   3    vc_sql   varchar2(1000);

15:50:39   4    n_try    number;

15:50:39   5    n_status number;

15:50:39   6  begin

15:50:39   7    --Define the Task

15:50:39   8    vc_task := 'Task 1: By Rowid'; --Task名称

15:50:39   9    dbms_parallel_execute.create_task(task_name => vc_task); --手工定义一个Task任务;

15:50:39  10 

15:50:39  11    --Define the Spilt

15:50:39  12    dbms_parallel_execute.create_chunks_by_rowid(task_name   => vc_task,

15:50:39  13                                                 table_owner => 'LHR',

15:50:39  14                                                 table_name  => 'T',

15:50:39  15                                                 by_row      => true,

15:50:39  16                                                 chunk_size  => 10000); --定义Chunk

15:50:39  17 

15:50:39  18    vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id';

15:50:40  19    --Run the task

15:50:40  20    dbms_parallel_execute.run_task(task_name      => vc_task,

15:50:40  21                                   sql_stmt       => vc_sql,

15:50:40  22                                   language_flag  => dbms_sql.native,

15:50:40  23                                   parallel_level => 4); --执行任务,确定并行度

15:50:40  24 

15:50:40  25    --Controller

15:50:40  26    n_try    := 0;

15:50:40  27    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

15:50:40  28    while (n_try < 2 and n_status != dbms_parallel_execute.FINISHED) loop

15:50:40  29      dbms_parallel_execute.resume_task(task_name => vc_task);

15:50:40  30      n_status := dbms_parallel_execute.task_status(task_name => vc_task);

15:50:40  31    end loop;

15:50:40  32 

15:50:40  33    --Deal with Result

15:50:40  34    dbms_parallel_execute.drop_task(task_name => vc_task);

15:50:40  35  end;

15:50:40  36  /

 

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:03:50.78

15:58:05 SQL>

15:58:06 SQL> create index idx_t_id on t(object_id) nologging parallel 4;

 

Index created.

 

Elapsed: 00:01:35.12

16:00:05 SQL> alter index idx_t_id noparallel;

 

Index altered.

 

Elapsed: 00:00:00.07

16:00:15 SQL>

16:02:51 SQL> declare

16:02:52   2    vc_task  varchar2(100);

16:02:52   3    vc_sql   varchar2(1000);

16:02:52   4    n_try    number;

16:02:52   5    n_status number;

16:02:52   6  begin

16:02:52   7    --Define the Task

16:02:52   8    vc_task := 'Task 2: By Number Col';

16:02:52   9    dbms_parallel_execute.create_task(task_name => vc_task);

16:02:52  10 

16:02:52  11    --Define the Spilt

16:02:52  12    dbms_parallel_execute.create_chunks_by_number_col(task_name    => vc_task,

16:02:52  13                                                      table_owner  => 'LHR',

16:02:52  14                                                      table_name   => 'T',

16:02:52  15                                                      table_column => 'OBJECT_ID',

16:02:52  16                                                      chunk_size   => 100000); --定义chunk

 

16:02:53  17  16:02:53  18    vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';

16:02:53  19    --Run the task

16:02:53  20    dbms_parallel_execute.run_task(task_name      => vc_task,

16:02:53  21                                   sql_stmt       => vc_sql,

16:02:53  22                                   language_flag  => dbms_sql.native,

16:02:53  23                                   parallel_level => 4);

16:02:53  24 

16:02:53  25    --Controller

16:02:53  26    n_try    := 0;

16:02:53  27    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

16:02:53  28    while (n_try < 2 and n_status != dbms_parallel_execute.FINISHED) loop

16:02:53  29      dbms_parallel_execute.resume_task(task_name => vc_task);

16:02:53  30      n_status := dbms_parallel_execute.task_status(task_name => vc_task);

16:02:53  31    end loop;

16:02:53  32 

16:02:53  33    --Deal with Result

16:02:53  34    dbms_parallel_execute.drop_task(task_name => vc_task);

16:02:53  35  end;

16:02:53  36  /

^Cdeclare

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "SYS.DBMS_LOCK", line 201

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 44

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 390

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 417

ORA-06512: at line 20

 

 

Elapsed: 00:07:12.08

 

16:11:36 SQL>

16:11:36 SQL> EXEC   dbms_parallel_execute.drop_task(task_name => 'Task 2: By Number Col');

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.11

16:31:53 SQL> declare

16:32:05   2    vc_task   varchar2(100);

16:32:05   3    vc_sql    varchar2(1000);

16:32:05   4    vc_sql_mt varchar2(1000);

16:32:05   5    n_try     number;

16:32:05   6    n_status  number;

16:32:05   7  begin

16:32:05   8    --Define the Task

16:32:05   9    vc_task := 'Task 3: By SQL';

16:32:05  10    dbms_parallel_execute.create_task(task_name => vc_task);

16:32:05  11 

16:32:05  12    --Define the Spilt

16:32:05  13    vc_sql_mt := 'select distinct object_id, object_id from t';

16:32:05  14    dbms_parallel_execute.create_chunks_by_SQL(task_name => vc_task,

16:32:05  15                                               sql_stmt  => vc_sql_mt,

16:32:05  16                                               by_rowid  => false);

16:32:05  17 

16:32:05  18    vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';

16:32:05  19    --Run the task

16:32:05  20    dbms_parallel_execute.run_task(task_name      => vc_task,

16:32:05  21                                   sql_stmt       => vc_sql,

16:32:05  22                                   language_flag  => dbms_sql.native,

16:32:05  23                                   parallel_level => 4);

16:32:05  24 

16:32:05  25    --Controller

16:32:05  26    n_try    := 0;

16:32:05  27    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

16:32:05  28    while (n_try < 2 and n_status != dbms_parallel_execute.FINISHED) loop

16:32:05  29      dbms_parallel_execute.resume_task(task_name => vc_task);

16:32:05  30      n_status := dbms_parallel_execute.task_status(task_name => vc_task);

16:32:05  31    end loop;

16:32:05  32 

16:32:05  33    --Deal with Result

16:32:05  34    dbms_parallel_execute.drop_task(task_name => vc_task);

16:32:05  35  end;

16:32:05  36  /

 

^Cdeclare

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", line 634

ORA-06512: at "SYS.DBMS_PARALLEL_EXECUTE", line 163

ORA-06512: at line 14

 

 

Elapsed: 00:01:09.08

 

16:33:14 SQL>  EXEC   dbms_parallel_execute.drop_task(task_name => 'Task 3: By SQL');

 

PL/SQL procedure successfully completed.

 

1.4.2.1  相关字典视图查询

一、 create_chunks_by_rowid过程

 

SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS;

wpsE43D.tmp 

 

 

SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS;

wpsE43E.tmp 

 

 

SELECT count(1) FROM DBA_PARALLEL_EXECUTE_CHUNKS;

 

wpsE43F.tmp 

 

 

 select status, count(*) from user_parallel_execute_chunks group by status;

wpsE440.tmp 

 

 

select D.owner,D.job_name,D.JOB_STYLE,D.JOB_TYPE,D.JOB_ACTION from dba_scheduler_jobs d where d.owner='LHR';

wpsE441.tmp 

 

 

告警日志:

Wed Jun 03 15:53:48 2015

Archived Log entry 1202 added for thread 1 sequence 2669 ID 0x6779dfc4 dest 1:

Thread 1 advanced to log sequence 2671 (LGWR switch)

  Current log# 4 seq# 2671 mem# 0: /app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_4_bpxd8g7v_.log

Wed Jun 03 15:53:49 2015

Archived Log entry 1203 added for thread 1 sequence 2670 ID 0x6779dfc4 dest 1:

Wed Jun 03 15:53:57 2015

Thread 1 advanced to log sequence 2672 (LGWR switch)

  Current log# 5 seq# 2672 mem# 0: /app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_5_bpxdbwdz_.log

Wed Jun 03 15:53:58 2015

Archived Log entry 1204 added for thread 1 sequence 2671 ID 0x6779dfc4 dest 1:

Thread 1 advanced to log sequence 2673 (LGWR switch)

  Current log# 1 seq# 2673 mem# 0: /app/oracle/oradata/CNYDB/redo01.log

Wed Jun 03 15:54:04 2015

Archived Log entry 1205 added for thread 1 sequence 2672 ID 0x6779dfc4 dest 1:

Thread 1 advanced to log sequence 2674 (LGWR switch)

  Current log# 6 seq# 2674 mem# 0: /app/oracle/flash_recovery_area/CNYDB/onlinelog/o1_mf_6_bpxdcjx2_.log

Wed Jun 03 15:54:05 2015

Archived Log entry 1206 added for thread 1 sequence 2673 ID 0x6779dfc4 dest 1:

 

 

由告警日志可以看出redo切换非常迅速,归档来不及,所以还是需要在空闲的时候来做实验。

 

 

 

 

 

二、 create_chunks_by_number_col过程

SELECT * FROM DBA_PARALLEL_EXECUTE_CHUNKS;

wpsE451.tmpwpsE452.tmp 

 

SELECT * FROM DBA_PARALLEL_EXECUTE_TASKS;

 

 

 

wpsE453.tmp 

 

 

 

select status, count(*) from dba_parallel_execute_chunks group by status;

wpsE454.tmp 

 

 

 select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$%';

wpsE455.tmp 

 

 

 

 

 

select D.owner,D.job_name,D.JOB_STYLE,D.JOB_TYPE,D.JOB_ACTION from dba_scheduler_jobs d where d.owner='LHR';

wpsE466.tmp 

 

 

 

 

1.4.3  实验总结

 

由实验可以看出,采用dbms_parallel_execute.create_chunks_by_rowid方法,4千万的数据量大约4G大小的表更新完大约4分钟,这个速度还是可以的,另外2种方式更新下来速度太慢就没有测试了,具体可以参考这里:http://blog.itpub.net/26736162/viewspace-1683912/http://blog.itpub.net/26736162/viewspace-1683913/

 

 

1.4.4  实验脚本

 

1.4.4.1  create_chunks_by_rowid方式

declare

  vc_task  varchar2(100);

  vc_sql   varchar2(1000);

  n_try    number;

  n_status number;

begin

  --Define the Task

  vc_task := 'Task 1: By Rowid'; --Task名称

  dbms_parallel_execute.create_task(task_name => vc_task); --手工定义一个Task任务;

 

  --Define the Spilt

  dbms_parallel_execute.create_chunks_by_rowid(task_name   => vc_task,

                                               table_owner => 'LHR',

                                               table_name  => 'T',

                                               by_row      => true,

                                               chunk_size  => 10000); --定义Chunk

 

  vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id';

  --Run the task

  dbms_parallel_execute.run_task(task_name      => vc_task,

                                 sql_stmt       => vc_sql,

                                 language_flag  => dbms_sql.native,

                                 parallel_level => 4); --执行任务,确定并行度

 

  --Controller

  n_try    := 0;

  n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  while (n_try < 2 and n_status != dbms_parallel_execute.FINISHED) loop

    dbms_parallel_execute.resume_task(task_name => vc_task);

    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  end loop;

 

  --Deal with Result

  dbms_parallel_execute.drop_task(task_name => vc_task);

end;

/

 

1.4.4.2  create_chunks_by_number_col

declare

  vc_task  varchar2(100);

  vc_sql   varchar2(1000);

  n_try    number;

  n_status number;

begin

  --Define the Task

  vc_task := 'Task 2: By Number Col';

  dbms_parallel_execute.create_task(task_name => vc_task);

 

  --Define the Spilt

  dbms_parallel_execute.create_chunks_by_number_col(task_name    => vc_task,

                                                    table_owner  => 'LHR',

                                                    table_name   => 'T',

                                                    table_column => 'OBJECT_ID',

                                                    chunk_size   => 10000); --定义chunk

 

  vc_sql := 'update /*+ ROWID(dda) */ t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';

  --Run the task

  dbms_parallel_execute.run_task(task_name      => vc_task,

                                 sql_stmt       => vc_sql,

                                 language_flag  => dbms_sql.native,

                                 parallel_level => 4);

 

  --Controller

  n_try    := 0;

  n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  while (n_try < 2 and n_status != dbms_parallel_execute.FINISHED) loop

    dbms_parallel_execute.resume_task(task_name => vc_task);

    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  end loop;

 

  --Deal with Result

  dbms_parallel_execute.drop_task(task_name => vc_task);

end;

/

 

1.4.4.3  create_chunks_by_SQL

declare

  vc_task   varchar2(100);

  vc_sql    varchar2(1000);

  vc_sql_mt varchar2(1000);

  n_try     number;

  n_status  number;

begin

  --Define the Task

  vc_task := 'Task 3: By SQL';

  dbms_parallel_execute.create_task(task_name => vc_task);

 

  --Define the Spilt

  vc_sql_mt := 'select distinct object_id, object_id from t';

  dbms_parallel_execute.create_chunks_by_SQL(task_name => vc_task,

                                             sql_stmt  => vc_sql_mt,

                                             by_rowid  => false);

 

  vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';

  --Run the task

  dbms_parallel_execute.run_task(task_name      => vc_task,

                                 sql_stmt       => vc_sql,

                                 language_flag  => dbms_sql.native,

                                 parallel_level => 4);

 

  --Controller

  n_try    := 0;

  n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  while (n_try < 2 and n_status != dbms_parallel_execute.FINISHED) loop

    dbms_parallel_execute.resume_task(task_name => vc_task);

    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  end loop;

 

  --Deal with Result

  dbms_parallel_execute.drop_task(task_name => vc_task);

end;

/




 Oracle中如何更新一张大表记录 

 

SQL语句是一种方便的语言,同样也是一种“迷惑性”的语言。这个主要体现在它的集合操作特性上。无论数据表数据量是1条,还是1亿条,更新的语句都是完全相同。但是,实际执行结果(或者能否出现结果)却是有很大的差异。

笔者在开发DBA领域的一个理念是:作为开发人员,对数据库、对数据要有敬畏之心,一个语句发出之前,起码要考虑两个问题:目标数据表的总数据量是多少(投产之后)?你这个操作会涉及到多大的数据量?不同的回答,处理的方案其实是不同的。

更新大表数据,是我们在开发和运维,特别是在数据迁移领域经常遇到的一种场景。上面两个问题的回答是:目标数据表整体就很大,而且更新范围也很大。一个SQL从理论上可以处理。但是在实际中,这种方案会有很多问题。

本篇主要介绍几种常见的大表处理策略,并且分析出他们的优劣。作为我们开发人员和DBA,选取的标准也是灵活的:根据你的操作类型(运维操作还是系统日常作业)、程序运行环境(硬件环境是否支持并行)和程序设计环境(是否可以完全独占所有资源)来综合考量决定。

首先,我们需要准备出一张大表。

 

1、环境准备

 

我们选择Oracle 11.2版本进行试验。

 

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE     11.2.0.1.0     Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

准备一张大表。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> insert into t select * from t;

72797 rows inserted

 

SQL> insert into t select * from t;

145594 rows inserted

 

(篇幅原因,中间过程略……

SQL> commit;

Commit complete

 

 

SQL> select bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';

 

BYTES/1024/1024/1024

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

 1.0673828125

 

SQL> select count(*) from t;

 

COUNT(*)

----------

 9318016

 

Executed in 14.711 seconds

 

 

数据表T作为数据来源,一共包括9百多万条记录,合计空间1G左右。笔者实验环境是在虚拟机上,一颗虚拟CPU,所以后面进行并行Parallel操作的方案就是示意性质,不具有代表性。

下面我们来看最简单的一种方法,直接update

 

2、方法1:直接Update

 

最简单,也是最容易出问题的方法,就是“不管三七二十一”,直接update数据表。即使很多老程序员和DBA,也总是选择出这样的策略方法。其实,即使结果能出来,也有很大的侥幸成分在其中。

我们首先看笔者的实验,之后讨论其中的原因。先创建一张实验数据表t_target

 

 

SQL> create table t_targettablespace users as select * from t;

Table created

 

 

SQL> update t_target set owner=to_char(length(owner));

(长时间等待……

 

 

在等待期间,笔者发现如下几个现象:

ü  数据库服务器运行速度奇慢,很多连接操作速度减缓,一段时间甚至无法登陆;

ü  后台会话等待时间集中在数据读取、log space buffer、空间分配等事件上;

ü  长期等待,操作系统层面开始出现异常。Undo表空间膨胀;

ü  日志切换频繁;

此外,选择这样策略的朋友还可能遇到:前台错误抛出异常、客户端连接被断开等等现象。

笔者遇到这样的场景也是比较纠结,首先,长时间等待(甚至一夜)可能最终没有任何结果。最要命的是也不敢轻易的撤销操作,因为Oracle要进行update操作的回滚动作。一个小时之后,笔者放弃。

 

 

updatet_target set owner=to_char(length(owner))

ORA-01013: 用户请求取消当前的操作

(接近一小时未完成)

 

 

之后就是相同时间的rollback等待,通常是事务执行过多长时间,回滚进行多长时间。期间,可以通过x$ktuxe后台内部表来观察、测算回滚速度。这个过程中,我们只有“乖乖等待”。

 

 

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

 

  KTUXESIZ

----------

     62877

……

 

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

 

  KTUXESIZ

----------

       511

 

 

综合这种策略的结果通常是:同业抱怨(影响了他们的作业执行)、提心吊胆(不知道执行到哪里了)、资源耗尽(CPU、内存或者IO占到满)、劳而无功(最后还是被rollback)。如果是正式投产环境,还要承担影响业务生产的责任。

我们详细分析一下这种策略的问题:

首先,我们需要承认这种方式的优点,就是简单和片面的高效。相对于在本文中其他介绍的方法,这种方式代码量是最少的。而且,这种方法一次性的将所有的任务提交给数据库SQL引擎,可以最大程度的发挥系统一个方面(CPUIO或者内存)的能力。

如果我们的数据表比较小,经验值在几万一下,这种方法是比较合适的。我们可以考虑使用。

另一方面,我们要看到Oracle Update的另一个方面,就是UndoRedo和进程工作负载的问题。熟悉Oracle的朋友们知道,在DML操作的时候,UndoRedo是非常重要的方面。当我们在UpdateDelete数据的时候,数据块被修改之前的“前镜像”就会保存在Undo Tablespace里面。注意:Undo Tablespace是一种特殊的表空间,需要保存在磁盘上。Undo的存在主要是为了支持数据库其他会话的“一致读”操作。只要事务没有被commit或者rollbackUndo数据就会一直保留在数据库中,而且不能被“覆盖”。

Redo记录了进行DML操作的“后镜像”,Redo生成是和我们修改的数据量相关。现实问题要修改多少条记录,生成的Redo总量是不变的,除非我们尝试nologging选项。Redo单个日志成员如果比较小,Oracle应用生成Redo速度比较大。Redo Group切换频度高,系统中就面临着大量的日志切换或者Log Space Buffer相关的等待事件。

如果我们选择第一种方法,Undo表空间就是一个很大的瓶颈。大量的前镜像数据保存在Undo表空间中不能释放,继而不断的引起Undo文件膨胀。如果Undo文件不允许膨胀(autoextend=no),Oracle DML操作会在一定时候报错。即使允许进行膨胀,也会伴随大量的数据文件DBWR写入动作。这也就是我们在进行大量update的时候,在event等待事件中能看到很多的DBWR写入。因为,这些写入中,不一定都是更新你的数据表,里面很多都是Undo表空间写入。

同时,长时间的等待操作,触动OracleOS的负载上限,很多奇怪的事情也可能出现。比如进程僵死、连接被断开。

这种方式最大的问题在于rollback动作。如果我们在长时间的事务过程中,发生一些异常报错,通常是由于数据异常,整个数据需要回滚。回滚是Oracle自我保护,维持事务完整性的工具。当一个长期DML update动作发生,中断的时候,Oracle就会进入自我的rollback阶段,直至最后完成。这个过程中,系统是比较运行缓慢的。即使重启服务器,rollback过程也会完成。

所以,这种方法在处理大表的时候,一定要慎用!!起码要评估一下风险。

 

3、方法2PL/SQL匿名块

 

上面方法1的最大问题在于“一次性瞬间压力”大。无论是Undo量、还是Rollback量,都是有很大的问题。即使我们的系统能够支持这样的操作,如果update过程中存在其他的作业,必然受到影响。

PL/SQL匿名块的原则在于平稳负载,分批的进行处理。这个过程需要使用bulk collect批量操作,进行游标操作。

我们首先还原实验环境。

 

 

SQL> truncate table t_target;

Table truncated

 

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

 

SQL> commit;

Commit complete

 

 

代码片段。

 

 

SQL> set timing on;

SQL> declare

  2    type rowid_list is table of urowid index by binary_integer;

  3    rowid_infosrowid_list;

  4    i number;

  5    cursor c_rowids is select rowid from t_target;

6  begin

  7    open c_rowids;

  8 

  9    loop

 10      fetch c_rowidsbulk collect into rowid_infos limit 2000;

 11 

 12      foralli in 1..rowid_infos.count

 13         update t_target set owner=to_char(length(owner)+1)

 14             where rowid=rowid_infos(i);

 15 

 16      commit;

 17      exit when rowid_infos.count<2000;

 18    end loop;

 19    close c_rowids;

20  end;

21  /

 

PL/SQL procedure successfully completed

 

Executed in 977.081 seconds

 

 

我们在977s完成了整个操作。这个方法有几个特点:首先是批量的获取bulk collect指定数量更新批量。第二个是使用forall的方法批量更新,减少引擎之间的切换。第三是更新一批之后,commit操作。

这样的好处在于平稳化undo使用。如果数据量少,这种代码方法可能比直接update要慢。但是如果数据量大,特别是海量数据情况下,这种方法是可以支持非常大的数据表更新的。

代码中update操作,使用rowid,如果有其他业务方面的主键也可以使用替换。

在编程实践中,有时候我们可能不能使用PL/SQL代码片段,只能使用SQL语句。这种时候就需要结合业务方面有没有特点可以使用?这种时候往往也就考验开发人员对业务特点的理解了。

在使用forall的时候,要注意一批更新的数量。根据一些Oracle文献透露,内部SQL引擎在update的时候,也是分批进行的,每批大约1000条记录。经验告诉我们每批数量在1-5万比较合适。

这是第二种方法,下面我们介绍一种简单、可行的手段,比较方便。

 

4、方法3insert append方法

 

UndoRedo的角度看,我们更喜欢insert,特别是生成较少redonologgingappend插入。UpdateDelete操作,都会生成Undo记录,在我们看来,都是可以想办法减少的方法。

这种方法的思路是:利用insert,将原来的数据表插入到一个新建立的数据表。在insert过程中,整理column的取值,起到update相同的效果。

下面是实验过程。我们先创建数据表,注意可以设置nologging属性

 

 

SQL> create tablet_renamenologging as select * from t_target where 1=0;

Table created

 

Executed in 0.889 seconds

 

 

在这个过程中,我们创建的是一个空表。之后就可以插入数据,这种方法比较麻烦的地方,就是需要在insert脚本中列出所有的数据列。当然,借用一些工具技巧,这个过程也可以很简单。

 

 

SQL> insert /*+append*/into t_rename

2  selectto_char(length(owner)) owner,

3  OBJECT_NAME,

4  SUBOBJECT_NAME,

5  OBJECT_ID,

6  DATA_OBJECT_ID,

7  OBJECT_TYPE,

8  CREATED,

9  LAST_DDL_TIME,

10  TIMESTAMP,

11  STATUS,

12  TEMPORARY,

13  GENERATED,

14  SECONDARY,

15  NAMESPACE,

16  EDITION_NAME from t_target;

 

9318016 rows inserted

 

Executed in 300.333 seconds

 

 

使用append操作,可以减少redo log的生成。从结果看,一共执行了300s左右,效率应该是比较好的。

之后,提交事务。将原来的数据表删除,将新数据表rename成原有数据表名称。

 

 

SQL> commit;

Commit complete

 

Executed in 0.031 seconds

 

SQL> drop table t_target purge;

Table dropped

 

Executed in 1.467 seconds

 

SQL> rename t_rename to t_target;

Table renamed

 

Executed in 0.499 seconds

 

SQL> select count(*) from t_target;

COUNT(*)

----------

   9318016

 

Executed in 14.336 seconds

 

 

最后,可以将nologging属性修改回来,将数据表约束添加上。

 

 

SQL> alter table t_target logging;

Table altered

 

Executed in 0.015 seconds

 

 

这种方法的好处在于效率,在数据量维持中高的情况下,这种方法速度是比较吸引人的。但是,这种方式也要消耗更多的存储空间。在存储空间允许的情况下,可以用这种方法。

如果数据量更大,达到海量的程度,比如几十G的数据表,这种方法就值得考量一下了。需要结合硬件环境和运行环境完成。另外,这种方法涉及到数据表的创建等运维工作特性,故不适合在应用程序中使用,适合在运维人员过程中使用。

还有,就是这种方法的实际对备份的影响。由于我们使用了nologging+append选项,生成的redo log数量是不足以进行还原的,所以如果要实现完全恢复的话,数据库实际上是失去了连续还原的依据。因此,如果真正使用了这个方法在生产环境下,之后需要进行数据库全备份操作。

如果数据库版本为11.2以上,我们可以使用Oracle的一个新特性dbms_parallel_execute包,进行数据表并行更新。详见下面介绍。

 

5、方法3dbms_paralle_execute并行包使用

 

其他优化手段都用上的时候,并行是可以尝试的方法。并行parallel就是利用多个process同时进行处理,从而提高处理效率的方法。Parallel的使用有一些前提,也有一些不良反应。并行的前提是硬件支持,并行技术本身要消耗很多的资源,相当于是将服务器资源“榨干”来提速。在规划并行策略的时候,首先要看硬件资源是不是支持,单核CPU情况下,也就不需要使用这个技术了。

使用并行之后,必然对其他正在运行程序、作业有影响。所以,笔者的经验是:一般应用不要考虑并行的事情,如果发现特定场景存在并行的需要,可以联系DBA或者运维人员确定可控的技术方案。

11.2之前,使用并行稍微复杂一些,很多朋友在使用的时候经常是“有名无实”,看似设置了并行,但是实际还是单进程运行。11.2之后,Oracle提供了新的并行操作接口dbms_parallel_execute,这让并行更加简单。

说明:本篇不是专门介绍dbms_parallel_execute接口,只作为介绍。详细内容参见笔者专门介绍这个接口的文章。

dbms_parallel_execute工作采用作业task方式,后台执行。首先是按照特定的原则进行数据分割,将工作数据集合分割为若干chunk。之后启动多个后台job进行工作。在划分工作集合的问题上,Oracle提供了三种方法,rowidcolumn_valueSQL,分别按照rowid、列值和特定SQL语句进行分割。

注意:使用dbms_parallel_execute接口包有一个前提,就是job_queue_process参数必须设置非空。如果为0,则我们的进程执行之后被阻塞挂起。

恢复数据环境。

 

 

SQL> create table t_targettablespace users as select * from t where 1=0;

Table created

 

Executed in 0.078 seconds

 

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

 

Executed in 64.974 seconds

 

SQL> commit;

Commit complete

 

Executed in 0.109 seconds

 

 

参数环境。

 

 

SQL> show parameter job_queue

 

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000

 

 

在这个任务中,我们选择使用create_chunks_by_rowid方法。这种方法通用型强,执行计划稳定性好。

 

 

SQL> set serveroutput on;

SQL> declare

  2    vc_sqlvarchar2(1000);

  3    n_try number;

  4    n_status number;

5  begin

  6    --create a task

  7    dbms_parallel_execute.create_task(task_name => 'Huge_Update');

  8 

  9    --By Rowid

 10    dbms_parallel_execute.create_chunks_by_rowid(task_name => 'Huge_Update',

 11     table_owner => 'SYS',table_name => 'T_TARGET',by_row =>true,chunk_size => 10000);

 12 

 13    vc_sql := 'update /*+rowid(dda)*/t_target set owner=to_char(length(owner)) where rowid between :start_id and :end_id';

 14 

 15    dbms_parallel_execute.run_task(task_name => 'Huge_Update',sql_stmt =>vc_sql,language_flag =>dbms_sql.native,parallel_level => 3);

 16  --防止失败后重启

 17    n_try := 0;

 18    n_status := dbms_parallel_execute.task_status('Huge_Update');

 19    while (n_try<2 and (n_status != dbms_parallel_execute.FINISHED)) loop

 20       n_try := n_try + 1;

 21       dbms_parallel_execute.resume_task('Huge_Update');

 22       n_status := dbms_parallel_execute.task_status('Huge_Update');

 23    end loop;

 24 

 25    dbms_output.put_line(''||n_try);

 26    dbms_parallel_execute.drop_task('Huge_Update');

27  end;

28  /

 

0

 

PL/SQL procedure successfully completed

 

Executed in 1177.106 seconds

 

 

在代码中,需要注意start_idend_id两个绑定变量。这两个范围值是接口固定的。这种方法使用了1177s来完成工作。

在执行过程中,我们也有很多方法来监督执行过程。Oracle提供了两个视图,关于parallel_execute接口的。Dba_parallel_execute_tasks表示了提交的任务,里面我们可以看到状态信息。

 

 

SQL> col task_name for a15;

SQL> select task_name, status from dba_parallel_execute_tasks;

 

TASK_NAME           STATUS

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

Huge_Update         PROCESSING

 

 

SQL> select task_name, JOB_PREFIX from dba_parallel_execute_tasks;

 

TASK_NAME            JOB_PREFIX

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

Huge_Update          TASK$_655

 

 

执行中,我们从v$session中可能看到后台的进程会话。

 

 

SQL> select sid, serial#, status, PROGRAM, SQL_ID, event from v$session where action like 'TASK$_655%';

 

       SID    SERIAL# STATUS   PROGRAM                                          SQL_ID        EVENT

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

        35        507 ACTIVE   oracle@bspdev.localdomain (J003)                 d7xw8z3nzh5cg db file scattered read

        38        119 ACTIVE   oracle@bspdev.localdomain (J001)                 d7xw8z3nzh5cg log buffer space

        45       6612 ACTIVE   oracle@bspdev.localdomain (J000)                 d7xw8z3nzh5cg Data file init write

 

 

另一个视图更有用dba_parallel_execute_chunks,其中包括了所有的chunk对象。Parallel Execute执行的原则就是数据的划分,这个视图中,可以看到哪些chunk已经完成,哪些没有完成。

 

 

SQL> select status, count(*) from dba_parallel_execute_chunks group by status;

 

STATUS                 COUNT(*)

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

ASSIGNED                      3

UNASSIGNED                 2315

PROCESSED                   571

 

 

ASSIGNED状态表示多进程状态正在处理,我们设置了三个后台进程执行。UNASSIGNED表示没有完成,正等待处理。PROCESSED表示已经处理完。

这种方法应该是目前找到比较好的方法。缺点就是代码量比较大。优点是处理使用并行,如果物理条件支持,执行效率是很高的。而且,在海量数据表的情况下,这种策略是很值得推荐的。

 

6、结论

 

更新大量的数据,是我们常见的一种问题场景。无论对开发人员,还是运维人员,都有不同的挑战。笔者本篇要强调的是:没有绝对好的策略,都是针对特别的场景和背景,选取最适合的策略。从而更好地完成任务。盲目的执行SQL语句,是一种典型不负责任的行为,需要避免杜绝。




使用11g dbms_parallel_execute执行并行更新(上) 

 

海量数据处理,是很多系统开发人员,有时候甚至是运维人员,经常面对的需求。接口海量数据文件加载入库、批量数据更新、阶段数据归档删除是我们经常遇到的应用需求。针对不同的实际情况,包括软硬件、运维环境、SLA窗口期要求,我们需要采用不同的策略和方法进行问题解决。

在笔者之前文章《Oracle中如何更新一张大表记录》(http://blog.itpub.net/17203031/viewspace-1061065/)中,介绍了以Oracle数据库端为中心,进行大表数据处理过程中的一些方法和考虑因素。简单的说,海量数据处理难点不在语句层面,而在如何平衡各种需求因素。比较常见的因素有如下:

 

ü  业务系统正常生产冲击。大数据操作绝大多数场景是在生产环境。在7*24可用性需求日益强化的今天,业务系统一个SQL运行之后,影响减慢核心操作速度,严重甚至系统崩溃,绝对不是我们运维人员希望见到的;

ü  操作窗口期长短。在相同的业务操作量的情况下,平缓化操作负载一定是以增加操作时间作为前提的。增加延长操作时间是否能够在维护窗口内完成,也是需要考量的问题;

ü  对数据一致性的影响。一些“流言”方法(如nologging),虽然可以减少操作负载,但是潜在会给系统备份连续性带来灾难影响;

 

此外,SQL语句本身优化,操作策略也会有一些可以提高的空间。但是,一些问题还是需要单纯的大量数据处理。当其他常规手段出尽的时候,在硬件条件允许下,并行、并发操作往往是不错的选择。

11gR2中,Oracle为海量数据处理提供了很多方便的支持。工具包dbms_parallel_execute可以支持将海量数据分拆为独立的chunk任务,并行执行作业。本篇就详细介绍这个新特性的使用。

 

1、环境准备

 

实验环境为11.2.0.3

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

 

 

构造一张大表。说明:条件所限,笔者环境比较简单,一些性能方面的优势比较难体现出来。先创建出一个单独表空间。

 

 

SQL> create tablespace test datafile size 2G autoextend on

  2  extent management local uniform size 1m

  3  segment space management auto;

Tablespace created

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> insert into t select * from t;

75586 rows inserted

(一系列的insert操作……

 

SQL> commit;

Commit complete

 

数据表T包括大约2千万条记录,占用空间体积在2G左右。

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

  19350016

 

SQL> select bytes/1024/1024/1024, tablespace_name from dba_segments where owner='SYS' and segment_name='T';

 

BYTES/1024/1024/1024 TABLESPACE_NAME

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

        2.0986328125 TEST

 

Dbms_parallel_execute并不是传统的多进程并行操作,本质上是通过作业管理器Schedule来完成系列作业的(在后文中会详细证明)。所以前提要求job_queue_processes参数设置不能为0

 

 

SQL> show parameter job

 

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     1000

 

2dbms_parallel_execute包执行介绍

 

Dbms_parallel_executeOracle 11g推出的一个全新并行操作接口。它的原理为:当Oracle需要处理一个大量数据处理,特别是update操作的时候,可以将其拆分为若干各chunk分块,以多进程作业(Schedule Job)分块执行操作。从而降低一次性undo的使用,更进一步的便于断点续作。

Dbms_parallel_execute包使用要满足两个条件:

 

ü  执行程序用户需要拥有create job系统权限;

ü  Dbms_parallel_execute程序包执行中需要调用dbms_sql包的一些方法,所以也需要该程序包执行权限;

 

并行包的执行有两个问题需要调用者确定:chunk分割方法和并行作业进程个数。

传统的单线程执行策略中,无论任务多大,都是对应一个Server Process进行处理。如果调用了并行,会有对应的协调进程和工作进程存在(v$px_process)。

如果启用了并行执行,一个关键问题在于如何划分任务,将一个数据表更新操作划分为多个小数据集合操作。Dbms_parallel_execute包支持三种任务划分方法。

 

ü  By_rowid方法:依据rowid将操作数据进行划分;

ü  By_number_col方法:输入定义一个数字列名称,依据这个列的取值进行划分;

ü  By_SQL语句方法:给一个SQL语句,用户可以帮助定义出每次chunk的起始和终止id取值;

 

在三种方法中,笔者比较推荐rowid方法,理由是条件要求低、操作速度快。如果操作过程中没有明确的对数据表作业,这种策略是首选。具体比较可以从下面的实验中看出。

确定了划分方法,还要确定每个chunk的大小。注意:这个chunk设置大小并不一定是每个chunk操作数据行的数量。针对不同的分区类型,有不同的策略。这个在下面实验中笔者也会给出明确的解析。

并行进程个数表示的是当“一块”任务被划分为“一堆”相互独立的任务集合之后,准备多少个工作进程进行工作。这个是并行包使用的关键,类似于并行度,是需要依据实际软硬件资源负载情况综合考虑。

长时间作业存在一个问题,就是调用用户希望随时了解执行情况。Oracle提供了两个数据视图user_parallel_execute_tasksuser_parallel_execute_chunks,分别查看Task执行情况和各个chunk执行完成情况。

Oracle官方文档中,给出了调用dbms_parallel_execute包的方法流程,本文使用的也就是这个脚本的变种,特此说明。下面,我们先看第一种by rowid方法。

 

3By Rowid划分chunk方法

 

Oracle中的rowid是数据实际物理位置的表示。借助rowid直接定位数据,是目前Oracle获取数据最快的方法。所以在RBO中,第一执行计划被确定为rowid访问方式。

依据Oracle文档提供的PL/SQL匿名块,修改处我们第一个rowid范围查询。

 

declare

  vc_task varchar2(100);

  vc_sql varchar2(1000);

  n_try number;

  n_status number;

begin

  --Define the Task

  vc_task := 'Task 1: By Rowid';  --Task名称

  dbms_parallel_execute.create_task(task_name => vc_task); --手工定义一个Task任务;

 

  --Define the Spilt

  dbms_parallel_execute.create_chunks_by_rowid(task_name => vc_task,

                                               table_owner => 'SYS',

                                               table_name => 'T',

                                               by_row => true,

                                               chunk_size => 1000); --定义Chunk

                                              

  vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where rowid between :start_id and :end_id';

  --Run the task

  dbms_parallel_execute.run_task(task_name => vc_task,

                                 sql_stmt => vc_sql,

                                 language_flag => dbms_sql.native,

                                 parallel_level => 2); --执行任务,确定并行度

 

  --Controller

  n_try := 0;

  n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop

     dbms_parallel_execute.resume_task(task_name => vc_task);

     n_status := dbms_parallel_execute.task_status(task_name => vc_task);

  end loop;        

 

  --Deal with Result

  dbms_parallel_execute.drop_task(task_name => vc_task);                                   

end;

/

 

从调用过程来看,这个并行操作包括下面几个步骤:

 

ü  定义Task

ü  确定chunk划分方法,定义每个chunk的范围信息;

ü  执行作业,确定并行作业进程数量;

 

这个调用过程和我们常见的并行方式有很大差异,类似于OracleJob Schedule机制。由于执行过程比较长,我们可以有比较从容的查看并行执行包的情况。

user_parallel_execute_tasks中,看到当前作业的关键信息。注意:chunk_type表示的是采用什么样的划分方法。JOB_PREFIX对应的则是Schedule中的内容。

 

SQL> select task_name, chunk_type, JOB_PREFIX from user_parallel_execute_tasks;

 

TASK_NAME            CHUNK_TYPE   JOB_PREFIX

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

Task 1: By Rowid     ROWID_RANGE  TASK$_4

 

user_parallel_execute_chunks中,作业的所有chunk划分,每个chunk对应的一行数据。其中包括这个chunk的起始和截止rowid。对应的chunk取值对应的就是每个chunk的数据行数

 

SQL> select chunk_id, task_name, status, start_rowid, end_rowid from user_parallel_execute_chunks where rownum<10;

 

  CHUNK_ID TASK_NAME            STATUS               START_ROWID        END_ROWID

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

         1 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAACAAAA AAATLKAAHAAAACxCcP

         2 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAACyAAA AAATLKAAHAAAADjCcP

         3 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAADkAAA AAATLKAAHAAAAD/CcP

         4 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAEAAAA AAATLKAAHAAAAExCcP

         5 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAEyAAA AAATLKAAHAAAAFjCcP

         6 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAFkAAA AAATLKAAHAAAAF/CcP

         7 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAGAAAA AAATLKAAHAAAAGxCcP

         8 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAGyAAA AAATLKAAHAAAAHjCcP

         9 Task 1: By Rowid     PROCESSED            AAATLKAAHAAAAHkAAA AAATLKAAHAAAAH/CcP

 

9 rows selected

 

作为user_parallel_execute_chunks,一个很重要的字段就是status状态列,用于标注每个chunk的处理情况。我们可以依据这个字段来判断任务完成情况。

 

SQL> select status, count(*) from user_parallel_execute_chunks group by status;

 

STATUS                 COUNT(*)

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

ASSIGNED                      2

UNASSIGNED                 5507

PROCESSED                   938

 

(过一会之后…….

SQL> select status, count(*) from user_parallel_execute_chunks group by status;

 

STATUS                 COUNT(*)

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

ASSIGNED                      2

UNASSIGNED                 5441

PROCESSED                  1004

 

status字段,我们可以分析出并行作业工作的原理。每一个chunk记录在划分之后,都是设置为unassiged状态,包括起始和终止的id信息(rowid或者column_range)。每次处理的chunkassigned状态,实验程序中我们设置parallel_level2,所以每次都是2chunkassigned状态。处理结束之后,设置为processed状态。

海量数据更新最大的问题在于undo拓展的量,我们检查一下执行过程中的undo size情况。

 

 

SQL> select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

 

SUM(BYTES)/1024/1024

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

 

SQL> select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

 

SUM(BYTES)/1024/1024

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

                  16

 

SQL> select sum(bytes)/1024/1024 from dba_undo_extents where status='ACTIVE';

 

SUM(BYTES)/1024/1024

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

                  10

 

每次的数据量都不大,说明每次都是一小块chunk的操作。也确定使用parallel执行的过程,是分步小块commit的过程。在job视图中,我们也可以明确的看出作为作业的信息。

 

SQL> select owner, job_name, JOB_ACTION, SCHEDULE_TYPE, state, last_start_date from dba_scheduler_jobs where job_name like 'TASK$_4%';

 

OWNER   JOB_NAME  JOB_ACTION                                 SCHEDULE_TYPE STATE           LAST_START_DATE

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

SYS     TASK$_4_2  DBMS_PARALLEL_EXECUTE.RUN_INTERNAL_WORKER  IMMEDIATE     RUNNING         10-2 -14 01.48.34.947417 下午 PRC

SYS     TASK$_4_1  DBMS_PARALLEL_EXECUTE.RUN_INTERNAL_WORKER  IMMEDIATE     RUNNING         10-2 -14 01.48.34.730487 下午 PRC

 

注意:传统的并行进程v$px_process中没有看到数据信息,说明并行程序包并不是Oracle传统的数据库并行方案。

 

SQL> select * from v$px_process;

 

SERVER_NAME STATUS           PID SPID                            SID    SERIAL#

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

 

执行结束信息:

 

25    --Controller

 26    n_try := 0;

 27    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

 28    while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop

 29       dbms_parallel_execute.resume_task(task_name => vc_task);

 30       n_status := dbms_parallel_execute.task_status(task_name => vc_task);

 31    end loop;

 32 

 33    --Deal with Result

 34    dbms_parallel_execute.drop_task(task_name => vc_task);

 35  end;

 36  /

 

PL/SQL procedure successfully completed

 

Executed in 944.453 seconds

 

更新2G数据一共使用945s,合计约16分钟。

从上面的数据视图和调用过程,我们可以得到如下结论:

dbms_parallel_execute执行包而言,通过确定chunk方法和chunk size,可以将一个很大的数据集合划分为若干各小chunk集合,分步进行操作处理。代码中设置的parallel_level,体现在设置Job的个数上。启动作业任务后,Oracle并不是启动传统的并行机制,而是在Job Schedule的基础上创建parallel_level个数的作业,类型为立即执行。多个作业分别执行各个chunk的小块工作。使用Job Schedule的一个好处在于可以方便的进行作业resumestart过程。

下面我们讨论by number colby SQL两种执行方法。





 

上篇我们讨论了dbms_parallel_execute的工作方法、使用流程和特点。本篇继续来讨论其他两种划分Chunk方式。说明:对每种划分策略执行过程中,笔者都进行了不同的实验,来说明其工作特点。

 

4By Number Col划分Chunk方法

 

应该说,使用rowid进行数据表划分可以带来很多好处。每个chunk数据获取过程,本质上就是执行一个范围Range操作。对于rowid而言,直接通过范围检索的效率是相当高的。

Rowid方法对应两种策略都是依据“数据表列范围”进行chunk划分。By Number Col的方法顾名思义,需要我们指定出一个数字类型列名称。Oracle会依据这个列取值进行划分。每个chunk实际上都是通过数字类型检索到的结果集合进行处理。

当然,这个过程必然伴随着我们对于“地势”条件的依赖。每次从上千万条记录中,FTS的检索出一个chunk数据显然是很费力的操作过程。最直接的优化手段就是索引和分区。注意:如果我们没有特殊的条件进行chunk划分辅助,一定要考虑by number col方式是否适合。

 

SQL> create index idx_t_id on t(object_id);

Index created

Executed in 107.282 seconds

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,degree => 2);

PL/SQL procedure successfully completed

Executed in 87.453 seconds

 

修改的脚本如下:

 

SQL> declare

  2    vc_task varchar2(100);

  3    vc_sql varchar2(1000);

  4    n_try number;

  5    n_status number;

  6  begin

  7    --Define the Task

  8    vc_task := 'Task 2: By Number Col';

  9    dbms_parallel_execute.create_task(task_name => vc_task);

 10 

 11    --Define the Spilt

 12    dbms_parallel_execute.create_chunks_by_number_col(task_name => vc_task,

 13                                                      table_owner => 'SYS',

 14                                                      table_name => 'T',

 15                                                      table_column => 'OBJECT_ID',

 16                                                      chunk_size => 1000); --定义chunk

 17 

 18    vc_sql := 'update /*+ ROWID(dda) */t set DATA_OBJECT_ID=object_id+1 where object_id between :start_id and :end_id';

 19    --Run the task

 20    dbms_parallel_execute.run_task(task_name => vc_task,

 21                                   sql_stmt => vc_sql,

 22                                   language_flag => dbms_sql.native,

 23                                   parallel_level => 1);

 24 

 25    --Controller

 26    n_try := 0;

 27    n_status := dbms_parallel_execute.task_status(task_name => vc_task);

 28    while (n_try<2 and n_status != dbms_parallel_execute.FINISHED) loop

 29       dbms_parallel_execute.resume_task(task_name => vc_task);

 30       n_status := dbms_parallel_execute.task_status(task_name => vc_task);

 31    end loop;

 32 

 33    --Deal with Result

 34    dbms_parallel_execute.drop_task(task_name => vc_task);

 35  end;

 36  /

 

从执行流程上看,上面脚本和by rowid方式没有显著地差异。最大的区别在于定义chunk时调用的方法,参数包括指定的数据表、列名和chunk size注意:我们这里定义了chunk size1000,但是在执行过程中,我们不能保证每个chunk的大小是1000。这个结论我们在后面的阐述实验中可以证明。

执行脚本的速度显著的比by rowid的慢了很多。但是我们也能发现很多技术细节。首先,我们会有一个时期,在chunk视图中没有结果返回。

 

SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;

 

TASK_NAME                 CHUNK_TYPE   STATUS

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

Task 2: By Number Col     NUMBER_RANGE CHUNKING

Executed in 0.61 seconds

 

SQL> select status, count(*) from user_parallel_execute_chunks group by status;

 

STATUS                 COUNT(*)

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

 

在之后,我们才能查看到chunk处理情况。

 

 

SQL> select task_name, chunk_type, status from user_parallel_execute_tasks;

 

TASK_NAME                 CHUNK_TYPE   STATUS

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

Task 2: By Number Col     NUMBER_RANGE PROCESSING

 

SQL> select status, count(*) from user_parallel_execute_chunks group by status;

 

STATUS                 COUNT(*)

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

ASSIGNED                      1

UNASSIGNED                 1557

PROCESSED                    13

 

这个现象说明:对dbms_parallel_execute包处理过程来说,包括两个重要的步骤,Chunk分块步骤和Chunk处理步骤。无论是哪种分块方法,Oracle都是首先依据分割原则,将任务拆分开来,规划在任务视图里面。之后再进行分作业JobProcessing处理过程。

by rowid方式中的rowid Range信息一样,我们在chunk视图中也是可以看到数字列范围的信息。

 

SQL> select task_name, status, start_id, end_id, job_name from user_parallel_execute_chunks where rownum<5;

 

TASK_NAME                 STATUS                 START_ID     END_ID JOB_NAME

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

Task 2: By Number Col     PROCESSED                 25002      26001 TASK$_5_2

Task 2: By Number Col     ASSIGNED                  26002      27001 TASK$_5_1

Task 2: By Number Col     ASSIGNED                  27002      28001 TASK$_5_2

Task 2: By Number Col     UNASSIGNED                28002      29001

 

注意:我们此处看到的chunk范围是1000,由于数据准备过程,范围1000绝对不意味着每个chunk的大小是1000。所以,我们也就可以推断出,调用方法中的chunk sizenumber col方式中,是取值范围的大小。

直观的想,Oracle选取这样的策略也是有依据的:Oracle可以直接选取一个最小和最大的数据列值,依次chunk取值范围进行分割。这样做可减少对数据检索的压力。 <