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

环境：阿里云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)|