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

我就是我 2022-09-30 03:40 84阅读 0赞

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

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

![Image 1][]![Center][]

![Image 1][]

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

![Image 1][] ![Center 1][]

![Image 1][]

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

CREATE INDEX IDX\_F\_ACCINVESTBILL\_01 ON F\_ACCINVESTBILL("MANAGERID", "ACTUALPAYDATE");

CREATE INDEX IDX\_F\_ACCPLANCOLLECTRECEIPT\_01 ON F\_ACCPLANCOLLECTRECEIPT("AUDITDATE");

![Image 1][] ![Center 2][]

![Image 1][]

然后手动执行了一下这条添加过索引的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(+)

然而却报了如下错;

![Image 1][] ![Center 3][]

![Image 1][]

也就是说两个标量子查询对外关联了同一张表，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条数据，显然是不行的和原来的结果是不一样的。

![Image 1][] ![Center 4][]

![Image 1][]

然后继续修改，索性直接把标量子查询全盘拿下来，原来的查询结果作为一张临时视图来看，修改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！

[Image 1]: 
[Center]: /images/20220708/a2d7dc1dadc7484dba0132aad5967871.png
[Center 1]: /images/20220708/bbc0e634bf704f86b37d873f2d8ec57e.png
[Center 2]: /images/20220708/3ec993ab071c4ae0a1afc359939c91f9.png
[Center 3]: /images/20220708/c32a1838c97841778a0c9917f8875604.png
[Center 4]: /images/20220708/1b0bae1ae45f44899ce1425e3204ea07.png