PLSQL代码性能优化案例

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

有个存储过程从周五晚上跑了到了周一还没有跑完,存储过程代码如下:

TMP_NBR_NO_XXXX共有400w行数据,180MB。For in 后面的查询

上面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记录:

创建另外一个临时表,用于存储要被更新的表的ROWID以及no字段:

改写之后的PLSQL代码:

改写后的PLSQL能在4小时左右跑完。有没有什么办法进一步优化呢?单个进程能在4小时左右跑完,如果开启8个并行进程,那应该能在30分钟左右跑完。但是PLSQL怎么开启并行呢?正常情况下PLSQL是无法开启并行的,如果直接在多个窗口中执行同一个PLSQL代码,会遇到锁争用,如果能解决锁争用,在多个窗口中执行同一个PLSQL代码,这样就变相实现了PLSQL开并行功能。可以利用ROWID切片变相实现并行:

但是这时发现,切割出来的数据分布严重不均衡,这是因为创建表空间的时候没有指定uniform size 的Extent所导致的。于是新建一个表空间,指定采用uniform size方式管理Extent:

重建一个表用来存储要被更新的ROWID:

将ROWID插入到新表中:

这样RID_TABLE中每行指定的数据都很均衡,大概4035条数据。最终更改的PLSQL代码:

然后在8个窗口中同时运行上面PLSQL代码:

最终能在29分左右跑完所有存储过程。本案例技巧就在于ROWID切片实现并行,并且考虑到了数据分布对并行的影响,其次还使用了ROWID关联更新技巧。

未经允许不得转载:Oracle一体机用户组 » PLSQL代码性能优化案例

相关推荐