ORACLE:大数据update(部分转载)

前端之家收集整理的这篇文章主要介绍了ORACLE:大数据update(部分转载)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
  1. 批量更新,MERGE语句性能最好,因为它可以多块读,并且可以并行执行,但是缺点就是消耗比较多的UNDO,一旦down机死事物恢复较慢。
  2. ORDER BY ROWID buffer cache 不够大的情况下性能较好好(没Merge快,因为Merge可以多块读,走ROWID只能单块读)。
  3. 优点就是可以批量提交。缺点就是不能并行更新。
  4. ORDER BY ROWID buffer cache足够大(能确保被更新的表不被page out) 的情况下性能较好。
  5. create table a as select * from dba_objects;
  6. create table b as select * from dba_objects;
  7. insert into b select * from b; --- 直到插入60W数据
  8. sql> SELECT COUNT(*) FROM B;
  9. COUNT(*)
  10. ----------
  11. 616864
  12. sql> SELECT SUM(BYTES)/1024/1024 "SIZE(MB)" FROM DBA_SEGMENTS WHERE SEGMENT_NAME='B';
  13. SIZE(MB)
  14. ----------
  15. 72
  16. create index idx_a on a(object_name,object_id);
  17. create index idx_b on b(object_id);
  18. 比如要执行这个update b set b.object_name=(select a.object_name from a where a.object_id=b.object_id);
  19. 可以用MERGE代替
  20. -------------------------MERGE版本,使用MERGE一定要确保MERGE into 的表走全表扫描----------------
  21. alter session set db_file_multiblock_read_count=128;
  22. 如果要更新的表很大,alter session enable parallel dml;
  23. alter session set workarea_size_policy=manual;
  24. alter session set sort_area_size=xxx;
  25. alter session set hash_area_size=xxx;
  26. merge /*+ USE_HASH(C,H) FULL(C) */ into b c
  27. using (select /*+INDEX(A) USE_HASH(A) */ a.object_name,a.object_id
  28. from a
  29. where a.object_id in (select /*+ use_hash(b) index(b) */ object_id from b)) h
  30. on (c.object_id = h.object_id)
  31. when matched then
  32. update set c.object_name = h.object_name;
  33. select * from table(dbms_xplan.display);
  34. 也可以写PL/sql
  35. -------------------------PL/sql版本---------------------------------
  36. DECLARE
  37. CURSOR CUR_B IS
  38. SELECT a.object_id,a.object_name,b.ROWID ROW_ID
  39. FROM A,B
  40. WHERE A.object_id = B.object_id
  41. ORDER BY B.ROWID;
  42. V_COUNTER NUMBER;
  43. BEGIN
  44. V_COUNTER := 0;
  45. FOR ROW_B IN CUR_B LOOP
  46. UPDATE b SET object_name = ROW_B.object_name WHERE ROWID = ROW_B.ROW_ID;
  47. V_COUNTER := V_COUNTER + 1;
  48. IF (V_COUNTER >= 10000) THEN
  49. COMMIT;
  50. dbms_output.put_line('Updated: ' ||V_COUNTER || ' lines.');
  51. V_COUNTER := 0;
  52. END IF;
  53. END LOOP;
  54. COMMIT;
  55. END;
  56. /
  57. 下面这个版本是批量处理的版本
  58. ------------------------批量处理版本--------------------------------
  59. declare
  60. maxrows number default 100000;
  61. row_id_table dbms_sql.urowid_table;
  62. --currcount_table dbms_sql.number_Table;
  63. object_name_table dbms_sql.varchar2_Table;
  64. cursor cur_b is
  65. SELECT /*+ index(a) use_hash(a,b) index(b) */
  66. a.object_name,B
  67. WHERE A.object_id = B.object_id
  68. ORDER BY B.ROWID;
  69. v_counter number;
  70. begin
  71. v_counter := 0;
  72. open cur_b;
  73. loop
  74. EXIT WHEN cur_b%NOTFOUND;
  75. FETCH cur_b bulk collect
  76. into object_name_table,row_id_table limit maxrows;
  77. forall i in 1 .. row_id_table.count
  78. update b
  79. set object_name = object_name_table(i)
  80. where rowid = row_id_table(i);
  81. commit;
  82. end loop;
  83. end;
  84. /
  85. 关于ORDER BY ROWID提升速度的验证
  86. -------------------Buffer cache 不够大---------------------------------------
  87. buffer cache 40MbB表有72Mb 所以不够存放下 B
  88. keep pool 52Mb--keep idx_a,idx_b
  89. sql> alter system flush buffer_cache;
  90. 系统已更改。
  91. 已用时间: 00: 00: 00.00
  92. sql> merge into b c
  93. 2 using (select a.object_name,a.object_id
  94. 3 from a
  95. 4 where a.object_id in (select object_id from b)) h
  96. 5 on (c.object_id = h.object_id)
  97. 6 when matched then
  98. 7 update set c.object_name = h.object_name;
  99. 616851 行已合并。
  100. 已用时间: 00: 00: 12.51
  101. sql> alter system flush buffer_cache;
  102. 系统已更改。
  103. 已用时间: 00: 00: 00.00
  104. sql> declare
  105. 2 maxrows number default 100000;
  106. 3 row_id_table dbms_sql.urowid_table;
  107. 4 --currcount_table dbms_sql.number_Table;
  108. 5 object_name_table dbms_sql.varchar2_Table;
  109. 6 cursor cur_b is
  110. 7 SELECT /*+ index(a) use_hash(a,b) index(b) */
  111. 8 a.object_name,b.ROWID ROW_ID
  112. 9 FROM A,B
  113. 10 WHERE A.object_id = B.object_id
  114. 11 ORDER BY B.ROWID; ------有ORDER BY ROWID
  115. 12 v_counter number;
  116. 13 begin
  117. 14 v_counter := 0;
  118. 15 open cur_b;
  119. 16 loop
  120. 17 EXIT WHEN cur_b%NOTFOUND;
  121. 18 FETCH cur_b bulk collect
  122. 19 into object_name_table,row_id_table limit maxrows;
  123. 20 forall i in 1 .. row_id_table.count
  124. 21 update b
  125. 22 set object_name = object_name_table(i)
  126. 23 where rowid = row_id_table(i);
  127. 24 commit;
  128. 25 end loop;
  129. 26 end;
  130. 27 /
  131. PL/sql 过程已成功完成。
  132. 已用时间: 00: 00: 31.71
  133. sql> alter system flush buffer_cache;
  134. 系统已更改。
  135. 已用时间: 00: 00: 01.87
  136. sql> declare
  137. 2 maxrows number default 100000;
  138. 3 row_id_table dbms_sql.urowid_table;
  139. 4 --currcount_table dbms_sql.number_Table;
  140. 5 object_name_table dbms_sql.varchar2_Table;
  141. 6 cursor cur_b is
  142. 7 SELECT /*+ index(a) use_hash(a,B
  143. 10 WHERE A.object_id = B.object_id;
  144. 11 v_counter number;
  145. 12 begin
  146. 13 v_counter := 0;
  147. 14 open cur_b;
  148. 15 loop
  149. 16 EXIT WHEN cur_b%NOTFOUND;
  150. 17 FETCH cur_b bulk collect
  151. 18 into object_name_table,row_id_table limit maxrows;
  152. 19 forall i in 1 .. row_id_table.count
  153. 20 update b
  154. 21 set object_name = object_name_table(i)
  155. 22 where rowid = row_id_table(i);
  156. 23 commit;
  157. 24 end loop;
  158. 25 end;
  159. 26 /
  160. PL/sql 过程已成功完成。
  161. 已用时间: 00: 01: 25.64
  162. MERGE只需要13秒,ORDER BY ROWID 的PL/sql 需要32秒,而没ORDER BY ROWID 的PL/sql 需要1分26秒
  163. 如果buffer cache不够大(不能容纳下A,B),不order by rowid 要花1分25秒,order by rowid只花了32秒
  164. 可见,ORDER BY ROWID 在BUFFER CACHE不够大的情况下,对于速度的提升是非常明显的,因为buffer cache不够大,block可能经常被page out。
  165. order by rowid 会连续更新临近的block,这样就确保读入的block尽可能的不被page out。
  166. 为什么Merge 比 用ROWID 去更新快呢?因为MERGE可以多块读,做MERGE的时候设置参数db_file_multiblock_read_count=128
  167. 根据ROWID去更新,只能一次读一个block
  168. ---------------------BUFFER CACHE 足够大,多次执行,取运行最快的时间---------------------------------------
  169. sql> alter system set db_keep_cache_size=150m;
  170. 系统已更改。
  171. sql> alter table a storage(buffer_pool keep);
  172. 表已更改。
  173. sql> alter table b storage(buffer_pool keep);
  174. 表已更改。
  175. sql> select /*+ full(a) */ count(*) from a;
  176. COUNT(*)
  177. ----------
  178. 50526
  179. 已用时间: 00: 00: 00.16
  180. sql> select /*+ full(b) */ count(*) from b;
  181. COUNT(*)
  182. ----------
  183. 616864
  184. 已用时间: 00: 00: 01.08
  185. sql> SELECT o.OWNER,o.object_type,o.OBJECT_NAME,COUNT(*) NUMBER_OF_BLOCKS
  186. 2 FROM DBA_OBJECTS o,V$BH bh
  187. 3 WHERE o.DATA_OBJECT_ID = bh.OBJD
  188. 4 AND o.owner='SCOTT'
  189. 5 GROUP BY o.owner,o.OBJECT_NAME
  190. 6 ORDER BY COUNT(*)desc,2 ;
  191. OWNER OBJECT_TYPE OBJECT_NAME NUMBER_OF_BLOCKS
  192. ------------------------------ ------------------- ------------------------------ ----------------
  193. SCOTT TABLE B 8467
  194. SCOTT INDEX IDX_B 1394
  195. SCOTT TABLE A 695
  196. SCOTT INDEX IDX_A 288
  197. sql> declare
  198. 2 maxrows number default 100000;
  199. 3 row_id_table dbms_sql.urowid_table;
  200. 4 --currcount_table dbms_sql.number_Table;
  201. 5 object_name_table dbms_sql.varchar2_Table;
  202. 6 cursor cur_b is
  203. 7 SELECT /*+ index(a) use_hash(a,B
  204. 10 WHERE A.object_id = B.object_id
  205. 11 ORDER BY B.ROWID;
  206. 12 v_counter number;
  207. 13 begin
  208. 14 v_counter := 0;
  209. 15 open cur_b;
  210. 16 loop
  211. 17 EXIT WHEN cur_b%NOTFOUND;
  212. 18 FETCH cur_b bulk collect
  213. 19 into object_name_table,row_id_table limit maxrows;
  214. 20 forall i in 1 .. row_id_table.count
  215. 21 update b
  216. 22 set object_name = object_name_table(i)
  217. 23 where rowid = row_id_table(i);
  218. 24 commit;
  219. 25 end loop;
  220. 26 end;
  221. 27 /
  222. PL/sql 过程已成功完成。
  223. 已用时间: 00: 00: 11.83
  224. sql> declare
  225. 2 maxrows number default 100000;
  226. 3 row_id_table dbms_sql.urowid_table;
  227. 4 --currcount_table dbms_sql.number_Table;
  228. 5 object_name_table dbms_sql.varchar2_Table;
  229. 6 cursor cur_b is
  230. 7 SELECT /*+ index(a) use_hash(a,row_id_table limit maxrows;
  231. 19 forall i in 1 .. row_id_table.count
  232. 20 update b
  233. 21 set object_name = object_name_table(i)
  234. 22 where rowid = row_id_table(i);
  235. 23 commit;
  236. 24 end loop;
  237. 25 end;
  238. 26 /
  239. PL/sql 过程已成功完成。
  240. 已用时间: 00: 00: 09.71
  241. sql> merge into b c
  242. 2 using (select a.object_name,a.object_id
  243. 3 from a
  244. 4 where a.object_id in (select object_id from b)) h
  245. 5 on (c.object_id = h.object_id)
  246. 6 when matched then
  247. 7 update set c.object_name = h.object_name;
  248. 616851 行已合并。
  249. 已用时间: 00: 00: 08.54
  250. ORDER BY ROWID 的PL/sql 用了11秒,没有ORDER BY ROWID的PL/sql用了9秒,而Merge最快,只花了8秒多。
  251. (反复测试,以最快时间为准,添加了3个logfile group 每组500Mb,减少logfile对测试的影响)。
  252. 由此可见,如果buffer cache够大,不order by rowid 反比order by rowid更快(因为少了排序)


  1. 大数据updata
  2. ----转帖请注明作者和出处: fromeast http://www.itpub.net/thread-1052077-1-1.html
  3. 最近一直在折腾大表的更新问题,今天终于有了突破。兴奋之余发个帖子跟大家分享一下心得,并且讨论一下是否还可能进一步提高处理速度。
  4. 问题是这样的:一张5亿条记录的表,没有分区。由于增加了一个冗余字段,需要根据另外一张表(4.8亿条)更新这个大表。下面是具体的描述:
  5. 环境:HP-UX 11i+Oracle9.2.0.8+RAID
  6. 要更新的表:T1 (id1 number,id2 number,curr_count number,.....) --id1唯一 5亿条记录 >60GB
  7. 更新数据来源:T2 (id2 number,curr_count number) --id2唯一 4.8亿
  8. 更新逻辑:T2中的每一条记录,都到T1中找到对应的记录(T2.id2=T1.id2),更新T1.curr_count=T2.curr_count
  9. 限制条件:只能在线更新(应用程序一直在访问这个表,所以不能用INSERT SELECT),不能占用太多系统资源,要求3天之内更新完毕。
  10. 原来的做法:
  11. declare
  12. cursor cur_t2 is
  13. select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
  14. T2.id2,T2.curr_count,T1.rowid row_id
  15. from T1,T2
  16. where T1.id2=T2.id2;
  17. v_counter number;
  18. begin
  19. v_counter := 0;
  20. for row_t2 in cur_t2 loop
  21. update T1 set curr_count=row_t2.curr_count
  22. where rowid=row_t2.row_id;
  23. v_counter := v_counter + 1;
  24. if (v_counter>=1000) then
  25. commit;
  26. v_counter := 0;
  27. end if;
  28. end loop;
  29. commit;
  30. end;
  31. /
  32. 问题:更新太慢,260 rows/s,全部更新完毕需要22天!
  33. 经过调查发现是UPDATE语句执行的效率太低,进一步的跟踪发现,UPDATE至少90%的时间是在等待db file sequential read这个事件。按说都ROWID了,为什么还有这么多磁盘等待?再看disk reads,明白了,原来UPDATE语句产生了大量的物理读,当然慢了。想必T1表太大了,Data Buffer装不下,并且有其他的表跟它竞争,所以刚更新一条数据,从磁盘读取了一个数据块到内存,很快就被挤去出了,下次更新这个块上的其他数据时,还得再从磁盘读取。这样Data Buffer Cache的效率就很低,基本没有利用上。
  34. 怎么解决呢?最好是能按数据块的顺序更新,这样某个数据块里的第一行数据更新后,数据块内的其他行就不用再从磁盘里读取了(不太可能那么快就被挤出内存),物理读降低了,速度肯定能加快。可是怎样按数据块的顺序更新呢?我想到了ROWID的结构是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串),那么ROWID的顺序应该就是数据块的顺序了。于是我修改PLsql
  35. alter table T1 storage(buffer_pool keep); -- keep buffer pool size = 6GB
  36. declare
  37. cursor cur_t2 is
  38. select /*+ use_hash(T1,T2
  39. where T1.id2=T2.id2
  40. order by T1.rowid;
  41. v_counter number;
  42. begin
  43. v_counter := 0;
  44. for row_t2 in cur_t2 loop
  45. update T1 set curr_count=row_t2.curr_count
  46. where rowid=row_t2.row_id;
  47. v_counter := v_counter + 1;
  48. if (v_counter>=1000) then
  49. commit;
  50. v_counter := 0;
  51. end if;
  52. end loop;
  53. commit;
  54. end;
  55. /
  56. alter table T1 storage(buffer_pool default);
  57. 这回更新的速度大为加快:10000 rows/s。分析跟踪文件表明db file sequential reads和磁盘读取变的很少。按照这个速度20个小时之内就能全部更新完了。
  58. 心得:处理的数据量并没有减少,只是改变一下处理的顺序,也可以极大地提高性能
  59. ====================================================================
  60. *后记4:已上生产
  61. *后记3:试验了KEEP的影响
  62. (1)重新运行试验1(不order by rowid)
  63. 开始的语句改成:alter table T1 storage(buffer_pool keep);
  64. 处理速度:73~74行/秒
  65. (2)重新运行试验2(order by rowid):
  66. 开始的语句改成:alter table T1 storage(buffer_pool default);
  67. 处理速度:1万条/秒
  68. 结论:从本次测试可以印证先前的推断——把表的buffer_pool属性设为keep与否,对处理速度的影响很小,以至于可以忽略。处理速度加快的原因,是因为order by rowid,按块顺序处理数据,很大程度上减少了物理读。
  69. *后记2:关于order by rowid的资料:
  70. http://rdc.taobao.com/blog/dba/html/199_oracle_rowid_order.html
  71. 这篇文章说order by rowid导致大量的查询物理读。其实在本文第二个测试中也是这样的——CURSOR的打开时间比不ORDER BY ROWID时间要长,因为多了SORT。可是这样是值得的,因为后续有大量的UPDATE,节省的物理读是很可观的。
  72. *后记1:修改了几处错误
  73. where T1.id1=T2.id2 => where T1.id2=T2.id2
  74. parallel_index(IX_T2_id2,16) => parallel_index(IX_T1_id2,16)

猜你在找的Oracle相关文章