其实一直都觉得咋们ERP查询 这一块挺慢的，不过这为我学习优化SQL提供了很好的实践机会。下面来记录下我的这次优化。其实这次SQL优化成功还要源自落落博客的一文章，让我找到了优化入口。
select a.APPROVALNO,a.QUALITYSTATENAME,a.ENTIREQUANTITY,c.printcount,a.billid,a.branchid,a.pk,a.billingdate,a.prodid prodid,a.prodno prodcode,a.prodname prodname,a.prodSPECIFICATION prodspec,a.MANUFACTURE manufacturer,
a.busitypetext,a.PACKGEUNIT packunit,a.note,a.whseid,a.quantity,a.price,a.SETTLEMENTPRICE realprice,a.lotno,a.lotexpiredate,a.productdate,case when a.QUALITYSTATE = '1' then '合格' else '不合格' end acceptanceresult,
a.amount,a.QUANTITYSUM QuantitySum,a.BIGPACKAGEQUANTITY packqty,qualitystate,a.grossprofit,
CASE WHEN A.grossprofit = 0 THEN '0%' ELSE to_char(round(a.grossprofit/a.amount,4)*100,'FM9999990.99') ||'%' END grossprofitrate,e.relationtext RELATIONname
,a.STOCKOUTTASKPK,a.SALEORDERDETPK,a.CHINESEDRUGYIELDLY,COSTACCOUNTING,SettlementPrice,ReturnQuantity,
trim(a.whsename) whsename
,e.custid,e.custno,trim(e.custname) custname,c.staffid,c.staffname,
e.territories,e.territoriestext,e.MAINOPID,e.MAINOPNAME,
e.BUSINESSID,e.BUSINESSMAN,e.BIGAREAMGRID,e.BIGAREAMGR,
e.executivedeptid,e.executivedept,e.medicalorgassistcode,f.purchaser,
e.is_publichospital,e.relation,e.relationtext,e.custtype,a.realgrossprofit,e.ownerareatext
From tb_gos_sale_salestockoutdet a
inner join tb_gos_sale_salestockoutSum c on a.billid = c.billid and a.branchid = c.branchid
left join vw_common_cust e on c.customerid=e.custid and c.branchid = e.branchid
inner join vw_common_prod f on a.prodid = f.prodid and a.branchid = f.branchid
where a.billingdate between &begindate and &enddate
and (c.customerid=&CustId or &CustId is null)
and upper(a.billid) like '%' || upper(&BillId) || '%'
and c.deleteflag = 0
and (&BillerId is null or e.MAINOPID = &BillerId)
and (&BigAreaMgrId is null or &BigAreaMgrId = e.BIGAREAMGRID)
and (&BizManId is null or &BizManId = e.BUSINESSID)
and (&Relation is null or &Relation = e.relation)
and (&territories is null or &territories = e.territories)
and (a.busitypetext=&BusiType or &BusiType is null)
and e.custname like '%' || &CustName|| '%'
and ( &ArticulatedType =3 or (&ArticulatedType = 1 and e.isarticulated=1) or (&ArticulatedType = 2 and e.isarticulated<>1) );
--------用PL/SQL查，时间范围是一个月，结果只显示一页，时间61.047s
tb_gos_sale_salestockoutdet ---166 679行;
tb_gos_sale_salestockoutsum --16 424行;
vw_common_cust --851行;
vw_common_prod --- 26 895行;
执行计划：
已选择26472行。
已用时间: 00: 03: 30.95
执行计划
----------------------------------------------------------
Plan hash value: 957020064
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 218 | 110K| 752 (1)| 00:00:10 |
|* 1 | HASH JOIN | | 218 | 110K| 752 (1)| 00:00:10 |
|* 2 | HASH JOIN | | 218 | 106K| 279 (2)| 00:00:04 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TB_GOS_SALE_SALESTOCKOUTDET | 1296 | 330K| 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_SALESTOCKOUTDET_DATE | 1 | | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 2675 | 619K| 275 (2)| 00:00:04 |
|* 6 | MAT_VIEW ACCESS FULL | VW_COMMON_CUST | 42 | 7602 | 34 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | TB_GOS_SALE_SALESTOCKOUTSUM | 16090 | 879K| 240 (1)| 00:00:03 |
| 8 | MAT_VIEW ACCESS FULL | VW_COMMON_PROD | 26837 | 576K| 472 (1)| 00:00:06 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."PRODID"="F"."PRODID" AND "A"."BRANCHID"="F"."BRANCHID")
2 - access("A"."BILLID"="C"."BILLID" AND "A"."BRANCHID"="C"."BRANCHID")
3 - filter(UPPER("A"."BILLID") LIKE '%%')
4 - access(SYS_OP_DESCEND("BILLINGDATE")>=HEXTORAW('878FFAE0FEF8FEFAFF') AND
SYS_OP_DESCEND("BILLINGDATE")<=HEXTORAW('878FFAFEF8FEF8FF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))>=TO_DATE(' 2012-05-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))<=TO_DATE(' 2012-05-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
5 - access("C"."BRANCHID"="E"."BRANCHID" AND "C"."CUSTOMERID"="E"."CUSTID")
6 - filter("E"."CUSTNAME" LIKE '%%')
7 - filter("C"."DELETEFLAG"=0)
统计信息
----------------------------------------------------------
3009 recursive calls
0 db block gets
23729802 consistent gets
0 physical reads
0 redo size
5572337 bytes sent via SQL*Net to client
19764 bytes received via SQL*Net from client
1766 SQL*Net roundtrips to/from client
100 sorts (memory)
0 sorts (disk)
26472 rows processed
从执行计划中看出 consistent gets很高。且先将TB_GOS_SALE_SALESTOCKOUTSUM 和vw_common_cust 做hash，得到结果后（大约2万数据）再跟 “过滤了一部分数据”的TB_GOS_SALE_SALESTOCKOUTDET做hash,最后和prod做hash 。
select count(1) from tb_gos_sale_salestockoutdet where billingdate between to_date('2012-5-1','yyyy-mm-dd') and to_date('2012-5-30','yyyy-mm-dd');
----26472 行源SQL加了一个限定条件 and c.billingdate between &begindate and &enddate 后执行计划变了。执行计划如下：
执行计划
----------------------------------------------------------
Plan hash value: 3638730693
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 14256 | 515 (1)| 00:00:07 |
|* 1 | HASH JOIN | | 27 | 14256 | 515 (1)| 00:00:07 |
|* 2 | HASH JOIN | | 27 | 13662 | 42 (3)| 00:00:01 |
|* 3 | HASH JOIN | | 255 | 62475 | 38 (3)| 00:00:01 |
|* 4 | MAT_VIEW ACCESS FULL | VW_COMMON_CUST | 21 | 3801 | 34 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TB_GOS_SALE_SALESTOCKOUTSUM | 3064 | 191K| 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TB_SALESTOCKOUTSUM_DATEBILLID | 1 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TB_GOS_SALE_SALESTOCKOUTDET | 324 | 84564 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IX_SALESTOCKOUTDET_DATE | 1 | | 3 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS FULL | VW_COMMON_PROD | 26886 | 577K| 472 (1)| 00:00:06 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."PRODID"="F"."PRODID" AND "A"."BRANCHID"="F"."BRANCHID")
2 - access("A"."BILLID"="C"."BILLID" AND "A"."BRANCHID"="C"."BRANCHID")
3 - access("C"."CUSTOMERID"="E"."CUSTID" AND "C"."BRANCHID"="E"."BRANCHID")
4 - filter("E"."CUSTNAME" LIKE '%%' AND "E"."RELATION"=TO_NUMBER(NVL('',TO_CHAR("E"."RELATION"))))
5 - filter("C"."DELETEFLAG"=0)
6 - access(SYS_OP_DESCEND("BILLINGDATE")>=HEXTORAW('878FFAE0FEF8FEFAFF') AND
SYS_OP_DESCEND("BILLINGDATE")<=HEXTORAW('878FFAFEF8FEF8FF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))>=TO_DATE(' 2012-05-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))<=TO_DATE(' 2012-05-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter(UPPER("A"."BILLID") LIKE '%%' AND "A"."BUSITYPETEXT"=NVL('',"A"."BUSITYPETEXT"))
8 - access(SYS_OP_DESCEND("BILLINGDATE")>=HEXTORAW('878FFAE0FEF8FEFAFF') AND
SYS_OP_DESCEND("BILLINGDATE")<=HEXTORAW('878FFAFEF8FEF8FF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))>=TO_DATE(' 2012-05-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))<=TO_DATE(' 2012-05-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
768 recursive calls
0 db block gets
2391720 consistent gets
0 physical reads
0 redo size
5574104 bytes sent via SQL*Net to client
19764 bytes received via SQL*Net from client
1766 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
26472 rows processed
最近将SQL改成如下，速度上来了。
select /*+ use_hash(f,a) leading(f)*/ a.APPROVALNO,a.QUALITYSTATENAME,a.ENTIREQUANTITY,c.printcount,a.billid,a.branchid,a.pk,a.billingdate,a.prodid prodid,a.prodno prodcode,a.prodname prodname,a.prodSPECIFICATION prodspec,a.MANUFACTURE manufacturer,
a.busitypetext,a.PACKGEUNIT packunit,a.note,a.whseid,a.quantity,a.price,a.SETTLEMENTPRICE realprice,a.lotno,a.lotexpiredate,a.productdate,case when a.QUALITYSTATE = '1' then '合格' else '不合格' end acceptanceresult,
a.amount,a.QUANTITYSUM QuantitySum,a.BIGPACKAGEQUANTITY packqty,qualitystate,a.grossprofit,
CASE WHEN A.grossprofit = 0 THEN '0%' ELSE to_char(round(a.grossprofit/a.amount,4)*100,'FM9999990.99') ||'%' END grossprofitrate,e.relationtext RELATIONname
,a.STOCKOUTTASKPK,a.SALEORDERDETPK,a.CHINESEDRUGYIELDLY,COSTACCOUNTING,SettlementPrice,ReturnQuantity,
trim(a.whsename) whsename
,e.custid,e.custno,trim(e.custname) custname,c.staffid,c.staffname,
e.territories,e.territoriestext,e.MAINOPID,e.MAINOPNAME,
e.BUSINESSID,e.BUSINESSMAN,e.BIGAREAMGRID,e.BIGAREAMGR,
e.executivedeptid,e.executivedept,e.medicalorgassistcode,f.purchaser,
e.is_publichospital,e.relation,e.relationtext,e.custtype,a.realgrossprofit,e.ownerareatext
From tb_gos_sale_salestockoutdet a
inner join tb_gos_sale_salestockoutSum c on a.billid = c.billid and a.branchid = c.branchid
inner join vw_common_cust e on c.customerid=e.custid and c.branchid = e.branchid
inner join vw_common_prod f on a.prodid = f.prodid and a.branchid = f.branchid
where a.billingdate between &begindate and &enddate
and c.billingdate between &begindate and &enddate
and (c.customerid=&CustId or &CustId is null)
and upper(a.billid) like '%' || upper(&BillId) || '%'
and c.deleteflag = 0
and (&BillerId is null or e.MAINOPID = &BillerId)
and (&BigAreaMgrId is null or &BigAreaMgrId = e.BIGAREAMGRID)
and (&BizManId is null or &BizManId = e.BUSINESSID)
and e.relation =nvl(&Relation,e.relation)
and (&territories is null or &territories = e.territories)
and a.busitypetext=nvl(&BusiType,a.busitypetext)
and e.custname like '%' || &CustName|| '%'
and ( &ArticulatedType =3 or (&ArticulatedType = 1 and e.isarticulated=1) or (&ArticulatedType = 2 and e.isarticulated<>1) );
在正式库上执行 0.703s。
执行计划
----------------------------------------------------------
Plan hash value: 926049473
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 312 | 160K| 515 (1)| 00:00:07 |
|* 1 | HASH JOIN | | 312 | 160K| 515 (1)| 00:00:07 |
|* 2 | MAT_VIEW ACCESS FULL | VW_COMMON_CUST | 21 | 3801 | 34 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 324 | 109K| 481 (2)| 00:00:06 |
|* 4 | HASH JOIN | | 324 | 91692 | 477 (1)| 00:00:06 |
| 5 | MAT_VIEW ACCESS FULL | VW_COMMON_PROD | 26837 | 576K| 472 (1)| 00:00:06 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TB_GOS_SALE_SALESTOCKOUTDET | 324 | 84564 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IX_SALESTOCKOUTDET_DATE | 1 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | TB_GOS_SALE_SALESTOCKOUTSUM | 3064 | 191K| 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | TB_SALESTOCKOUTSUM_DATEBILLID | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C"."CUSTOMERID"="E"."CUSTID" AND "C"."BRANCHID"="E"."BRANCHID")
2 - filter("E"."CUSTNAME" LIKE '%%' AND "E"."RELATION"=TO_NUMBER(NVL('',TO_CHAR("E"."RELATION"))))
3 - access("A"."BILLID"="C"."BILLID" AND "A"."BRANCHID"="C"."BRANCHID")
4 - access("A"."PRODID"="F"."PRODID" AND "A"."BRANCHID"="F"."BRANCHID")
6 - filter(UPPER("A"."BILLID") LIKE '%%' AND "A"."BUSITYPETEXT"=NVL('',"A"."BUSITYPETEXT"))
7 - access(SYS_OP_DESCEND("BILLINGDATE")>=HEXTORAW('878FFAE0FEF8FEFAFF') AND
SYS_OP_DESCEND("BILLINGDATE")<=HEXTORAW('878FFAFEF8FEF8FF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))>=TO_DATE(' 2012-05-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))<=TO_DATE(' 2012-05-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - filter("C"."DELETEFLAG"=0)
9 - access(SYS_OP_DESCEND("BILLINGDATE")>=HEXTORAW('878FFAE0FEF8FEFAFF') AND
SYS_OP_DESCEND("BILLINGDATE")<=HEXTORAW('878FFAFEF8FEF8FF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))>=TO_DATE(' 2012-05-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("BILLINGDATE"))<=TO_DATE(' 2012-05-31
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
3269 recursive calls
0 db block gets
8204 consistent gets
0 physical reads
0 redo size
5732814 bytes sent via SQL*Net to client
19764 bytes received via SQL*Net from client
1766 SQL*Net roundtrips to/from client
130 sorts (memory)
0 sorts (disk)
26472 rows processed
623
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
