利用rowid快速在线更新海量数据

xifenfei · 发表于 2011-8-9 00:38

先顶下，深有感悟

wkc168 · 发表于 2011-8-9 09:12

我觉得关键是你把表加入了数据缓存里面

alter table T1 storage(buffer_pool keep); -- keep buffer pool size = 6GB

laobu · 发表于 2011-8-9 13:07

原帖由 fromeast 于 2008-9-8 03:13 发表
----转帖请注明作者和出处: fromeast http://www.itpub.net/thread-1052077-1-1.html
最近一直在折腾大表的更新问题，今天终于有了突破。兴奋之余发个帖子跟大家分享一下心得，并且讨论一下是否还可能进一步提高处理速度。
问题是这样的：一张5亿条记录的表，没有分区。由于增加了一个冗余字段，需要根据另外一张表（4.8亿条）更新这个大表。下面是具体的描述：
环境：HP-UX 11i+Oracle9.2.0.8+RAID
要更新的表：T1 (id1 number, id2 number, curr_count number,.....) --id1唯一  5亿条记录 >60GB
更新数据来源：T2 (id2 number, curr_count number) --id2唯一  4.8亿
更新逻辑：T2中的每一条记录，都到T1中找到对应的记录（T2.id2=T1.id2），更新T1.curr_count=T2.curr_count
限制条件：只能在线更新（应用程序一直在访问这个表，所以不能用INSERT SELECT），不能占用太多系统资源，要求3天之内更新完毕。
原来的做法：
declare
  cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
   T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2;
  v_counter number;
begin
  v_counter := 0;
  for row_t2 in cur_t2 loop
update T1 set curr_count=row_t2.curr_count
   where rowid=row_t2.row_id;
v_counter := v_counter + 1;
if (v_counter>=1000) then
   commit;
   v_counter := 0;
end if;
  end loop;
  commit;
end;
/
问题：更新太慢，260 rows/s，全部更新完毕需要22天！
经过调查发现是UPDATE语句执行的效率太低，进一步的跟踪发现，UPDATE至少90%的时间是在等待db file sequential read这个事件。按说都ROWID了，为什么还有这么多磁盘等待？再看disk reads，明白了，原来UPDATE语句产生了大量的物理读，当然慢了。想必T1表太大了，Data Buffer装不下，并且有其他的表跟它竞争，所以刚更新一条数据，从磁盘读取了一个数据块到内存，很快就被挤去出了，下次更新这个块上的其他数据时，还得再从磁盘读取。这样Data Buffer Cache的效率就很低，基本没有利用上。
怎么解决呢？最好是能按数据块的顺序更新，这样某个数据块里的第一行数据更新后，数据块内的其他行就不用再从磁盘里读取了（不太可能那么快就被挤出内存），物理读降低了，速度肯定能加快。可是怎样按数据块的顺序更新呢？我想到了ROWID的结构是data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，那么ROWID的顺序应该就是数据块的顺序了。于是我修改了PLSQL：
alter table T1 storage(buffer_pool keep); -- keep buffer pool size = 6GB
declare
  cursor cur_t2 is
select /*+ use_hash(T1,T2) parallel(T1,16) parallel_index(IX_T1_id2,16) */
   T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2
order by T1.rowid;
  v_counter number;
begin
  v_counter := 0;
  for row_t2 in cur_t2 loop
update T1 set curr_count=row_t2.curr_count
   where rowid=row_t2.row_id;
v_counter := v_counter + 1;
if (v_counter>=1000) then
   commit;
   v_counter := 0;
end if;
  end loop;
  commit;
end;
/
alter table T1 storage(buffer_pool default);

这回更新的速度大为加快：10000 rows/s。分析跟踪文件表明db file sequential reads和磁盘读取变的很少。按照这个速度20个小时之内就能全部更新完了。

心得：处理的数据量并没有减少，只是改变一下处理的顺序，也可以极大地提高性能。

====================================================================
*后记4：已上生产
*后记3：试验了KEEP的影响
(1)重新运行试验1（不order by rowid）
   开始的语句改成：alter table T1 storage(buffer_pool keep);
   处理速度：73~74行/秒
(2)重新运行试验2（order by rowid）：
   开始的语句改成：alter table T1 storage(buffer_pool default);
   处理速度：1万条/秒
结论：从本次测试可以印证先前的推断——把表的buffer_pool属性设为keep与否，对处理速度的影响很小，以至于可以忽略。处理速度加快的原因，是因为order by rowid，按块顺序处理数据，很大程度上减少了物理读。

*后记2：关于order by rowid的资料：
  http://rdc.taobao.com/blog/dba/html/199_oracle_rowid_order.html
这篇文章说order by rowid导致大量的查询物理读。其实在本文第二个测试中也是这样的——CURSOR的打开时间比不ORDER BY ROWID时间要长，因为多了SORT。可是这样是值得的，因为后续有大量的UPDATE，节省的物理读是很可观的。

*后记1：修改了几处错误：
  where T1.id1=T2.id2  => where T1.id2=T2.id2
parallel_index(IX_T2_id2,16) => parallel_index(IX_T1_id2,16)

测试了楼主的方法，以及68楼zhp6489的方法，阅读了很多讨论跟帖，补充2点：
1、这个问题之所以成立的前提条件是：
  A 表很大，buffer远远不够容纳，所以才会反复物理读。KEEP当然不起作用，很简单，内存装不下嘛
  B 业务对T1表有很多DML，长时间大面积锁定不可接受，不得已才分批处理

2、楼主的方法，问题在于存在大型排序，开销巨大；
   68楼zhp6489的方法，问题在于对T2表执行的是每行一次index scan，也不经济
为实现依照T1.rowid的顺序来UPDATE，其实有更“轻便”的办法：
cursor cur_t2 is
select /*+ leading(T2) use_hash(T1) */
   T2.id2, T2.curr_count, T1.rowid row_id
from T1, T2
where T1.id2=T2.id2;
干预cursor的表连接顺序，在hash连接中，指定T2表为驱动表，那么输出记录的顺序，自然就与T1表的记录存储顺序一致，天然就是按照T1.ROWID排序的
这样一来，在哈希连接的基础上免去了排序，开销只是对两张表的全表扫描

楼主及68楼zhp6489分享的经验，使我对相关问题加深了认识，在此感谢