欢迎访问 生活随笔！
TAG聚合

生活随笔

当前位置： 首页 >

Oracle某行系统SQL优化

发布时间：2024/8/26 49 豆豆
生活随笔 收集整理的这篇文章主要介绍了 Oracle某行系统SQL优化 小编觉得挺不错的,现在分享给大家,帮大家做个参考.

问题说明：   

业务人员反馈系统跑批慢了，平时耗时5分钟，现在需要跑3个多小时，而且是每月10日和每月15日都会变慢。

环境说明：

DB:Oracle 11.2.0.4.0 RAC OS:AIX 7.1

问题分析:

抓取跑批对应的慢SQL,查看SQL文本如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

select  '2021/08/15',

         RelativeDeductaccno,

         RelativeDeductaccno,

         LB.Deductaccno,

         lb.putoutno,

         LB.Customerid,

         LB.Customername,

         SI.ManageOrgID,

         0,

         LB.Normalbalance + LB.Overduebalance + LB.Waitoverduebalance AS balance,

         LB.Normalbalance + LB.Overduebalance + LB.Waitoverduebalance AS Actualbalance,

         SaveBeginSum * 10000,

         LoanBeginSum * 10000,

         SaveStandardSum * 10000,

         LoanStandardSum * 10000,

         nvl(ImpawnRatio1, 0),

         nvl(ImpawnRatio2, 0),

         nvl(ImpawnRatio3, 0),

         nvl(ImpawnRatio4, 0),

         IncomeBase * 10000,

         LB.Executerate / (30 * 1000),

         LB.Loanrate / (30 * 1000),

         case

           when LB.Executerate < LB.Loanrate then

            LB.Executerate / (30 * 1000)

           else

            LB.Loanrate / (30 * 1000)

         end,

         SI.SaveRate / 1000,

         LB.maturitydate,

         IncomeReturnDay,

         0,

         '0',

         '1',

         MainSaveToLoanFlag,

         case

           when LB.LoanStatus <= '1' then

            1

           else

            0

         end as LoanStatus,

         lb.assetflag,

         lb.businesstype,

         nvl(case

               when cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15') > 0 then

                cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15')

               else

                0

             end,

             0),

         case

           when nvl(LB.assetflag, 0) = '1' and

                cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15') > 0 then

            nvl(cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15'), 0)

           else

            0

         end

    from cjcaaaaaaa_info SI, chen_balance LB

   where SI.putoutno = LB.putoutno

     and SI.Validdate <= '2021/08/15'

     and Status = '1'

     and ACCOUNTFLAG = '1'

手动执行，查看速度：

返回前100条记录很快，之后平均每10秒取出100行数据，最终取出全部结果集耗时很长。

查看执行计划：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

   PLAN_TABLE_OUTPUT

......

20 

21------------------------------------------------------------------------------------------------

22| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

23------------------------------------------------------------------------------------------------

24|   0 | SELECT STATEMENT             |                 |       |       | 14354 (100)|          |

25|   1 |  NESTED LOOPS                |                 |  6966 |  1367K| 14354   (1)| 00:00:01 |

26|   2 |   NESTED LOOPS               |                 |  6966 |  1367K| 14354   (1)| 00:00:01 |

27|*  3 |    TABLE ACCESS FULL         | cjcaaaaaaa_info |  6966 |   666K|   418   (1)| 00:00:01 |

28|*  4 |    INDEX UNIQUE SCAN         | chen_balance_PK |     1 |       |     1   (0)| 00:00:01 |

29|   5 |   TABLE ACCESS BY INDEX ROWID| chen_balance    |     1 |   103 |     2   (0)| 00:00:01 |

30------------------------------------------------------------------------------------------------

31 

32Predicate Information (identified by operation id):

33---------------------------------------------------

34 

35   3 - filter(("ACCOUNTFLAG"='1' AND "STATUS"='1' AND "SI"."VALIDDATE"<='2021/08/15'))

36   4 - access("SI"."PUTOUTNO"="LB"."PUTOUTNO")

37

查看执行计划，可以看到，即使cjcaaaaaaa_info走了全表扫描，cost也很低，预估的时间也很短。

难道是cjcaaaaaaa_info表统计信息不准确？

检查后发现表统计信息是准确的，cjcaaaaaaa_info数据量很小。

cjcaaaaaaa_info和chen_balance表关联关系很简单，where谓词条件也不复杂，那么是什么原因导致的SQL执行慢呢？

显然当前的cjcaaaaaaa_info和chen_balance关联采用NESTED LOOPS已经是最优的，尝试添加hint强制hash join速度更慢了。

既然表关联方式没问题，表访问路径没问题，还有可能哪块有问题呢？

仔细检查了SQL，发现查询的列有一处 可疑的地方：

1

2

3

4

5

6

7

8

9

10

11

12

13

......

nvl(case

               when cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15') > 0 then

                cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15')

               else

                0

             end,

             0),

         case

           when nvl(LB.assetflag, 0) = '1' and

                cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15') > 0 then

            nvl(cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15'), 0)

......

此处的cjc_fun_xxxxxxx看上去像是一个function，查看function的定义：

select dbms_metadata.get_ddl('FUNCTION','cjc_fun_xxxxxxx','CHENJ3') from dual;

函数部分由IF和ELSE两部分组成，每部分包含多个SELECT查询操作。

那么SQL执行慢，是否和cjc_fun_xxxxxxx函数有关呢？

注释掉原SQL中包含cjc_fun_xxxxxxx函数部分，再次执行SQL，速度恢复正常，不超过5分钟执行完成。

单独进行函数部分测试：

单独执行函数，速度很慢，每10秒返回100条记录

1

2

3

4

5

6

7

8

9

10

11

12

select case

         when nvl(LB.assetflag, 0) = 'https://www.fgba.net/' and

              cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15') > 0 then

          nvl(cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15'), 0)

         else

          0

       end

  from cjcaaaaaaa_info SI, chen_balance LB

 where SI.putoutno = LB.putoutno

   and SI.Validdate <= '2021/08/15'

   and Status = '1'

   and ACCOUNTFLAG = '1'

此时问题比较清晰了，就是因为cjc_fun_xxxxxxx函数部分导致SQL查询速度慢，那么为什么只有每月10号和每月15日速度慢呢？

主要是因为原SQL包含case when部分，当每月10号和每月15日时，cjc_fun_xxxxxxx函数部分执行的次数更多。

cjc_fun_xxxxxxx函数对性能究竟有多大的影响？

在满足sAssetFlag = '1'条件时，函数会执行8条select语句，并将结果集进行加和后返回。

在不满足sAssetFlag = '1'条件时，函数会执行14条select语句，并将结果集进行加和后返回。

并且除了执行的select次数不同外，执行的select语句也是不一样的，也就是在sAssetFlag值不同时，即使执行相同次数cjc_fun_xxxxxxx函数，执行时间也不同。

  综合以上两点，SQL执行时间取决于函数执行次数，和单次函数执行的逻辑有关。

例如：

在最极端的情况下，查询的每条语句都会调用4次函数，每次函数执行14个select语句，在查询60000条数据时，后台实际会执行 336万条select语句。

解决方案：

和业务人员沟通，cjc_fun_xxxxxxx函数不能在优化了，但是可以使用中间表代替。 例如，跑批前提前单独执行cjc_fun_xxxxxxx函数部分，并将结果插入到临时表t1中， 在跑批时，不需要在执行cjc_fun_xxxxxxx函数，直接和临时表t1进行关联即可， 经测试，速度有明显改善，平均耗时不超过5分钟。

总结

以上是生活随笔为你收集整理的Oracle某行系统SQL优化的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。