使用DBMS_PARALLEL_EXECUTE进行快速更新

最新推荐文章于 2022-07-20 22:07:51 发布
最新推荐文章于 2022-07-20 22:07:51 发布
阅读量158 收藏
点赞数
文章标签： 数据库
问题:
如何在尽量短的时间内完成大批量数据的更新？如漂白数据时，需要将客户表（约一亿行）中的手机号设置为常量。
比如：投产时数据迁移

可以考虑的方法：
1，并行dml
 一个事务，需要较大的回滚表空间
 进度难以预测
 并行dml bug较多
SQL> alter session enable parallel dml;
 
Session altered
 
Executed in 0.047 seconds
 
SQL> update /*+ parallel(e 20) */ CUST e SET e.mobile = '123';
 
107007800 rows updated
 
Executed in 183.285 seconds
 
SQL> commit;
 
Commit complete
 
Executed in 5.179 seconds
 
SQL> alter session disable parallel dml;
 
Session altered
 
Executed in 0.063 seconds
 
       SID TYPE        ID1      LMODE    REQUEST      BLOCK
---------- ---- ---------- ---------- ---------- ----------
       109 TM        99947          1          0          0
       109 TM        99947          4          0          0
       154 TM        99947          1          0          0
       154 TM        99947          4          0          0
       166 TM        99947          6          0          1  --发起并行更新的会话
       169 TM        99947          0          3          0  --对该表dml操作，被阻塞
       181 TM        99947          1          0          0
       181 TM        99947          4          0          0
       184 TM        99947          1          0          0
       184 TM        99947          4          0          0
       187 TM        99947          1          0          0
       187 TM        99947          4          0          0
       190 TM        99947          1          0          0
       190 TM        99947          4          0          0
       193 TM        99947          1          0          0
       193 TM        99947          4          0          0
       196 TM        99947          1          0          0
       196 TM        99947          4          0          0
       199 TM        99947          1          0          0
       199 TM        99947          4          0          0
       202 TM        99947          1          0          0
       202 TM        99947          4          0          0
       205 TM        99947          1          0          0
       205 TM        99947          4          0          0
       208 TM        99947          1          0          0
       208 TM        99947          4          0          0
       211 TM        99947          1          0          0
       211 TM        99947          4          0          0
       214 TM        99947          1          0          0
       214 TM        99947          4          0          0
       217 TM        99947          1          0          0
       217 TM        99947          4          0          0
       220 TM        99947          1          0          0
       220 TM        99947          4          0          0
       223 TM        99947          1          0          0
       223 TM        99947          4          0          0
       226 TM        99947          1          0          0
       226 TM        99947          4          0          0
       229 TM        99947          1          0          0
       229 TM        99947          4          0          0
       232 TM        99947          1          0          0
       232 TM        99947          4          0          0
 
42 rows selected

2，应用并行
  灵活
  步骤复杂
  主要步骤：
  *分片（chunk）
  *多个分片并行更新


版本:
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE    11.2.0.3.0    Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

软硬件环境:
SQL> select stat_name, value
  2    from v$osstat
  3   where stat_name in ('NUM_CPU_CORES', 'NUM_CPUS', 'PHYSICAL_MEMORY_BYTES');
 
STAT_NAME                                                             VALUE
---------------------------------------------------------------- ----------
NUM_CPUS                                                                 96
NUM_CPU_CORES                                                            24
PHYSICAL_MEMORY_BYTES                                            7730941132

SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 22144M
sga_target                           big integer 22144M
 
SQL> show parameter pga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 7372M

SQL> show parameter cluster
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects 

 
SQL> show parameter undo
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL>
SQL> select bytes/1e9,autoextensiable from dba_data_files where tablespace_name='UNDOTBS1';


 BYTES/1E9   AUTOEXT
--------------------
8.55113728   YES


测试表:
SQL> select bytes/1e9 from user_segments where segment_name='CUST';
 
 BYTES/1E9
----------
14.4388915

SQL> select /*+ parallel(t 8) */ count(*) from cust t;
 
  COUNT(*)
----------
 107007800
 

分片并行更新:
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', 'BOCNET', 'CUST', false, 1000);
 
  -- Execute the DML in parallel
  l_sql_stmt := 'update /*+ ROWID (e) */ CUST e
      SET e.mobile = ''123''
      WHERE rowid BETWEEN :start_id AND :end_id';
  DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
                                 parallel_level => 20);
 
  -- 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;
/

==>
PL/SQL procedure successfully completed
 
Executed in 391.235 seconds


在执行期间,在第二个会话中执行:
SQL>
SQL> select event,count(*) from v$session where wait_class<>'Idle'
  2  group by event;
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read                                                    9
log file parallel write                                                   1
db file parallel write                                                   10
control file sequential read                                              1
log buffer space                                                         11
 
SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read                                                   1
db file scattered read                                                    4
latch: redo allocation                                                   15
LGWR wait for redo copy                                                   1
latch: object queue header operation                                      1
 
SQL> set time on
10:58:55 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
db file scattered read                                                    3
latch: redo allocation                                                   17
latch free                                                                1
 
10:58:57 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
log file switch completion                                               19
log file parallel write                                                   1
db file parallel write                                                    3
log buffer space                                                          1
 
 
11:00:19 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
latch: undo global data                                                   1
db file scattered read                                                    3
enq: HW - contention                                                      3
Disk file operations I/O                                                  1
enq: US - contention                                                      4
latch: redo allocation                                                    1
log file parallel write                                                   1
db file parallel write                                                    1
control file sequential read                                              1
buffer busy waits                                                         6
 
10 rows selected
 
11:01:39 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
latch: cache buffers lru chain                                            4
db file scattered read                                                    8
latch: redo allocation                                                    6
Disk file operations I/O                                                  2
LGWR wait for redo copy                                                   1
 
11:02:13 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
ASM file metadata operation                                               1
db file scattered read                                                    6
enq: US - contention                                                      2
enq: HW - contention                                                      2
log file parallel write                                                   1
buffer busy waits                                                         9
 
6 rows selected
 
11:02:57 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
enq: HW - contention                                                      1
log file parallel write                                                   1
undo segment extension                                                    2
log file switch (checkpoint incomplete)                                  10
buffer busy waits                                                         7
 
11:03:19 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read                                                   1
db file scattered read                                                    2
enq: US - contention                                                      5
enq: HW - contention                                                      1
log file parallel write                                                   1
db file parallel write                                                    2
control file sequential read                                              1
undo segment extension                                                    1
buffer busy waits                                                         9
 
9 rows selected
 
11:03:46 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
db file sequential read                                                   1
db file scattered read                                                    5
enq: US - contention                                                      6
latch: redo allocation                                                    2
enq: HW - contention                                                      1
log file parallel write                                                   1
Data file init write                                                      1
buffer busy waits                                                         4
 
8 rows selected
 
11:04:42 SQL> r
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
 

 
在执行期间,在第三个会话中执行:
SQL> select sum(used_ublk) from v$transaction;
 
SUM(USED_UBLK)
--------------
          7938
 
SQL> r
 
SUM(USED_UBLK)
--------------
          6875
 
SQL> set time on
10:59:03 SQL> r
 
SUM(USED_UBLK)
--------------
          8027
 
11:00:15 SQL> r
 
SUM(USED_UBLK)
--------------
          8413
 
11:00:46 SQL> r
 
SUM(USED_UBLK)
--------------
         13125
 
11:00:50 SQL> select count(*),sum(used_ublk) from v$transaction;
 
  COUNT(*) SUM(USED_UBLK)
---------- --------------
        20          10701
 
11:01:35 SQL> r
 
  COUNT(*) SUM(USED_UBLK)
---------- --------------
        20           9650
 
11:03:15 SQL> r
 
  COUNT(*) SUM(USED_UBLK)
---------- --------------
        21           5391
 
11:03:42 SQL> r
 
  COUNT(*) SUM(USED_UBLK)
---------- --------------
        20           6826
 
11:04:37 SQL> r
 
  COUNT(*) SUM(USED_UBLK)
---------- --------------
         0
 
在执行期间,在第四个会话中执行:

 SQL> select status,count(*) from user_parallel_execute_chunks where task_name='mytask' group by status;
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     19
UNASSIGNED                 1635
PROCESSED                   515
 
SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     20
UNASSIGNED                 1475
PROCESSED                   674
 
SQL> set time on
10:59:10 SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     20
UNASSIGNED                 1031
PROCESSED                  1118
 
10:59:47 SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     20
UNASSIGNED                  825
PROCESSED                  1324
 
11:00:10 SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     20
UNASSIGNED                  796
PROCESSED                  1353
 
11:03:12 SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     20
UNASSIGNED                  279
PROCESSED                  1870
 
11:03:32 SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     18
UNASSIGNED                  219
PROCESSED                  1932
 
STATUS                 COUNT(*)
-------------------- ----------
ASSIGNED                     20
UNASSIGNED                   61
PROCESSED                  2088
 
11:04:52 SQL> r
 
STATUS                 COUNT(*)
-------------------- ----------
 
11:06:01 SQL> 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-762381/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-762381/

cuian7084
关注
oracle并行parallel update两张表_【oracle】使用DBMS_PARALLEL_EXECUTE并行更新表
weixin_42165508的博客
01-14 660
在11.2 版本中使用DBMS_PARALLEL_EXECUTE包批量并行递增式的更新表。1 把数据集分割成小的块2 在每一个块上以并行的方式应用update语句，在每个块执行完成后，提交！此更新技术有如下好处：1 在执行update的时候，仅仅锁住一个shunk而非锁住整个表！2 因为每个chunk 执行完毕就提交，所以当update操作失败后，之前变更的并不会回滚！3 减小回滚空间的使用4 提...
oracle ＞=11g 并发执行更新 DBMS_PARALLEL_EXECUTE
marylgao技术专栏
03-26 1919
oracle 11g 及更高级版本，利用数据内部并发机制执行update，大大提高执行效率 1.创建task: exec DBMS_PARALLEL_EXECUTE.create_task (task_name => ‘CUST_BIG_2KW_1_task’); 2.查询创建task select * from user_parallel_execute_tasks; select task_name,status from user_parallel_execute_tasks where t
使用 DBMS_PARALLEL_EXECUTE 并行(Parallel)更新(Update)大表(Large Tables) (Doc ID 1066555.1)
u010692693的专栏
11-13 700
11.2 New Feature : Using DBMS_PARALLEL_EXECUTE to Update Large Tables in Parallel (Doc ID 1066555.1) PURPOSE 目的 The purpose of this article is to introduce the 11.2.0.x database new package DBMS...
ORACLE 11G R2中的并行执行，dbms_parallel_execute
陈志明的专栏
01-14 6361
在以前我们处理大数据量的时候，一般机器硬件都足够强悍，单线程的处理怎么也消耗不了多少的系统资源，而整个处理的过程又比较慢，这个时候并行就出现了。并行可以通过ORACLE的PARALLEL来指定和执行，而在11G R2版本中，这个功能得到了加强，ORACLE推出了一个dbms_parallel_execute包，专门来处理这些内容。这个包其实原理也比较简单：首先是把数据分成一个个CHUNK，然后指定
使用 DBMS_PARALLEL_EXECUTE 更新大表
不吃草的牛德
08-22 1047
Oracle 11g 中新功能 DBMS_PARALLEL_EXECUTE 包可以使你在并行增量更新一个大表的数据，两个高层次的步骤是： Group sets of rows in the table into smaller chunks. Apply the desired UPDATE statement to the chunks in parallel, committing
11g包dbms_parallel_execute在海量数据处理过程中的应用
weixin_34116110的博客
06-03 241
11g包dbms_parallel_execute在海量数据处理过程中的应用 一.1  BLOG文档结构图       一.2  前言部分   一.2.1  导读 各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~： ① 11g包dbms_parallel_execute在海量数据处理过程中的应用   注意：本篇BLOG中代码部分需要特别关注的...
DBMS_DATAPUMP示例
彦祖的小号的博客
07-21 892
演示环境 scott@CNMMBO> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production 1、导出schema(schema模式) DECLARE ...
Oracle 11g系统调优之dbms_sqltune包的使用
cining2000的博客
03-18 740
前沿：随着数据库版本的提升，Oracle也提供了越来越多的性能诊断工具，针对SQL的调优，DBMS_SQLTUNE就是其中一个比较优秀的包。 DBMS_SQLTUNE最开始是在10G里面出现，11G里面则对其进行了加强，...
28 Oracle深度学习笔记——ORACLE自带DBMS函数包
qq_43667669的博客
01-16 5429
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28.Oracle深度学习笔记——ORACLE自带DBMS函数包欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/5083088912C 包含
ORACLE 11g自带DBMS函数包
HXC_HUANG的博客
06-03 2189
select object_name,status,object_type from dba_objects where object_name like ‘%DBMS%’ and object_type=’PACKAGE’;1 DBMS_STANDARD VALID PACKAGE 2 DBMS_REGISTRY VALID PACKAGE 3 DBMS_REGIS
DBMS_PARALLEL_EXECUTE
一生所爱的专栏
08-10 2154
转自http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php The DBMS_PARALLEL_EXECUTE package allows a workload associated with a base table to be broken down into smaller chunks whic
使用dbms_parallel_execute来完成DML的并行(r3笔记第1天)
杨建荣的学习笔记
09-20 192
在工作中使用并行可以极大的提高工作效率。可以Object,session.hint级别引入并行。可以使大量的数据处理更加高效。SQL> drop table t;T...
Oracle-dbms_parallel_execute包批量dml使用
sinat_36757755的博客
07-20 1052
dbms_parallel_execute包是Oracle11G的新功能，通过使用dbms_parallel_execute对DML批量操作按rowid,列，sql语句进行chunk切片，并发处理，从而提高批量操作的速度以及避免大事务的发生。
ORACLE普通表转分区表__使用ROWID将表切片迁移数据
u010692693的专栏
11-13 616
利用11G的新特性DBMS_PARALLEL_EXECUTE按ROWID对表进行分块，具体参考文章https://blog.csdn.net/u010692693/article/details/103048115 普通表 SQL> SELECT NUM_COL, COUNT(*) FROM TEST_TAB 2 GROUP BY NUM_COL ORDER BY NUM...
DBMS_PARALLEL_EXECUTE包的应用
张陈亚的博客
08-22 3570
前言叙述： 1 把数据集分割成小的块 2 在每一个块上以并行的方式应用update语句，在每个块执行完成后，提交！ 此更新技术有如下好处： 1 在执行update的时候，仅仅锁住一个shunk而非锁住整个表！ 2 因为每个chunk 执行完毕就提交，所以当update操作失败后，之前变更的并不会回滚！ 3 减小回滚空间的使用 4 提高性能 dbms_parallel_execute一般分为3个...
Oracle11.2新特性之使用DBMS_PARALLEL_EXECUTE包实现并行
01-05 221
使用DBMS_PARALLEL_EXECUTE包实现并行 该包支持insert、update、delete、merge、匿名包自动以scheduler job 方式并行执行。支持的chunk方式包括：CREATE_CHUNKS...
Oracle 并行处理
巫山云的专栏
08-03 1948
Oracle 表连接 Oracle 锁与锁争用（一） Oracle 并行处理  2013-03-30 12:25:25|  分类： Oracle |  标签：并行  |字号大中小 订阅 在一个串行的执行环境中，由单个进行程或线程负责处理SQL的操作，而且每个动作必须在随后的动作开始前完成。在任意给定的时刻，单个Oracle进程
Oracle11g使用dbms_parallel_execute对大表进行并行update
viviliving的专栏
07-16 768
1 把数据集分割成小的块 2 在每一个块上以并行的方式应用update语句，在每个块执行完成后，提交！ 此更新技术有如下好处： 1 在执行update的时候，仅仅锁住一个shunk而非锁住整个表！ 2 因为每个chunk 执行完毕就提交，所以当update操作失败后，之前变更的并不会回滚！ 3 减小回滚空间的使用 4 提高性能 dbms_parallel_execute一般分为3个步骤： 创建一个TASK；然后创建CHUNK把数据进行分批；最后是执行这个TASK。注意，该包是Oracle 11g 以后
oracle dbms_parallel_execute,学习dbms_parallel_execute包
weixin_32141253的博客
04-13 385
一、简介ORACLE11g R2版本的新特性之一就是引进了DBMS_PARALLEL_EXECUTE包，使用DBMS_PARALLEL_EXECUTE包批量并行递增式的更新表。更多ORACLE11g新特性请参考：http://www.cnblogs.com/oracle-dba/articles/3632223.html基本原理：(1)把数据集分割成小的块(chunk)，可基于rowid进行分块，...
DATAPUMP_EXP_SCHEMA 创建角色
最新发布
01-24
GRANT EXECUTE ON SYS.DBMS_DATAPUMP TO datapump_exp_role; ``` #### 4. 测试角色功能 验证所创建的角色是否能够正常工作非常重要。可以通过创建测试用户并将这个自定义角色分配给它来进行简单的测试。 ```sql ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值