EAS优化-日收银汇总单优化

日收银汇总单在原来加上日期之后，效率有了很大的提升。但是在后台监控相关语句时，发现IO的量还是比较大。重新将相关语句进行了分析优化。

SELECT  acTbl.FParentID FParentID,

                        MIN(

                                CASE

                                       WHEN acTbl.FCardNumberID IS NULL

                                       THEN' '

                                       ELSE acTbl.FCardNumberID

                                END) FMinCdNum

                FROM    T_CYI_AccountsEntry acTbl

                        LEFT JOIN T_CYI_Accounts acTs

                        ON      acTs.fid=acTbl.FParentID

                        LEFT JOIN T_BD_SettlementTypeseTbl

                        ON      acTbl.FPaymentFashionID = seTbl.FID

                WHERE   acTs.FBizDate                  >=to_date('2014-09-08','yyyy-mm-dd')

                        AND acTs.FBizDate              <=to_date('2014-09-08','yyyy-mm-dd')

                        AND seTbl.FdeletedStatus        =1

                GROUPBY FParentID

执行语句分析，发现全表扫描了T_CYI_Accounts。原因为上次优化之后加入了日期条件，但是并未就日期进行索引。导致了全表扫描。增加索引之后，前后计划比较：

优化前：

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4174569348

 

----------------------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT        |                      |  3037 |  376K| 72155   (1)| 00:14:26 |       |      |

|   1|  HASH GROUP BY          |                      |  3037 |  376K| 72155   (1)| 00:14:26 |       |      |

|   2|   NESTED LOOPS          |                      |  3037 |  376K| 72155   (1)| 00:14:26 |       |      |

|   3|    VIEW                 | VW_GBC_9             | 3182 |   282K| 72153   (1)| 00:14:26 |       |      |

|   4|     HASH GROUP BY       |                      |  3182 |  431K| 72153   (1)| 00:14:26 |       |      |

|*  5|      HASH JOIN          |                      |  3182 |  431K| 72152   (1)| 00:14:26 |       |      |

|*  6|       TABLE ACCESS FULL |T_CYI_ACCOUNTS       |  1928 | 77120 | 31864   (1)| 00:06:23 |       |      |

|   7|       PARTITION HASH ALL|                      |  4723K|  445M| 40237   (1)| 00:08:03 |     1 |    4 |

|   8|        TABLE ACCESS FULL|T_CYI_ACCOUNTSENTRY  |  4723K|  445M| 40237   (1)| 00:08:03 |     1 |    4 |

|*  9|    INDEX RANGE SCAN     | IDX_SETTLEMENTTYPE_1 |     1 |   36 |     1   (0)| 00:00:01 |       |      |

----------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   5-access("ACTS"."FID"="ACTBL"."FPARENTID")

   6- filter("ACTS"."FBIZDATE"=TIMESTAMP' 2014-09-08 00:00:00')

   9- access("ITEM_1"="SETBL"."FID" AND"SETBL"."FDELETEDSTATUS"=1)

 

 

Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

    116405  consistent gets

    116397  physical reads

         0  redo size

       408  bytes sent via SQL*Net toclient

       513  bytes received via SQL*Netfrom client

         1  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

0        rows processed

优化后：

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2407869826

 

-------------------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT               |                      |  3189 |  454K|  1118   (1)| 00:00:14 |

|   1|  HASH GROUP BY                 |                      |  3189 |  454K|  1118   (1)| 00:00:14 |

|   2|   NESTED LOOPS                 |                      |  3189 |  454K|  1117   (0)| 00:00:14 |

|   3|    NESTED LOOPS                |                      |  3189 |  342K|  1116   (0)| 00:00:14 |

|   4|     TABLE ACCESS BY INDEX ROWID|T_CYI_ACCOUNTS       |  2105 | 84200 |    63  (0)| 00:00:01 |

|*  5|      INDEX RANGE SCAN          | IDX_CYI_ACCBIZ       | 2110 |       |     4  (0)| 00:00:01 |

|*  6|     INDEX RANGE SCAN           | IX_ACCOUNTSENTRY_1   |     2 |  140 |     1   (0)| 00:00:01 |

|*  7|    INDEX RANGE SCAN            | IDX_SETTLEMENTTYPE_1 |     1 |   36 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

   5- access("ACTS"."FBIZDATE"=TIMESTAMP' 2014-09-08 00:00:00')

   6-access("ACTS"."FID"="ACTBL"."FPARENTID")

   7- access("ACTBL"."FPAYMENTFASHIONID"="SETBL"."FID"AND "SETBL"."FDELETEDSTATUS"=1)

 

 

Statistics

----------------------------------------------------------

         1  recursive calls

         0  db block gets

      5948  consistent gets

         8  physical reads

         0  redo size

    133479  bytes sent via SQL*Net toclient

      2625  bytes received via SQL*Netfrom client

       193  SQL*Net roundtrips to/fromclient

         0  sorts (memory)

         0  sorts (disk)

      2879  rows processed

降低了大量的硬盘直接读取和IO。运行时间由9s提升至0.5S.