今天姚工发来个语句,说gz地市的一个语句跑得很慢,1个多钟了还不出结果:
改写后:
查询结果minus一下为null,说明结果一致
总结:mdmuser.BBB t1 是视图,视图里面有笛卡尔乘积(MERGE JOIN CARTESIAN),一开始以为是这里产生的性能问题,查了视图的结果,返回数量非常少,不存在性能问题。标量子查询中的exists引起了我的注意,这里我改成了左外连接,让它走HASH JOIN RIGHT OUTER,一开始没加distinct,结果出来的记录数跟跑出来的不一样,翻倍增长,原因是mdmuser.CCC表的关联字段zqlzr_id有非常多重复的值。加完distinct之后,问题解决。
原来语句:
select /*+parallel(t,5)*/
case when to_number(to_char(t.open_date,'yyyy'))<2012 then '2012' else to_char(t.open_date,'yyyymm') end as open_date,
serv_id,divide_market_6,develop_channel_new,is_intelligence,is_fee_user,is_new_user,
case when srhx_fee/100 >=0 and srhx_fee/100<5 then 1
when srhx_fee/100 >=5 and srhx_fee/100<10 then 2
when srhx_fee/100 >=10 and srhx_fee/100<20 then 3
when srhx_fee/100 >=20 and srhx_fee/100<30 then 4
when srhx_fee/100 >=30 and srhx_fee/100<40 then 5
when srhx_fee/100 >=40 and srhx_fee/100<50 then 6
when srhx_fee/100 >=50 and srhx_fee/100<60 then 7
when srhx_fee/100 >=60 and srhx_fee/100<80 then 8
when srhx_fee/100 >=80 and srhx_fee/100<100 then 9
when srhx_fee/100 >=100 and srhx_fee/100<150 then 10
when srhx_fee/100 >=150 and srhx_fee/100<200 then 11
when srhx_fee/100 >=200 and srhx_fee/100<500 then 12
when srhx_fee/100 >=500 then 13
else -1 end as srhx_fee,
dumb_terminal_apply_type,cdma_disc_type,
case when t1.disc_fee_market >=0 and t1.disc_fee_market<10 then 1
when t1.disc_fee_market >=10 and t1.disc_fee_market<19 then 2
when t1.disc_fee_market >=19 and t1.disc_fee_market<49 then 3
when t1.disc_fee_market >=49 and t1.disc_fee_market<89 then 4
when t1.disc_fee_market >=89 and t1.disc_fee_market<189 then 5
when t1.disc_fee_market >=189 then 6
else -1 end as disc_fee_market,
is_yx,
case when SALESTAFF_ID is null or SALESTAFF_ID = '-1' then 0
else 1 end as IS_SALESTAFF,
case when exists(select 1 from mdmuser.CCC t1 where t.SALESTAFF_ID=t1.zqlzr_id) then 1
else 0 end as IS_GROUP_SALESTAFF
from SUMMARY_SJZ_GZ.AAA t,mdmuser.BBB t1 where t.CDMA_DISC_TYPE=t1.cdma_disc_type3 AND (t.is_fee_user=1 OR IS_NEW_USER=1)
;
Plan hash value: 3234892192
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24M| 4196M| 15218 (3)| 00:04:34 | | | |
|* 1 | TABLE ACCESS FULL | CCC | 667 | 8004 | 2 (0)| 00:00:01 | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 24M| 4196M| 15218 (3)| 00:04:34 | Q1,01 | P->S | QC (RAND) |
|* 4 | HASH JOIN | | 24M| 4196M| 15218 (3)| 00:04:34 | Q1,01 | PCWP | |
| 5 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 6 | PX RECEIVE | | 783 | 20358 | 13 (8)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000 | 783 | 20358 | 13 (8)| 00:00:01 | | S->P | BROADCAST |
| 8 | VIEW | BBB | 783 | 20358 | 13 (8)| 00:00:01 | | | |
| 9 | UNION-ALL | | | | | | | | |
| 10 | TABLE ACCESS FULL | BBB | 782 | 132K| 6 (0)| 00:00:01 | | | |
| 11 | HASH UNIQUE | | 1 | 206 | 7 (15)| 00:00:01 | | | |
| 12 | NESTED LOOPS ANTI | | 15 | 3090 | 6 (0)| 00:00:01 | | | |
| 13 | MERGE JOIN CARTESIAN | | 15 | 2985 | 6 (0)| 00:00:01 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| BBB | 1 | 174 | 2 (0)| 00:00:01 | | | |
|* 15 | INDEX RANGE SCAN | IXDIM_CDMA_DISC_TYPE_05 | 1 | | 1 (0)| 00:00:01 | | | |
| 16 | BUFFER SORT | | 632 | 15800 | 4 (0)| 00:00:01 | | | |
|* 17 | TABLE ACCESS FULL | TB_CFG_SUMCLASSCODE_MAPPING | 632 | 15800 | 4 (0)| 00:00:01 | | | |
|* 18 | INDEX RANGE SCAN | IXDIM_CDMA_DISC_TYPE_05 | 1 | 7 | 0 (0)| 00:00:01 | | | |
| 19 | PX BLOCK ITERATOR | | 5001K| 744M| 15187 (3)| 00:04:34 | Q1,01 | PCWC | |
|* 20 | TABLE ACCESS FULL | AAA | 5001K| 744M| 15187 (3)| 00:04:34 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."ZQLZR_ID"=:B1)
4 - access("T"."CDMA_DISC_TYPE"="T1"."CDMA_DISC_TYPE3")
14 - filter("CDMA_DISC_TYPE3_NAME"='无')
15 - access("CDMA_DISC_TYPE3"=(-1))
17 - filter("DES_FIELD"='CDMA_DISC_TYPE')
18 - access("B"."CDMA_DISC_TYPE3"=TO_NUMBER("A"."CLASS_CODE"))
20 - filter("T"."CDMA_DISC_TYPE" IS NOT NULL AND ("T"."IS_FEE_USER"=1 OR "IS_NEW_USER"=1))
Note
-----
- dynamic sampling used for this statement
改写后:
select /*+parallel(t,5)*/
case when to_number(to_char(t.open_date,'yyyy'))<2012 then '2012' else to_char(t.open_date,'yyyymm') end as open_date,
serv_id,divide_market_6,develop_channel_new,is_intelligence,is_fee_user,is_new_user,
case when srhx_fee/100 >=0 and srhx_fee/100<5 then 1
when srhx_fee/100 >=5 and srhx_fee/100<10 then 2
when srhx_fee/100 >=10 and srhx_fee/100<20 then 3
when srhx_fee/100 >=20 and srhx_fee/100<30 then 4
when srhx_fee/100 >=30 and srhx_fee/100<40 then 5
when srhx_fee/100 >=40 and srhx_fee/100<50 then 6
when srhx_fee/100 >=50 and srhx_fee/100<60 then 7
when srhx_fee/100 >=60 and srhx_fee/100<80 then 8
when srhx_fee/100 >=80 and srhx_fee/100<100 then 9
when srhx_fee/100 >=100 and srhx_fee/100<150 then 10
when srhx_fee/100 >=150 and srhx_fee/100<200 then 11
when srhx_fee/100 >=200 and srhx_fee/100<500 then 12
when srhx_fee/100 >=500 then 13
else -1 end as srhx_fee,
dumb_terminal_apply_type,
cdma_disc_type,
case when t1.disc_fee_market >=0 and t1.disc_fee_market<10 then 1
when t1.disc_fee_market >=10 and t1.disc_fee_market<19 then 2
when t1.disc_fee_market >=19 and t1.disc_fee_market<49 then 3
when t1.disc_fee_market >=49 and t1.disc_fee_market<89 then 4
when t1.disc_fee_market >=89 and t1.disc_fee_market<189 then 5
when t1.disc_fee_market >=189 then 6
else -1 end as disc_fee_market,
is_yx,
case when SALESTAFF_ID is null or SALESTAFF_ID = '-1' then 0
else 1 end as IS_SALESTAFF,
case when t3.zqlzr_id is null then 0 else 1 end as IS_GROUP_SALESTAFF
from
SUMMARY_SJZ_gz.AAA t,mdmuser.BBB t1 ,
(select distinct zqlzr_id from mdmuser.CCC) t3
where t.CDMA_DISC_TYPE=t1.cdma_disc_type3
AND (t.is_fee_user=1 OR IS_NEW_USER=1)
and t.SALESTAFF_ID=t3.zqlzr_id(+) ;
Plan hash value: 1662846629
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24M| 4472M| | 15382 (3)| 00:04:37 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 24M| 4472M| | 15382 (3)| 00:04:37 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 24M| 4472M| | 15382 (3)| 00:04:37 | Q1,02 | PCWP | |
| 4 | BUFFER SORT | | | | | | | Q1,02 | PCWC | |
| 5 | PX RECEIVE | | 783 | 20358 | | 13 (8)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 783 | 20358 | | 13 (8)| 00:00:01 | | S->P | BROADCAST |
| 7 | VIEW | BBB | 783 | 20358 | | 13 (8)| 00:00:01 | | | |
| 8 | UNION-ALL | | | | | | | | | |
| 9 | TABLE ACCESS FULL | BBB | 782 | 132K| | 6 (0)| 00:00:01 | | | |
| 10 | HASH UNIQUE | | 1 | 206 | | 7 (15)| 00:00:01 | | | |
| 11 | NESTED LOOPS ANTI | | 15 | 3090 | | 6 (0)| 00:00:01 | | | |
| 12 | MERGE JOIN CARTESIAN | | 15 | 2985 | | 6 (0)| 00:00:01 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| BBB | 1 | 174 | | 2 (0)| 00:00:01 | | | |
|* 14 | INDEX RANGE SCAN | IXDIM_CDMA_DISC_TYPE_05 | 1 | | | 1 (0)| 00:00:01 | | | |
| 15 | BUFFER SORT | | 632 | 15800 | | 4 (0)| 00:00:01 | | | |
|* 16 | TABLE ACCESS FULL | TB_CFG_SUMCLASSCODE_MAPPING | 632 | 15800 | | 4 (0)| 00:00:01 | | | |
|* 17 | INDEX RANGE SCAN | IXDIM_CDMA_DISC_TYPE_05 | 1 | 7 | | 0 (0)| 00:00:01 | | | |
|* 18 | HASH JOIN RIGHT OUTER | | 5001K| 801M| | 15351 (3)| 00:04:37 | Q1,02 | PCWP | |
| 19 | BUFFER SORT | | | | | | | Q1,02 | PCWC | |
| 20 | PX RECEIVE | | 66663 | 781K| | 157 (2)| 00:00:03 | Q1,02 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ10001 | 66663 | 781K| | 157 (2)| 00:00:03 | | S->P | BROADCAST |
| 22 | VIEW | | 66663 | 781K| | 157 (2)| 00:00:03 | | | |
| 23 | HASH UNIQUE | | 66663 | 781K| 2656K| 157 (2)| 00:00:03 | | | |
| 24 | TABLE ACCESS FULL | CCC | 66663 | 781K| | 31 (0)| 00:00:01 | | | |
| 25 | PX BLOCK ITERATOR | | 5001K| 744M| | 15190 (3)| 00:04:34 | Q1,02 | PCWC | |
|* 26 | TABLE ACCESS FULL | AAA | 5001K| 744M| | 15190 (3)| 00:04:34 | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."CDMA_DISC_TYPE"="T1"."CDMA_DISC_TYPE3")
13 - filter("CDMA_DISC_TYPE3_NAME"='无')
14 - access("CDMA_DISC_TYPE3"=(-1))
16 - filter("DES_FIELD"='CDMA_DISC_TYPE')
17 - access("B"."CDMA_DISC_TYPE3"=TO_NUMBER("A"."CLASS_CODE"))
18 - access("T"."SALESTAFF_ID"="T3"."ZQLZR_ID"(+))
26 - filter("T"."IS_FEE_USER"=1 OR "IS_NEW_USER"=1)
Note
-----
- dynamic sampling used for this statement
查询结果minus一下为null,说明结果一致
总结:mdmuser.BBB t1 是视图,视图里面有笛卡尔乘积(MERGE JOIN CARTESIAN),一开始以为是这里产生的性能问题,查了视图的结果,返回数量非常少,不存在性能问题。标量子查询中的exists引起了我的注意,这里我改成了左外连接,让它走HASH JOIN RIGHT OUTER,一开始没加distinct,结果出来的记录数跟跑出来的不一样,翻倍增长,原因是mdmuser.CCC表的关联字段zqlzr_id有非常多重复的值。加完distinct之后,问题解决。