查看: 93550|回复: 206

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

[复制链接]
论坛徽章:
10
ITPUB元老
日期:2005-02-28 12:57:002010广州亚运会纪念徽章:保龄球
日期:2011-01-30 11:57:03祖国60周年纪念徽章
日期:2009-10-09 08:28:00参与2009年中国云计算大会纪念
日期:2009-06-05 10:02:28ITPUB北京2009年会纪念徽章
日期:2009-02-09 11:42:452009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:鼠
日期:2008-01-02 17:35:53会员2006贡献徽章
日期:2006-04-17 13:46:34授权会员
日期:2005-10-30 17:05:33ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41
发表于 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)

[ 本帖最后由 fromeast 于 2009-3-22 21:52 编辑 ]
招聘 : 数据库管理员
论坛徽章:
2
生肖徽章2007版:鸡
日期:2008-01-02 17:35:53祖国60周年纪念徽章
日期:2009-10-09 08:28:00
发表于 2008-9-8 08:28 | 显示全部楼层
学习了,顶一个!

使用道具 举报

回复
论坛徽章:
145
生肖徽章2007版:兔
日期:2008-01-02 17:35:532010新春纪念徽章
日期:2010-01-04 08:33:082010新春纪念徽章
日期:2010-01-04 08:33:082010新春纪念徽章
日期:2010-01-04 08:33:082010新春纪念徽章
日期:2010-01-04 08:33:082010新春纪念徽章
日期:2010-01-04 08:33:082010新春纪念徽章
日期:2010-01-04 08:33:082009新春纪念徽章
日期:2009-01-04 14:52:282009新春纪念徽章
日期:2009-01-04 14:52:282009新春纪念徽章
日期:2009-01-04 14:52:28
发表于 2008-9-8 08:39 | 显示全部楼层
支持原创,顶!

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2008-9-8 08:39 | 显示全部楼层
在第一个里没看到你缓存T1表啊,想不明白这里有了order by rowid怎么会起到你说的那作用,order by rowid怎么会影响一个数据块里的数据呢???

使用道具 举报

回复
论坛徽章:
5
祖国60周年纪念徽章
日期:2009-10-09 08:28:002011新春纪念徽章
日期:2011-02-18 11:42:472012新春纪念徽章
日期:2012-01-04 11:54:262014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2008-9-8 08:52 | 显示全部楼层
这么大的表用游标遍历不太合适

使用道具 举报

回复
论坛徽章:
311
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2008-9-8 08:55 | 显示全部楼层
好思维!

使用道具 举报

回复
发表于 2008-9-8 08:56 | 显示全部楼层
原帖由 TO_TO_RO 于 2008-9-8 08:52 发表
这么大的表用游标遍历不太合适


那用什么呢?批量获取处理应该更快些

使用道具 举报

回复
论坛徽章:
5
祖国60周年纪念徽章
日期:2009-10-09 08:28:002011新春纪念徽章
日期:2011-02-18 11:42:472012新春纪念徽章
日期:2012-01-04 11:54:262014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2008-9-8 08:57 | 显示全部楼层
应该是你缓存了表的引起的效率提高。

使用道具 举报

回复
论坛徽章:
5
祖国60周年纪念徽章
日期:2009-10-09 08:28:002011新春纪念徽章
日期:2011-02-18 11:42:472012新春纪念徽章
日期:2012-01-04 11:54:262014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09
发表于 2008-9-8 09:05 | 显示全部楼层
根据表中字段的特点比如idx=aa,idx=bb....将小的表分割,直接UPDATE呢?
另外,游标里面每隔1000行就commit一次也是忌讳的。容易引起“快照太旧”错误

[ 本帖最后由 TO_TO_RO 于 2008-9-8 09:35 编辑 ]

使用道具 举报

回复
论坛徽章:
17
授权会员
日期:2008-08-18 15:32:56阿斯顿马丁
日期:2013-07-29 18:48:42蛋疼蛋
日期:2013-05-21 11:22:192012新春纪念徽章
日期:2012-01-04 11:49:54ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-03-21 14:26:302011新春纪念徽章
日期:2011-02-18 11:42:47ITPUB9周年纪念徽章
日期:2010-10-08 09:28:522010年世界杯参赛球队:塞尔维亚
日期:2010-01-07 09:54:43
发表于 2008-9-8 09:08 | 显示全部楼层

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

上面这句是不是将你的表都缓存在内存中了,所以快

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表