字段类型不一致导致的全表扫描

来源：互联网发布：linux ls命令编辑：程序博客网时间：2024/05/17 00:18

销售订单优化报告

监控后台语句

UPDATE T_SD_SaleOrderEntry   SET (FOrderedQty, FUnOrderedQty) =       (SELECT (ROUND(CAST((poe.FBaseQty / NVL(FBaseConvsRate,1)) AS                           NUMBER(28,16)),                     mu.FQtyPrecision) + T_SD_SaleOrderEntry.FOrderedQty) FORDEREDQTY,              (T_SD_SaleOrderEntry.FQty -               (ROUND(CAST((poe.FBaseQty /NVL(FBaseConvsRate,1)) AS                            NUMBER(28,16)),                      mu.FQtyPrecision) + T_SD_SaleOrderEntry.FOrderedQty)) FUNORDEREDQTY          FROM T_SM_PurOrderEntry POE          LEFTOUTERJOIN T_BD_MultiMeasureUnit MU            ON poe.FMaterialID = mu.FMaterialID         WHERE (poe.FSourceBillEntryID =T_SD_SaleOrderEntry.FIDAND               (((poe.FParentID IN ('LlBN+WXBSsiRN5zSo9wMfjFxv60='))AND              T_SD_SaleOrderEntry.FMaterialID = poe.FMaterialID)AND              T_SD_SaleOrderEntry.FUnitID = mu.FMeasureUnitID))) WHERE T_SD_SaleOrderEntry.FIDIN       (SELECT T_SD_SaleOrderEntry.FID          FROM T_SD_SaleOrderEntry,T_SM_PurOrderEntry POE          LEFTOUTERJOIN T_BD_MultiMeasureUnit MU            ON poe.FMaterialID = mu.FMaterialID         WHERE (poe.FSourceBillEntryID =T_SD_SaleOrderEntry.FIDAND               (((poe.FParentID IN ('LlBN+WXBSsiRN5zSo9wMfjFxv60='))AND              T_SD_SaleOrderEntry.FMaterialID = poe.FMaterialID)AND              T_SD_SaleOrderEntry.FUnitID = mu.FMeasureUnitID)))   ANDEXISTS (SELECT (ROUND(CAST((poe.FBaseQty / NVL(FBaseConvsRate,1)) AS                           NUMBER(28,16)),                     mu.FQtyPrecision) + T_SD_SaleOrderEntry.FOrderedQty) FORDEREDQTY,              (T_SD_SaleOrderEntry.FQty -               (ROUND(CAST((poe.FBaseQty /NVL(FBaseConvsRate,1)) AS                            NUMBER(28,16)),                      mu.FQtyPrecision) + T_SD_SaleOrderEntry.FOrderedQty)) FUNORDEREDQTY          FROM T_SM_PurOrderEntry POE          LEFTOUTERJOIN T_BD_MultiMeasureUnit MU            ON poe.FMaterialID = mu.FMaterialID         WHERE (poe.FSourceBillEntryID =T_SD_SaleOrderEntry.FIDAND               (((poe.FParentID IN ('LlBN+WXBSsiRN5zSo9wMfjFxv60='))AND              T_SD_SaleOrderEntry.FMaterialID = poe.FMaterialID)AND              T_SD_SaleOrderEntry.FUnitID = mu.FMeasureUnitID)))

发现IO比较高，接近5G byte。查看语句，语句本身没有问题，不应该需要这么高的IO量，进行了语句的计划分析。

ExecutionPlan

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

Planhash value: 4157953725

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

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

| 0 | SELECT STATEMENT | | 1 | 231 | 171K (1)| 00:34:23 |

| 1 | NESTED LOOPS | | 1 | 231 | 171K (1)| 00:34:23 |

|* 2 | HASH JOIN | | 1 | 173 | 171K (1)| 00:34:23 |

| 3 | TABLE ACCESS BY INDEX ROWID| T_SM_PURORDERENTRY | 8 | 688 | 6 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN |IX_SM_POEPARENT | 8 | | 2 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL |T_SD_SALEORDERENTRY | 8200K| 680M| 171K (1)| 00:34:22 |

|* 6 | INDEX RANGE SCAN |IDX_MULTIMEASUREUNIT_1 | 1 | 58 | 1 (0)| 00:00:01 |

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

PredicateInformation (identified by operation id):

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

2 -access("POE"."FSOURCEBILLENTRYID"=SYS_OP_C2C("T_SD_SALEORDERENTRY"."FID") AND

"T_SD_SALEORDERENTRY"."FMATERIALID"="POE"."FMATERIALID")

4 -access("POE"."FPARENTID"='LlBN+WXBSsiRN5zSo9wMfjFxv60=')

6 -access("POE"."FMATERIALID"="MU"."FMATERIALID"AND

"T_SD_SALEORDERENTRY"."FUNITID"="MU"."FMEASUREUNITID")

Statistics

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

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

331 bytes sent via SQL*Net to client

513 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

发现了其中一条子查询全表扫描了T_SD_SALEORDERENTRY，820万行数据量，700M的IO量。分析语句，发现执行过程中传递谓词出现系统字段转换SYS_OP_C2C access("POE"."FSOURCEBILLENTRYID"=SYS_OP_C2C("T_SD_SALEORDERENTRY"."FID")

经过对两张表的表结构进行分析，是由于FSOURCEBILLENTRYID的数据类型与"T_SD_SALEORDERENTRY"."FID"的数据类型不一致导致的。所以进行的数据类型的重新定义。优化后的语句计划如下：

ExecutionPlan

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

Planhash value: 2866515873

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

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

| 0 | SELECT STATEMENT | | 1 | 227 | 11 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 227 | 11 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 8 | 1352 | 10 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| T_SM_PURORDERENTRY | 8 | 656 | 6 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN |IX_SM_POEPARENT | 8 | | 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS BY INDEX ROWID| T_SD_SALEORDERENTRY | 1 | 87 | 1 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN |PK_SALEORDERENTRY | 1 | | 1 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN |IDX_MULTIMEASUREUNIT_1 | 1 | 58 | 1 (0)| 00:00:01 |

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

PredicateInformation (identified by operation id):

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

4 -access("POE"."FPARENTID"='LlBN+WXBSsiRN5zSo9wMfjFxv60=')

5 - filter("T_SD_SALEORDERENTRY"."FMATERIALID"="POE"."FMATERIALID")

6 -access("POE"."FSOURCEBILLENTRYID"="T_SD_SALEORDERENTRY"."FID")

7 -access("POE"."FMATERIALID"="MU"."FMATERIALID"AND

"T_SD_SALEORDERENTRY"."FUNITID"="MU"."FMEASUREUNITID")

Statistics

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

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

331 bytes sent via SQL*Net to client

513 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

新的执行计划只有87k的数据吞吐量。执行时间也由原来的30秒提升至0.01S。

这个为EAS数据库设计的问题。所有的表结构在存储外键的FFSOURCEBILLENTRYID的数据类型均为NVARCHAR类型，而存储主键的FID为VARCHAR。所以，在上下游单据联查，更新和删除的时候，会产生很大的效率问题。特别是供应链和财务模块相关大数据量的业务表。严重影响用户体验。需要逐一的进行表结构优化或者创建函数索引。

0 0