ITPub博客

首页 > 数据库 > Oracle > Oracle某行系统SQL优化(案例五)

Oracle某行系统SQL优化(案例五)

原创 Oracle 作者:chenoracle 时间:2021-08-22 14:55:23 1 删除 编辑

问题说明:   

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

环境说明:

DB:Oracle 11.2.0.4.0 RAC
OS:AIX 7.1

问题分析:

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

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行数据,最终取出全部结果集耗时很长。

查看执行计划:

   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,发现查询的列有一处 可疑的地方:

......
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条记录

select 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)
         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分钟。

#####chenjuchao 2021-08-22 14:30#####


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2788183/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE、OCMU 用户组成员、Oracle 11g OCM、微信公众号"IT小Chen"

注册时间:2014-08-05

  • 博文量
    634
  • 访问量
    1550741