今天在加班中,本身五一准备钓鱼去的, 我仿佛听到鲤鱼妹妹欢乐的笑声。
加班归加班,但是活并不多,我负责后期数据库调优,数据关联的。 其他公司抽取数据进度不行,听到的回答只是,现在再抽,没有报错,目前抽了多少不知道,速度不知道。 什么时候抽完不知道,方案B没有,二线没有。这够无语的。 后期他们一张560G的大表, 抽取进度 206M/分钟。 而且还是我帮他们评估出来的。 肯定不行,于是换方案。够喜剧性的,这个公司领导陆续施压,把另外2个人0点叫起来,高铁过来,到现场已是凌晨2点, 把单独抽取大表。 我当天是正常下班的, 看着群里他们大领导挨个拉进来,一级级施压,估计这情节放在韩国够拍成电视剧20集。我们领导让我帮他们建大表索引,我也答应了。我建索引最快的一个是308S, 索引20G。 这个下次说吧。建主键,报错,重复数据。 原因是大表重抽,他们通道里面的数据无法清理,造成数据量重复。于是主键建不了。 涉及到560G大表重复数据删除,我当时用了20分钟不到,甚至真正delete的时候 用了118S,各位想想看能有什么办法搞定?? 如果你不会,看了今天的文章你应该有所感悟了。好了废话不说,上干货。
有个存储过程从周五晚上跑了到了周一还没有跑完,存储过程代码如下:
TMP_NBR_NO_XXXX共有400w行数据,180MB。For in 后面的查询
select nli.*, .......
and ns2l.nbr_level_id between 201 and 208 order by nl2i.priority;
上面SQL查询返回43行数据。
嵌套循环就是一个loop循环,loop套loop相当于笛卡尔积。该PLSQL代码中有loop套loop的情况,这就导致UPDATE TMP_NBR_NO_XXXX要执行400w*43次,TMP_NBR_NO_XXXX.no列没有索引,TMP_NBR_NO_XXXX每次更新都要进行全表扫描。这就是为什么存储过程从周五跑到周一还没跑完的原因。
有读者可能会问,为什么不用MERGE进行改写呢?在PLSQL代码中是用regexp_like关联的.无法走hash连接,也无法走排序合并连接,两表只能走嵌套循环并且被驱动表无法走索引。如果强行使用MERGE进行改写,因为该SQL执行时间很长,会导致UNDO不释放,因此,没有采用MERGE INTO对代码进行改写。
有读者可能也会问,为什么不对TMP_NBR_NO_XXXX.no建立索引呢?因为关联更新可以采用ROWID批量更新,所以没有采用建立索引方法优化。
下面采用ROWID批量更新方法改写上面PLSQL,为了方便读者阅读PLSQL代码,先创建一个临时表用于存储43记录:
create table TMP_DATE_TEST as
select nli.expression, nl.nbr_level_id, priority from tmp_xxx_item
...... and ns2l.nbr_level_id between 201 and 208;
创建另外一个临时表,用于存储要被更新的表的ROWID以及no字段:
create table TMP_NBR_NO_XXXX_TEXT as
select rowid rid, nbn.no from TMP_NBR_NO_XXXX nbn
Where nbn.level_id=1 and length(nbn.no)= 8;
改写后的PLSQL能在4小时左右跑完。有没有什么办法进一步优化呢?单个进程能在4小时左右跑完,如果开启8个并行进程,那应该能在30分钟左右跑完。但是PLSQL怎么开启并行呢?正常情况下PLSQL是无法开启并行的,如果直接在多个窗口中执行同一个PLSQL代码,会遇到锁争用,如果能解决锁争用,在多个窗口中执行同一个PLSQL代码,这样就变相实现了PLSQL开并行功能。可以利用ROWID切片变相实现并行:
select DBMS_ROWID.ROWID_CREATE(1,c.oid,e.RELATIVE_FNO,e.BLOCK_ID,0) minrid,
DBMS_ROWID.ROWID_CREATE(1,c.oid,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1, 10000) maxrid from dba_extents e,
(select max(data_object_id)oid from dba_objects where object_name=
'TMP_NBR_NO_XXXX_TEXT' and owner='RESXX2')and data_object_id is not null) c
where e.segment_name='TMP_NBR_NO_XXXX_TEXT'and e.owner = 'RESXX2';
但是这时发现,切割出来的数据分布严重不均衡,这是因为创建表空间的时候没有指定uniform size 的Extent所导致的。于是新建一个表空间,指定采用uniform size方式管理Extent:
create tablespace TBS_BSS_FIXED datafile '/oradata/bs_bss_fixed_500.dbf'
size 500M extent management local uniform size 128k;
重建一个表用来存储要被更新的ROWID:
create table RID_TABLE
( rowno NUMBER, minrid VARCHAR2(18), maxrid VARCHAR2(18)) ;
将ROWID插入到新表中:
这样RID_TABLE中每行指定的数据都很均衡,大概4035条数据。最终更改的PLSQL代码:
然后在8个窗口中同时运行上面PLSQL代码:
Begin pro_phone_grade(0); end; ..... Begin pro_phone_grade(7); end;
最终能在29分左右跑完所有存储过程。本案例技巧就在于ROWID切片实现并行,并且考虑到了数据分布对并行的影响,其次还使用了ROWID关联更新技巧。
这个案例, 就是数据规划的经典案例,同样的上面超大表数据删除也是一样的。你应该感悟点什么,