记一次日终环境批量数据SQL优化

来源：互联网发布：stc单片机检测原理编辑：程序博客网时间：2024/05/07 14:50

环境：阿里云EC2服务器/oracle 11.2.0.1

一天下午2点多，发现alert告警，看到一条SQL执行报01555错，具体如下所示：

执行超过1831s，才执行完，结果查出来一共也就需要9w多数据，为何如此的慢

之后同事在一些表上添加了索引，勉强日终可以跑过去，但是还是比较慢

CREATE INDEX IDX_F_ACCINVESTBILL_01 ON F_ACCINVESTBILL("MANAGERID", "ACTUALPAYDATE");

CREATE INDEX IDX_F_ACCPLANCOLLECTRECEIPT_01 ON F_ACCPLANCOLLECTRECEIPT("AUDITDATE");

然后手动执行了一下这条添加过索引的SQL语句

select u.usercode,
       u.username,
       u.orgid,
       u.suborgid teamid,
       v.orgcode apporgcode,
       u.id userid,
       p.PROTYPE,
       p.PROCODE PRODUCTCODE,
       p.PRONAME PRODUCTNAME,
       nvl((select sum(b1.lendamt)
             from f_investapplyinfo a1, f_accplancollectreceipt b1
            where b1.mainapplyid = a1.id
              and a1.MANAGERID = u.id
              and b1.AUDITDATE between
                  (select case
                            when trunc(t.BUSINESSDATE, 'd') - 6 >
                                 trunc(t.BUSINESSDATE, 'mm') then
                             trunc(t.BUSINESSDATE, 'd') - 6
                            else
                             trunc(t.BUSINESSDATE, 'mm')
                          end begindate
                     from f_vsmsysdate t) and d.BUSINESSDATE
              and b1.proid = f.proid),
           0) AchieveAmt,
       (select count(1)
          FROM F_accinvestbill a
         where a.ACTUALPAYDATE between
               (select case
                         when trunc(t.BUSINESSDATE, 'd') - 6 >
                              trunc(t.BUSINESSDATE, 'mm') then
                          trunc(t.BUSINESSDATE, 'd') - 6
                         else
                          trunc(t.BUSINESSDATE, 'mm')
                       end begindate
                  from f_vsmsysdate t) and d.BUSINESSDATE
           and a.proid = f.proid
           and u.id = a.managerid) signCount
  from f_vsmuser    u,
       F_SMROLE     s,
       F_SMUSERROLE r,
       f_vsmorg     v,
       f_vsmsysdate d,
       f_smuserpro  f,
       f_vsmproduct p
 where u.id = r.userid
   and u.id = f.userid
   and f.proid = p.ID
   and r.roleid = s.id
   and s.rolecode = 'FX02'
   and v.id = u.orgid
   and u.validflag in ('0', '1')
   and r.useflag = '1'
   and p.PROTYPE in ('00', '01', '06')

执行计划如下：

93918 rows selected.
 
Elapsed: 00:02:23.17
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2387739622
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name| Rows| Bytes | Cost (%CPU)| Time|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       ||  8407 |  3661K|  3115   (1)| 00:00:38 |
|   1 |  SORT AGGREGATE        ||     1 |   160 |     ||
|   2 |   NESTED LOOPS       ||||     ||
|   3 |    NESTED LOOPS        ||     2 |   320 |    87   (0)| 00:00:02 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| F_ACCPLANCOLLECTRECEIPT|     2 |   172 |    85   (0)| 00:00:02 |
|*  5 |      INDEX RANGE SCAN       | IDX_F_ACCPLANCOLLECTRECEIPT_01 |   106 ||     2   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL        | SMSYSDATE|     1 |     8 |     3   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN       | PK_F_INVESTAPPLYINFO_ID|     1 ||     0   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID | F_INVESTAPPLYINFO|     1 |    74 |     1   (0)| 00:00:01 |
|   9 |  SORT AGGREGATE        ||     1 |    82 |     ||
|* 10 |   TABLE ACCESS BY INDEX ROWID  | F_ACCINVESTBILL|     1 |    82 |     4   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN       | IDX_F_ACCINVESTBILL_01 |     1 ||     3   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL       | SMSYSDATE|     1 |     8 |     3   (0)| 00:00:01 |
|* 13 |  HASH JOIN       ||  8407 |  3661K|  3115   (1)| 00:00:38 |
|* 14 |   TABLE ACCESS FULL       | F_SMPRODUCT|    38 |  2470 |     5   (0)| 00:00:01 |
|* 15 |   HASH JOIN       || 15245 |  5672K|  3110   (1)| 00:00:38 |
|* 16 |    HASH JOIN       ||   500 |   149K|   643   (1)| 00:00:08 |
|* 17 |     HASH JOIN       ||   500 |   127K|   622   (1)| 00:00:08 |
|* 18 |      HASH JOIN       ||   648 | 82296 |   177   (1)| 00:00:03 |
|  19 |       MERGE JOIN CARTESIAN     ||     1 |    51 |     6   (0)| 00:00:01 |
|  20 |        TABLE ACCESS FULL       | SMSYSDATE|     1 |     8 |     3   (0)| 00:00:01 |
|  21 |        BUFFER SORT       ||     1 |    43 |     3   (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL      | F_SMROLE|     1 |    43 |     3   (0)| 00:00:01 |
|* 23 |       TABLE ACCESS FULL        | F_SMUSERROLE| 28533 |  2117K|   171   (1)| 00:00:03 |
|* 24 |      TABLE ACCESS FULL       | SMUSER | 12190 |  1595K|   445   (1)| 00:00:06 |
|  25 |     INDEX FAST FULL SCAN       | IDX_SMORG_1|  4593 |   206K|    20   (0)| 00:00:01 |
|  26 |    TABLE ACCESS FULL       | F_SMUSERPRO|   494K|    34M|  2465   (1)| 00:00:30 |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("B1"."PROID"=:B1)
   5 - access("B1"."AUDITDATE">= (SELECT CASE  WHEN TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6>TRUN
      C(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') THEN TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6 ELSE
      TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') END  FROM "CREDIT"."SMSYSDATE" "SMSYSDATE") AND
      "B1"."AUDITDATE"<=:B1)
   7 - access("B1"."MAINAPPLYID"="A1"."ID")
   8 - filter("A1"."MANAGERID"=:B1)
  10 - filter("A"."PROID"=:B1)
  11 - access("A"."MANAGERID"=:B1 AND "A"."ACTUALPAYDATE">= (SELECT CASE  WHEN
      TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6>TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') THEN
      TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6 ELSE TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm')
      END  FROM "CREDIT"."SMSYSDATE" "SMSYSDATE") AND "A"."ACTUALPAYDATE"<=:B2)
  13 - access("F"."PROID"="ID")
  14 - filter("PROTYPE"='00' OR "PROTYPE"='01' OR "PROTYPE"='06')
  15 - access("ID"="F"."USERID")
  16 - access("ID"="ORGID")
  17 - access("ID"="R"."USERID")
  18 - access("R"."ROLEID"="S"."ID")
  22 - filter("S"."ROLECODE"='FX02')
  23 - filter("R"."USEFLAG"='1')
  24 - filter("VALIDFLAG"='0' OR "VALIDFLAG"='1')
 
 
Statistics
----------------------------------------------------------
 43  recursive calls
  0  db block gets
  106734311  consistent gets
924  physical reads
  0  redo size
    8206241  bytes sent via SQL*Net to client
      69390  bytes received via SQL*Net from client
       6263  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
      93918  rows processed

可以看到执行了143s，逻辑读106734311也就是1亿多逻辑读。

可见虽然添加索引优化以后，从1831s降到了143s， alert也不再报01555错，但是资源消耗可以看到很高。

然后先尝试进行将标量子查询挪下来进行改写。改写SQL如下：

select u.usercode,
       u.username,
       u.orgid,
       u.suborgid teamid,
       v.orgcode apporgcode,
       u.id userid,
       p.PROTYPE,
       p.PROCODE PRODUCTCODE,
       p.PRONAME PRODUCTNAME,
       nvl(x.sum_leadamt, 0) AchieveAmt,
       signCount
  from f_vsmuser u,
       F_SMROLE s,
       F_SMUSERROLE r,
       f_vsmorg v,
       f_vsmsysdate d,
       f_smuserpro f,
       f_vsmproduct p,
       (select a1.MANAGERID, b1.proid, sum(b1.lendamt) sum_leadamt
          from f_investapplyinfo a1, f_accplancollectreceipt b1,f_vsmsysdate d
         where b1.mainapplyid = a1.id
           and b1.AUDITDATE between
               (select case
                         when trunc(t.BUSINESSDATE, 'd') - 6 >
                              trunc(t.BUSINESSDATE, 'mm') then
                          trunc(t.BUSINESSDATE, 'd') - 6
                         else
                          trunc(t.BUSINESSDATE, 'mm')
                       end begindate
                  from f_vsmsysdate t) and d.BUSINESSDATE
         group by a1.MANAGERID, b1.proid) x,
       (select a.proid, a.managerid, count(1) signcount
          FROM F_accinvestbill a,f_vsmsysdate d
         where a.ACTUALPAYDATE between
               (select case
                         when trunc(t.BUSINESSDATE, 'd') - 6 >
                              trunc(t.BUSINESSDATE, 'mm') then
                          trunc(t.BUSINESSDATE, 'd') - 6
                         else
                          trunc(t.BUSINESSDATE, 'mm')
                       end begindate
                  from f_vsmsysdate t) and d.BUSINESSDATE
         group by a.proid, a.managerid) y
 where u.id = r.userid
   and u.id = f.userid
   and f.proid = p.ID
   and r.roleid = s.id
   and s.rolecode = 'FX02'
   and v.id = u.orgid
   and u.validflag in ('0', '1')
   and r.useflag = '1'
   and p.PROTYPE in ('00', '01', '06')
   and x.MANAGERID(+) = u.id
   and x.proid(+) = f.proid
   and y.proid(+) = f.proid
   and u.id = y.managerid(+)

然而却报了如下错;

也就是说两个标量子查询对外关联了同一张表，SQL语句无法这么写，故而报错。

C:\Users>oerr ora 1417

01417, 00000, "a table may be outer joined to at most one other table"

// *Cause: a.b (+) = b.b and a.c (+) = c.c is not allowed

// *Action: Check that this is really what you want, then join b and c first

// in a view.

然后无奈重新改写如下：

select v.usercode,
       v.username,
       v.orgid,
       v.suborgid,
       v.orgcode,
       v.id,
       v.PROTYPE,
       v.PROCODE,
       v.PRONAME,
       nvl(sum(case
             when b1.AUDITDATE <= d.BUSINESSDATE and b1.AUDITDATE >= (select case
                                 when trunc(t.BUSINESSDATE, 'd') - 6 >
                                      trunc(t.BUSINESSDATE, 'mm') then
                                  trunc(t.BUSINESSDATE, 'd') - 6
                                 else
                                  trunc(t.BUSINESSDATE, 'mm')
                               end begindate
                          from f_vsmsysdate t) then
              b1.lendamt
             else
              0
           end),0) AchieveAmt,
       count(case
               when a.ACTUALPAYDATE <= d.BUSINESSDATE and a.ACTUALPAYDATE >= (select case
                                    when trunc(t.BUSINESSDATE, 'd') - 6 >
                                         trunc(t.BUSINESSDATE, 'mm') then
                                     trunc(t.BUSINESSDATE, 'd') - 6
                                    else
                                     trunc(t.BUSINESSDATE, 'mm')
                                  end begindate
                             from f_vsmsysdate t) then
                1
               else
                0
             end) signCount
  from (select u.usercode,
               u.username,
               u.orgid,
               u.suborgid,
               v.orgcode,
               u.id,
               p.PROTYPE,
               p.PROCODE,
               p.PRONAME,
               f.proid
          from f_vsmuser    u,
               F_SMROLE     s,
               F_SMUSERROLE r,
               f_vsmorg     v,
               f_vsmsysdate d,
               f_smuserpro  f,
               f_vsmproduct p
         where u.id = r.userid
           and u.id = f.userid
           and f.proid = p.ID
           and r.roleid = s.id
           and s.rolecode = 'FX02'
           and v.id = u.orgid
           and u.validflag in ('0', '1')
           and r.useflag = '1'
           and p.PROTYPE in ('00', '01', '06')) v,
       f_vsmsysdate d,
       f_investapplyinfo a1,
       f_accplancollectreceipt b1,
       F_accinvestbill a
 where b1.mainapplyid = a1.id
   and a1.MANAGERID(+) = v.id
   and b1.proid(+) = v.proid
   and a.proid(+) = v.proid
   and a.managerid(+) = v.id   
 group by v.usercode,
          v.username,
          v.orgid,
          v.suborgid,
          v.orgcode,
          v.id,
          v.PROTYPE,
          v.PROCODE,
          v.PRONAME,
          v.proid;

但是查询出来的只有1w条数据，显然是不行的和原来的结果是不一样的。

然后继续修改，索性直接把标量子查询全盘拿下来，原来的查询结果作为一张临时视图来看，修改SQL如下：

select z.usercode,
       z.username,
       z.orgid,
       z.suborgid,
       z.orgcode,
       z.proid,
       z.id,
       z.PROTYPE,
       z.PROCODE,
       z.PRONAME,
       nvl(x.sum_leadamt, 0) AchieveAmt,
       y.signCount
  from (select u.usercode,
               u.username,
               u.orgid,
               u.suborgid,
               v.orgcode,
               f.proid,
               u.id,
               p.PROTYPE,
               p.PROCODE,
               p.PRONAME
          from f_vsmuser    u,
               F_SMROLE     s,
               F_SMUSERROLE r,
               f_vsmorg     v,
               f_vsmsysdate d,
               f_smuserpro  f,
               f_vsmproduct p
         where u.id = r.userid
           and u.id = f.userid
           and f.proid = p.ID
           and r.roleid = s.id
           and s.rolecode = 'FX02'
           and v.id = u.orgid
           and u.validflag in ('0', '1')
           and r.useflag = '1'
           and p.PROTYPE in ('00', '01', '06')) z,
       (select a1.MANAGERID, b1.proid, sum(b1.lendamt) sum_leadamt
          from f_investapplyinfo a1, f_accplancollectreceipt b1,f_vsmsysdate d
         where b1.mainapplyid = a1.id
           and b1.AUDITDATE between
               (select case
                         when trunc(t.BUSINESSDATE, 'd') - 6 >
                              trunc(t.BUSINESSDATE, 'mm') then
                          trunc(t.BUSINESSDATE, 'd') - 6
                         else
                          trunc(t.BUSINESSDATE, 'mm')
                       end begindate
                  from f_vsmsysdate t) and d.BUSINESSDATE
         group by a1.MANAGERID, b1.proid) x,
       (select a.proid, a.managerid, count(1) signcount
          FROM F_accinvestbill a,f_vsmsysdate d
         where a.ACTUALPAYDATE between
               (select case
                         when trunc(t.BUSINESSDATE, 'd') - 6 >
                              trunc(t.BUSINESSDATE, 'mm') then
                          trunc(t.BUSINESSDATE, 'd') - 6
                         else
                          trunc(t.BUSINESSDATE, 'mm')
                       end begindate
                  from f_vsmsysdate t) and d.BUSINESSDATE
         group by a.proid, a.managerid) y
 where x.MANAGERID(+) = z.id
   and x.proid(+) = z.proid
   and y.proid(+) = z.proid
   and z.id = y.managerid(+)

然后执行结果正好和原查询结果一致，对比数据也都一样，执行计划如下：

 
93918 rows selected.
 
Elapsed: 00:00:02.72
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3586458865
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation  | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |  8407 |  3357K|  5469   (1)| 00:01:06 |
|*  1 |  HASH JOIN RIGHT OUTER  |   |  8407 |  3357K|  5469   (1)| 00:01:06 |
|   2 |   VIEW  |   |  1183 |   100K|  1356   (1)| 00:00:17 |
|   3 |    HASH GROUP BY  |   |  1183 |   194K|  1356   (1)| 00:00:17 |
|   4 |     NESTED LOOPS  |   |   |   ||   |
|   5 |      NESTED LOOPS  |   |  1183 |   194K|  1352   (0)| 00:00:17 |
|   6 |       NESTED LOOPS  |   |  1183 |   108K|   169   (0)| 00:00:03 |
|   7 |        TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 | 3   (0)| 00:00:01 |
|   8 |        TABLE ACCESS BY INDEX ROWID| F_ACCPLANCOLLECTRECEIPT   |  1183 |99K|   166   (0)| 00:00:02 |
|*  9 | INDEX RANGE SCAN  | IDX_F_ACCPLANCOLLECTRECEIPT_01 |   213 |   | 1   (0)| 00:00:01 |
|  10 |  TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 | 3   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN   | PK_F_INVESTAPPLYINFO_ID   | 1 |   | 0   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID  | F_INVESTAPPLYINFO   | 1 |74 | 1   (0)| 00:00:01 |
|* 13 |   HASH JOIN RIGHT OUTER   |   |  8407 |  2643K|  4112   (1)| 00:00:50 |
|  14 |    VIEW   |   |  1182 |   100K|   998   (1)| 00:00:12 |
|  15 |     HASH GROUP BY  |   |  1182 |   103K|   998   (1)| 00:00:12 |
|  16 |      NESTED LOOPS  |   |  1182 |   103K|   994   (1)| 00:00:12 |
|  17 |       TABLE ACCESS FULL   | SMSYSDATE   | 1 | 8 | 3   (0)| 00:00:01 |
|* 18 |       TABLE ACCESS FULL   | F_ACCINVESTBILL   |  1182 | 96924 |   991   (1)| 00:00:12 |
|  19 |        TABLE ACCESS FULL  | SMSYSDATE   | 1 | 8 | 3   (0)| 00:00:01 |
|  20 |    VIEW   |   |  8407 |  1929K|  3113   (1)| 00:00:38 |
|* 21 |     HASH JOIN  |   |  8407 |  3595K|  3113   (1)| 00:00:38 |
|* 22 |      TABLE ACCESS FULL  | F_SMPRODUCT    |38 |  2470 | 5   (0)| 00:00:01 |
|* 23 |      HASH JOIN  |   | 15245 |  5553K|  3108   (1)| 00:00:38 |
|* 24 |       HASH JOIN   |   |   500 |   145K|   641   (1)| 00:00:08 |
|* 25 |        HASH JOIN  |   |   500 |   123K|   620   (1)| 00:00:08 |
|* 26 | HASH JOIN  |   |   648 | 77112 |   175   (1)| 00:00:03 |
|  27 |  MERGE JOIN CARTESIAN  |   | 1 |43 | 4   (0)| 00:00:01 |
|  28 |   INDEX FULL SCAN  | UN_SMSYSDATE_ID   | 1 |   | 1   (0)| 00:00:01 |
|  29 |   BUFFER SORT  |   | 1 |43 | 3   (0)| 00:00:01 |
|* 30 |    TABLE ACCESS FULL  | F_SMROLE   | 1 |43 | 3   (0)| 00:00:01 |
|* 31 |  TABLE ACCESS FULL  | F_SMUSERROLE   | 28533 |  2117K|   171   (1)| 00:00:03 |
|* 32 | TABLE ACCESS FULL  | SMUSER   | 12190 |  1595K|   445   (1)| 00:00:06 |
|  33 |        INDEX FAST FULL SCAN  | IDX_SMORG_1    |  4593 |   206K|20   (0)| 00:00:01 |
|  34 |       TABLE ACCESS FULL   | F_SMUSERPRO    |   494K|34M|  2465   (1)| 00:00:30 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("X"."MANAGERID"(+)="Z"."ID" AND "X"."PROID"(+)="Z"."PROID")
   9 - access("B1"."AUDITDATE">= (SELECT CASE  WHEN TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6>TRUNC(I
      NTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') THEN TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6 ELSE
      TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') END  FROM "CREDIT"."SMSYSDATE" "SMSYSDATE") AND
      "B1"."AUDITDATE"<="BUSINESSDATE")
  11 - access("B1"."MAINAPPLYID"="A1"."ID")
  13 - access("Y"."PROID"(+)="Z"."PROID" AND "Z"."ID"="Y"."MANAGERID"(+))
  18 - filter("A"."ACTUALPAYDATE"<="BUSINESSDATE" AND "A"."ACTUALPAYDATE">= (SELECT CASE  WHEN
      TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6>TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') THEN
      TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmd')-6 ELSE TRUNC(INTERNAL_FUNCTION("BUSINESSDATE"),'fmmm') END
      FROM "CREDIT"."SMSYSDATE" "SMSYSDATE"))
  21 - access("F"."PROID"="ID")
  22 - filter("PROTYPE"='00' OR "PROTYPE"='01' OR "PROTYPE"='06')
  23 - access("ID"="F"."USERID")
  24 - access("ID"="ORGID")
  25 - access("ID"="R"."USERID")
  26 - access("R"."ROLEID"="S"."ID")
  30 - filter("S"."ROLECODE"='FX02')
  31 - filter("R"."USEFLAG"='1')
  32 - filter("VALIDFLAG"='0' OR "VALIDFLAG"='1')
 
 
Statistics
----------------------------------------------------------
 57  recursive calls
  0  db block gets
      24857  consistent gets
       1234  physical reads
  0  redo size
   10767041  bytes sent via SQL*Net to client
      69390  bytes received via SQL*Net from client
       6263  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
      93918  rows processed

执行时间从143s变成了2.7s，逻辑读从1亿降到了2.4w，瞬间爆炸。

然后提交开发同事，之后等待上线观察效果。

over！

0 0