在拿awr报告时发现有一个存储过程效率很慢,执行要很长时间,所以在测试环境模拟,开始了我的优化之路..下面写一下我的整体优化步骤和优化后的效果。 通过awr报告定位到这条问题存储过程。 调试后分析大概需要42秒。 数量级为144万 表定义如下: 因为update部分耗时比较多,所以考虑改写成select看有没走索引。 这里用了索引范围扫描,继续下一步。 减少了12秒,但是还不是很理想。 改写后如下 核心脚本: 测试分析 这时候时间只需要0.06秒,性能获得了很大提升,到这里就剩下后面上生产测试验证的环节了,这步就忽略啦。 数据库优化是无止境的,这里实际上主要是简单应用了bulk collect将存储过程从42秒优化到0.06秒,如果需要的话还可以用forall来进一步优化,时间有限,就不继续了。大家有兴趣可以研究下。后面会分享更多优化方面内容,感兴趣的朋友可以关注下!概述
1、存储过程如下:
2、分析存储过程:
3、查看表数据量和定义
4、改写sql查看执行计划
select updatetime,sendtootm from Tab_TempDriverScan
where ScanWay = '1' and ShipmentNumber = v_shipmentnumber and LPN = idx.LPN and SKU = idx.SKU;
5、考虑update开并行
update /*+ parallel(Tab_TempDriverScan,8) */ Tab_TempDriverScan set UpdateTime = SYSDATE, SendToOTM = '1'
where ScanWay = '1' and ShipmentNumber = v_shipmentnumber and LPN = idx.LPN and SKU = idx.SKU;
6、应用bulk collect优化
TYPE Tab_TempDriverScan_rec_type IS RECORD --声明记录类型
(
v_shipmentnumber Tab_TempDriverScan.shipmentnumber%TYPE,
v_power_unit Tab_TempDriverScan.power_unit%TYPE,
v_IsSend Tab_TempDriverScan.IsSend%TYPE);
TYPE nested_Tab_TempDriverScan_type IS TABLE OF Tab_TempDriverScan_rec_type; --声明记录类型变量
Tab_TempDriverScan_tab nested_Tab_TempDriverScan_type;
...................
LOOP
fetch header1 BULK COLLECT
into Tab_TempDriverScan_tab; --应用bulk collect
EXIT WHEN header1%NOTFOUND;
........................