查看: 6443|回复: 28

一个巨大表数据转换优化,终于将时间控制在能接受的范围内了。

[复制链接]
论坛徽章:
13
授权会员
日期:2008-08-13 23:11:472010新春纪念徽章
日期:2010-03-01 11:19:502010新春纪念徽章
日期:2010-01-04 08:33:08生肖徽章2007版:猴
日期:2009-03-10 21:35:502009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-01-03 01:01:02生肖徽章2007版:狗
日期:2009-01-02 01:31:33生肖徽章2007版:龙
日期:2008-12-29 23:12:04生肖徽章2007版:猴
日期:2008-12-20 15:56:30ITPUB元老
日期:2008-11-25 00:30:00
发表于 2009-12-22 22:44 | 显示全部楼层 |阅读模式
本文只提供解决问题的思路,那些罗嗦的SQL,如果不是必要我就免谈了,LINUX环境下,4个3.0G双核 CPU ,8G内存,一个巨大的表A,有2.6亿条数据,表内有21个列,具体表的定义涉及到客户信息就不列出来了,由于原始开发商偷懒,这个表记录了从2000年-2009年的全部历史数据,其中还有N多非活动的数据,导致这个表膨胀,单单表的磁盘空间占用就超过40G,而且这个表没有做表分区,涉及这个表的数据访问很慢,准备做一个数据结转,将满足特定条件的记录(主要是非活动的数据和2004年以前的部分历史遗留数据)全部写到一个新的表B,然后将这个表A内相关的记录删除,经过查询计算,新的表A数据量能降到大约1亿条以下,然后打算再在这个基础上做分区表。

问题来了,这个表在周六周日是没人访问的,因此留给我们的数据转换时间只有48个小时,开发人员做了一个SQL脚本,测试了一通告诉我每秒只有50几条,晕倒,按这样速度计算1个小时才能转换18万条,仔细看过开发人员的脚本就是很简单,将满足条件的查询出来插入一个新的表,然后根据其他表的参数计算表某些记录值,更新某些记录,然后在原表上删除旧的数据,提交,开发人员还特意写了一个循环,每1000条提交一次。我看了又一遍一遍,实在是找不出些SQL语句有何可优化的地方,晕倒,难道我要将数据导到更快的机器上运行完成然后再导回来?申请机器,领导不允许,看来自己得想想办法了。

监控数据库,发现执行的时候,I/O等待高得惊人,再查询,发现均是REDO写等待,检查数据库当前REDO,呵呵和数据文件在同一个LUN上,马上从SAN存储上划了3个1G的小LUN给数据库,将这几个LUN做成RAW,将REDO log指向这几个RAW.再执行,呵呵,每秒上升到了80条。
看来还不能生成REDO
在唯一的一条delete 语句后面加上NOLOGGING
这次老天不开眼,再跑还是徘徊在每秒80-90条左右。

看来还是有问题,在查询数据库这次发现是大量的TEMP表空间读写,大量的TEMP占用通常是涉及大量的排序,这里什么东西排序最多?用下面的语句查询下表的占用情况,呵呵隐去表名和索引名,只需要表A和IDX_A_D1

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024 desc;

A  42718
PK_A_C1 21710
IDX_A_D1 18703
晕倒,连索引都占这么大的空间,8G内存的机器要维护索引还不频繁的写TEMP表空间。
看来这个优化的目标一切都要以降低I/O为目的
做个表压缩如何?
create table a_compress compress as select * from a;
压缩挺快的,我原以为要1-2个小时,谁知道才20来分钟就搞定了
看看压缩后的效果如何,在查询一下
A 9126
效果还是非常明显的,连索引也压缩一下

ALTER INDEX IDX_A_D1 REBUILD COMPRESS NOLOGGING;
IDX_A_D1        5440
呵呵再跑一遍试一下,哇这次比较爽了,每秒转换310条了,换算一下有100多万条每小时了,48个小时还是不够用呀。
接着测试,突然想到,这个表这么大,如果我将这个表拆成几个较小的表然后再压缩是不是就基本能全部加载到内存里了,机器的DB_CACHE定为5G?
考虑了一下,按年份拆分成4份,每份6500万左右
create table a_200212 pctfree 15 compress as select * from a where D1 <= '200012';
查看数据大小

A  42718
a_200212 1710
IDX_A_D1 1403
呵呵这两个表应该能完整装入DB_CACHE了,速度应该大大增加了吧,再测试一下,哇每秒1570条,呵呵,每小时接近565万条了,6500万条可以在11.5个小时内跑完,呵呵这样4个6500万,可以在46个小时跑完,^_^,加上我可以申请从周五下班开始跑起,一直跑到周一早上8点前,足足有
62个小时的时间,耗去准备数据的1个多小时,我终于将时间控制在规定的时间内了,其实如果搞多点内存上去,应该还能更快一点。

数据库慢不可怕,关键是搞清楚啥东西忙,然后设法解决忙的问题。

[ 本帖最后由 wwwlh 于 2009-12-22 23:18 编辑 ]
论坛徽章:
25
授权会员
日期:2007-08-20 23:44:422011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-02-18 11:42:49管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-01-04 11:49:54咸鸭蛋
日期:2012-02-06 17:15:202012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:362012新春纪念徽章
日期:2012-02-13 15:11:36
发表于 2009-12-22 22:55 | 显示全部楼层
delete语句后面加上nologging,本来就没有这样的写法,谈何减少redo?
nologging只出现于append方式的insert,direct loader,move,create table as等操作。
既然周末48小时都没有人访问,那使用create table as创建两张表,一张是满足归档条件而形成的归档(历史)表,另一张是当前需要用的表。然后重建索引、创建约束、赋权限等操作。40G的表操作应该是很快的。在我看来,4个小时之内是完全可以搞定的。

使用道具 举报

回复
论坛徽章:
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
发表于 2009-12-22 23:08 | 显示全部楼层
我也觉得楼主的方法不当,既然完全停机搞了还这么折腾,ctas(分区表)+parallel+nologging然后创建索引和约束,收集统计信息,这样估计几小时搞定吧,也没不用维护索引了,也没redo了

[ 本帖最后由 棉花糖ONE 于 2009-12-22 23:09 编辑 ]

使用道具 举报

回复
论坛徽章:
55
马上加薪
日期:2014-02-19 11:55:142010广州亚运会纪念徽章:排球
日期:2011-04-27 13:27:19SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:01生肖徽章2007版:兔
日期:2011-01-20 12:58:49
发表于 2009-12-22 23:10 | 显示全部楼层
原帖由 magic007 于 2009-12-22 22:55 发表
delete语句后面加上nologging,本来就没有这样的写法,谈何减少redo?
nologging只出现于append方式的insert,direct loader,move,create table as等操作。
既然周末48小时都没有人访问,那使用create table as创建两张表,一张是满足归档条件而形成的归档(历史)表,另一张是当前需要用的表。然后重建索引、创建约束、赋权限等操作。40G的表操作应该是很快的。在我看来,4个小时之内是完全可以搞定的。



SQL> create table test as select * from user_objects
  2  ;

表已创建。

SQL> select count(*) from test;

  COUNT(*)
----------
     22903

SQL> delete from test nologging;

已删除22903行。

SQL> commit;

提交完成。

SQL>

敢问为什么加了也没有问题呢??

使用道具 举报

回复
论坛徽章:
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
发表于 2009-12-22 23:12 | 显示全部楼层

回复 #4 xsmdel 的帖子

加了当作别名处理,delete和update根本没法nologging,因为无法通过直接往高水位上操作

使用道具 举报

回复
论坛徽章:
13
授权会员
日期:2008-08-13 23:11:472010新春纪念徽章
日期:2010-03-01 11:19:502010新春纪念徽章
日期:2010-01-04 08:33:08生肖徽章2007版:猴
日期:2009-03-10 21:35:502009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-01-03 01:01:02生肖徽章2007版:狗
日期:2009-01-02 01:31:33生肖徽章2007版:龙
日期:2008-12-29 23:12:04生肖徽章2007版:猴
日期:2008-12-20 15:56:30ITPUB元老
日期:2008-11-25 00:30:00
 楼主| 发表于 2009-12-22 23:16 | 显示全部楼层
看来大家还不理解我的环境,除了这个大表周六周日没人访问(特意让客户停止访问的),数据库上面还有一些其他表某些客户要访问,那个大表不是简单的删除取出来那么简单,新的表上有一些数据变换的操作,旧表上也有一些更新记录的操作,涉及到一些其他表记录的计算,都是原始开发商的问题,只能在原表上删除,如果能直接SELECT出来,我早就不那么麻烦了,呵呵刚才没有编辑完成,不小心按了一下提交,环境没给大家说清楚。

[ 本帖最后由 wwwlh 于 2009-12-22 23:22 编辑 ]

使用道具 举报

回复
论坛徽章:
55
马上加薪
日期:2014-02-19 11:55:142010广州亚运会纪念徽章:排球
日期:2011-04-27 13:27:19SQL大赛参与纪念
日期:2011-04-13 12:08:172011新春纪念徽章
日期:2011-02-18 11:43:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:502011新春纪念徽章
日期:2011-01-25 15:41:01生肖徽章2007版:兔
日期:2011-01-20 12:58:49
发表于 2009-12-22 23:21 | 显示全部楼层
原帖由 棉花糖ONE 于 2009-12-22 23:12 发表
加了当作别名处理,delete和update根本没法nologging,因为无法通过直接往高水位上操作


谢了棉花兄。

使用道具 举报

回复
论坛徽章:
13
授权会员
日期:2008-08-13 23:11:472010新春纪念徽章
日期:2010-03-01 11:19:502010新春纪念徽章
日期:2010-01-04 08:33:08生肖徽章2007版:猴
日期:2009-03-10 21:35:502009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-01-03 01:01:02生肖徽章2007版:狗
日期:2009-01-02 01:31:33生肖徽章2007版:龙
日期:2008-12-29 23:12:04生肖徽章2007版:猴
日期:2008-12-20 15:56:30ITPUB元老
日期:2008-11-25 00:30:00
 楼主| 发表于 2009-12-22 23:23 | 显示全部楼层
原帖由 xsmdel 于 2009-12-22 23:21 发表


谢了棉花兄。

看来当时我的想法错了,测试表明,在delete上加NOLOGGING也没啥用

[ 本帖最后由 wwwlh 于 2009-12-22 23:26 编辑 ]

使用道具 举报

回复
论坛徽章:
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
发表于 2009-12-22 23:32 | 显示全部楼层
有个问题请教楼主:如何发现REDO和数据文件是同一个LUN上?

数据文件的文件系统(LV)由几个POWERHARDDISK组成,这时,创建数据文件,肯定是在这几个LUN上,你的LUN多大?

另外,有否测试了用在线重定义的功能,把数据转移到安日期分区的表中? 后面再操作分区表,肯定方便多了.

使用道具 举报

回复
论坛徽章:
13
授权会员
日期:2008-08-13 23:11:472010新春纪念徽章
日期:2010-03-01 11:19:502010新春纪念徽章
日期:2010-01-04 08:33:08生肖徽章2007版:猴
日期:2009-03-10 21:35:502009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-01-03 01:01:02生肖徽章2007版:狗
日期:2009-01-02 01:31:33生肖徽章2007版:龙
日期:2008-12-29 23:12:04生肖徽章2007版:猴
日期:2008-12-20 15:56:30ITPUB元老
日期:2008-11-25 00:30:00
 楼主| 发表于 2009-12-22 23:34 | 显示全部楼层
原帖由 ZALBB 于 2009-12-22 23:32 发表
有个问题请教楼主:如何发现REDO和数据文件是同一个LUN上?

数据文件的文件系统(LV)由几个POWERHARDDISK组成,这时,创建数据文件,肯定是在这几个LUN上,你的LUN多大?

另外,有否测试了用在线重定义的功能,把数据转移到安日期分区的表中? 后面再操作分区表,肯定方便多了.

看REDO的名称呀,如果是在同一个VG下的,基本上可以肯定就是在相同的LUN上了,redo还是独立的LUN比较好
我的数据库数据文件的LUN一个是50G,有7个
没有测试过在线重定义的功能,我的环境不适合使用在线重定义的功能,我的数据在有些满足条件的数据转换过程中要被改写的,是为了解决某些数据错误的问题,所以我的数据要先生成了大表,下一步才好考虑分区表的问题。

[ 本帖最后由 wwwlh 于 2009-12-22 23:39 编辑 ]

使用道具 举报

回复

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

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


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

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