继续昨天的部分,上一篇的链接为:
http://blog.itpub.net/23718752/viewspace-1217012/
对这条大sql的性能瓶颈进行了分析。主要瓶颈在于一个很大的业务表,数据量在亿级。如果通过时间条件来过滤,会有5%以内的数据被过滤出来。
但是没有时间相关的索引字段,所以会走全表扫描,在目前的产品线中,这个大分区表的索引时严格控制的,所以最后经过测试和比对,还是考虑加并行来提高数据的查取速度。
--查找性能瓶颈,
根据反馈,查取的数据其实并不错,可能在几千条以内的样子。但是有很多的查询条件过滤。
如果有些大表走了索引,但是join的消耗很大,很可能就是表的查询顺序不当导致的。
有些情况下使用全表扫描的代价要比使用索引要低。
像这个例子,排查后,logical_date表中虽然有上千条记录,但是实际上使用的只有一条记录。
memo这个表是最大的表,由上亿条记录,走了索引。但是join的效率很差,根据排查,memo这个表是这个查询的关键,需要根据时间来得到前一天的数据变化。
如果根据时间来过滤,可以过滤到绝大多数的数据。
上一条记录过滤后只剩下 74811 rows selected.
如果关联配置表memo_type查询的数据就会一下子减少到1713条左右,这是对于性能极大的提升和关键。
--考虑加入并行
如果按照时间来查询,这个大表上没有和时间相关的字段,查询走全表扫描会很长,大概在5分钟左右。
--without parallel
74811 rows selected.
Elapsed: 00:03:23.10
这个时候如果只能走全表扫描,但是想使得速度能够提升,可以考虑并行,加入并行后,查询速度控制在了一分钟以内。
--add table mo1_memo_type, with parllel 8
1713 rows selected.
--加上配置表的过滤条件,查取的数据更少了,速度也有了提升。
Elapsed: 00:00:41.85
但是memo表没有时间相关的索引字段,所以会走全表扫描,在目前的产品线中,这个大分区表的索引时严格控制的,所以最后经过测试和比对,还是考虑加并行来提高数据的查取速度。
--去除笛卡尔积连接
如果是以Memo表作为首发,表的执行计划就有了很大的不同,关联时间时,会不停的去和Logical_date表做关联,其实Logical_date表里只需要一条记录,查看执行计划却走了笛卡尔积连接。
-去除笛卡尔积连接可以考虑采用with的句式,把数据先缓存起来,作为后续的查询,就避免了反复全表扫描的消耗。
可以把这段子查询抽取出来,在后续的查询中直接使用
with LO as (select logical_date from (select logical_date from logical_date
where EXPIRATION_DATE IS NULL
AND LOGICAL_DATE_TYPE = 'B'
AND EXPIRATION_DATE IS NULL)where rownum
--简化sql
可以看到sql语句中存在着很多重复的过滤条件,需要考虑在不改变业务的情况下保证语句的简单易读。
--减少/去除全表扫描
尝试减少或者去除全表扫描,保证效率。
如果通过sql monitor来监控sql语句的性能,可以发现在最后的查取中,对三个表又走了全表扫描。
SQL Plan Monitoring Details (Plan Hash Value=1239783398)
Id | Operation | Name |
Estimated Rows |
Cost |
Active Period (678s) |
Execs | Rows | Memory | Temp | IO Requests | CPU Activity | Wait Activity | Progress | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
. |
0 | SELECT STATEMENT |
. |
. |
. |
. |
1 |
. |
. |
. |
|
. |
. |
. |
|||||||||||
. |
1 | . SORT AGGREGATE |
. |
1 |
. |
|
1 | 0 |
. |
. |
|
|
|
. |
|||||||||||
-> | 2 | .. HASH JOIN |
. |
10G | 305K |
|
1 | 4G | 7.3MB |
. |
|
|
|
. |
|||||||||||
. |
3 | ... HASH JOIN |
. |
76218 | 260K |
|
1 | 90960 |
. |
. |
|
. |
. |
. |
|||||||||||
. |
4 | .... PARTITION RANGE ALL |
. |
602K | 248K |
|
1 | 449K |
. |
. |
|
. |
. |
. |
|||||||||||
. |
5 | ..... TABLE ACCESS FULL | BL1_RC_RATES | 602K | 248K |
|
11 | 449K |
. |
. |
|
|
|
|
|||||||||||
. |
6 | .... TABLE ACCESS FULL | SUBSCRIBER | 1M | 8495 |
|
1 | 1M |
. |
. |
|
. |
. |
. |
|||||||||||
-> | 7 | ... TABLE ACCESS FULL | CUSTOMER | 1M | 7441 |
|
1 | 464K |
. |
. |
|
|
|
|
|||||||||||
. |
8 | . HASH UNIQUE |
. |
1 | 469K |
. |
1 |
. |
. |
. |
|
. |
. |
. |
|||||||||||
. |
9 | .. FILTER |
. |
. |
. |
. |
1 |
. |
. |
. |
|
. |
. |
. |
|||||||||||
. |
10 | ... PX COORDINATOR |
. |
. |
. |
. |
17 |
. |
. |
. |
|
. |
如果对于这部分有所疑惑,可以参见最后select中的这段sql。
(SELECT sum(BR.AMOUNT)
FROM BL1_RC_RATES BR, CUSTOMER CU, SUBSCRIBER SS
WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO
AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID
AND BR.EFFECTIVE_DATE
AND((SS. SUB_STATUS 'C' and SS.
SUB_STATUS 'T' and BR.EXPIRATION_DATE is null)
OR (SS. SUB_STATUS = 'C' and
BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE))
AND BR.PP_IND = 'Y'
AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE,
CU.BILL_CYCLE AS CYCLE_CODE,
to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE,
to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE,
写这个sql的人是考虑在最后的数据集返回时,根据bl1_rc_rates来选择性的返回数据,但是在总查询中已经关联了customer,subscriber,在这个地方又关联就重复了!冗余的全表扫描就是因为这个导致的。
--子查询最大程度过滤结果集
可以考虑使用一些尽可能过滤较多数据的子查询来提高效率。
如果一些表的过滤条件会过滤掉大多数的数据,可以考虑子查询。
比如表product 根据soc_type来过滤会排除大多数的数据,可以使用如下的方式
( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') co来尽可能直接过滤掉最多的数据。
--观察执行计划中表的查取顺序。
做了如上的努力之后,发现还是一些全表扫描,效率貌似更差了。根据我的分析,这些表都应该走索引的。
Id | Operation | Name |
Estimated Rows |
Cost |
Active Period (235s) |
Execs | Rows | Memory | Temp | IO Requests | CPU Activity | Wait Activity | Progress | |||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
. |
0 | SELECT STATEMENT |
. |
. |
. |
. |
. |
. |
. |
. |
|
. |
. |
. |
||||||
. |
1 | . SORT AGGREGATE |
. |
1 |
. |
. |
. |
. |
. |
. |
|
. |
. |
. |
||||||
. |
2 | .. PARTITION RANGE ALL |
. |
1 | 3 |
. |
. |
. |
. |
. |
|
. |
. |
. |
||||||
. |
3 | ... TABLE ACCESS BY LOCAL INDEX ROWID | BL1_RC_RATES | 1 | 3 |
. |
. |
. |
. |
. |
|
. |
. |
. |
||||||
. |
4 | .... INDEX RANGE SCAN | BL1_RC_RATES_3IX | 8 | 2 |
. |
. |
. |
. |
. |
|
. |
. |
. |
||||||
. |
5 | . HASH UNIQUE |
. |
1 | 39T |
. |
1 |
. |
. |
. |
|
. |
. |
. |
||||||
-> | 6 | .. FILTER |
. |
. |
. |
|
1 | 0 |
. |
. |
|
|
|
. |
||||||
-> | 7 | ... PX COORDINATOR |
. |
. |
. |
|
9 | 260M |
. |
. |
|
|
|
. |
||||||
-> | 8 | .... PX SEND QC (RANDOM) | :TQ10006 | 20T | 30G |
|
8 | 260M |
. |
. |
|
|
|
. |
||||||
-> | 9 | ..... NESTED LOOPS |
. |
20T | 30G |
|
8 | 260M |
. |
. |
|
. |
. |
. |
||||||
-> | 10 | ...... HASH JOIN |
. |
30G | 96M |
|
8 | 89181 | 23.4MB |
. |
|
. |
. |
. |
||||||
. |
11 | ....... BUFFER SORT |
. |
. |
. |
|
8 | 152K |
. |
. |
|
. |
. |
. |
||||||
. |
12 | ........ PX RECEIVE |
. |
16895 | 40 |
|
8 | 152K |
. |
. |
|
. |
. |
. |
||||||
. |
13 | ......... PX SEND BROADCAST | :TQ10000 | 16895 | 40 |
|
1 | 152K |
. |
. |
|
. |
. |
. |
||||||
. |
14 | .......... MAT_VIEW ACCESS FULL | CSM_DEALER | 16895 | 40 |
|
1 | 18958 |
. |
. |
|
. |
. |
. |
||||||
-> | 15 | ....... NESTED LOOPS |
. |
31G | 96M |
|
8 | 89181 |
. |
. |
|
. |
. |
. |
||||||
-> | 16 | ........ HASH JOIN |
. |
46M | 3M |
|
8 | 26 | 498.2MB |
. |
|
|
|
. |
||||||
. |
17 | ......... BUFFER SORT |
. |
. |
. |
|
8 | 10M |
. |
. |
|
|
|
. |
||||||
. |
18 | .......... PX RECEIVE |
. |
944K | 5947 |
|
8 | 10M |
. |
. |
|
|
|
. |
||||||
. |
19 | ........... PX SEND BROADCAST | :TQ10001 | 944K | 5947 |
|
1 | 10M |
. |
. |
|
. |
. |
. |
||||||
. |
20 | ............ TABLE ACCESS FULL | ACCOUNT | 944K | 5947 |
|
1 | 1M |
. |
. |
|
|
|
. |
||||||
可以使用Hint leading来校正表的访问顺序。
/*+ leading(MO MOT SS CU CHD CPC CA ) */
最后修正后的sql语句如下:
with LO as (select logical_date from (select logical_date from logical_date
where EXPIRATION_DATE IS NULL
AND LOGICAL_DATE_TYPE = 'B'
AND EXPIRATION_DATE IS NULL)where rownum
SELECT /*+ leading(MO MOT SS CU CHD CPC CA ) */ DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2,
CU.CUST_SUB_TYPE AS ACCOUNT_TYPE,
CST.DESCRIPTION AS ACCOUNT_TYPE_DESC,
SS.PRIM_RESOURCE_VAL AS MSISDN,
CA.BAN AS BAN_KEY,
to_char(MO.MEMO_DATE, 'YYYYMMDD') AS MEMO_DATE,
CU.L9_IDENTIFICATION AS THAI_ID,
SS.SUBSCRIBER_NO AS SUBS_KEY,
SS.DEALER_CODE AS SHOP_CODE,
CD.DESCRIPTION AS SHOP_NAME,
MOT.SHORT_DESC,
REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ;]+', 1, 3) STAFF_ID,
MO.OPERATOR_ID AS USER_ID,
MO.MEMO_SYSTEM_TEXT,
CO2.SOC_NAME AS FIRST_SOCNAME,
CO3.SOC_NAME AS PREVIOUS_SOCNAME,
CO.SOC_NAME AS CURRENT_SOCNAME,
REGEXP_SUBSTR(MO.ATTR1VALUE, '[^ ; ]+', 1, 1) NAME,
CO.SOC_DESCRIPTION AS CURRENT_PP_DESC,
CO3.SOC_DESCRIPTION AS PREV_PP_DESC,
CO.SOC_CD AS SOC_CD,
(SELECT sum(BR.AMOUNT)
FROM BL1_RC_RATES BR,-- CUSTOMER CU, SUBSCRIBER SS --去除冗余的全表扫描
WHERE BR.SERVICE_RECEIVER_ID = SS.SUBSCRIBER_NO
AND BR.RECEIVER_CUSTOMER = SS.CUSTOMER_ID
AND BR.EFFECTIVE_DATE
AND((SS. SUB_STATUS 'C' and SS.
SUB_STATUS 'T' and BR.EXPIRATION_DATE is null)
OR (SS. SUB_STATUS = 'C' and
BR.EXPIRATION_DATE like SS.EFFECTIVE_DATE))
AND BR.PP_IND = 'Y'
AND BR.CYCLE_CODE = CU.BILL_CYCLE) AS PP_RATE,
CU.BILL_CYCLE AS CYCLE_CODE,
to_char(NVL(SS.L9_TMV_ACT_DATE, SS.INIT_ACT_DATE),'YYYYMMDD') AS ACTIVATED_DATE,
to_char(CD.EFFECTIVE_DATE, 'YYYYMMDD') AS SHOP_EFFECTIVE_DATE,
CD.EXPIRATION_DATE AS SHOP_EXPIRED_DATE,
CA.L9_COMPANY_CODE AS COMPANY_CODE
FROM SERVICE_DETAILS S, --大分区表,千万级数据量,存放着交易的明细信息
( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') CO, --产品配置表,大概几万条左右
CSM_PAY_CHANNEL CPC, --账务相关表,百万级
ACCOUNT CA, --账务相关表,百万级
SUBSCRIBER SS, --用户相关表,百万级
CUSTOMER CU, --用户相关表,百万级
CUSTOMER_SUB_TYPE CST, --用户配置表,几千条数据
CSM_DEALER CD, --产品配置表,大概几千条左右
SERVICE_DETAILS S2,
( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') CO2, --产品配置表,大概几万条左右
SERVICE_DETAILS S3,
( SELECT SOC_CD,SOC_NAME,SOC_DESCRIPTION FROM PRODUCT WHERE SOC_TYPE='P') CO3, --产品配置表,大概几万条左右
(select /*+ parallel(T 8)*/
MEMO_ID,ENTITY_ID,MEMO_TYPE_ID,ATTR1VALUE,OPERATOR_ID,MEMO_SYSTEM_TEXT,MEMO_DATE from
MO1_MEMO T
WHERE T.ENTITY_TYPE_ID = 6
AND TRUNC(T.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo)
) MO , --交易备注表,数据量过亿
MEMO_TYPE MOT, --配置表,数据量几千
-- LOGICAL_DATE LO, --时间配置表,数据量1千多
CHARGE_DETAILS CHD --交易表,数据量千万
WHERE SS.SUBSCRIBER_NO = CHD.AGREEMENT_NO
AND CPC.PYM_CHANNEL_NO = CHD.TARGET_PCN
AND CHD.CHG_SPLIT_TYPE = 'DR'
AND CHD.EXPIRATION_DATE IS NULL
AND S.SOC = CO.SOC_CD
AND CO.SOC_TYPE = 'P'
AND S.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND SS.PRIM_RESOURCE_TP = 'C'
AND CPC.PAYMENT_CATEGORY = 'POST'
AND CA.BAN = CPC.BAN
AND (CA.L9_COMPANY_CODE = 'RF' OR CA.L9_COMPANY_CODE = 'RM' OR
CA.L9_COMPANY_CODE = 'TM')
AND SS.CUSTOMER_ID = CU.CUSTOMER_ID
AND CU.CUST_SUB_TYPE = CST.CUST_SUB_TYPE
AND CU.CUSTOMER_TYPE = CST.CUSTOMER_TYPE
AND SS.DEALER_CODE = CD.DEALER
AND S2.EFFECTIVE_DATE= (SELECT MAX(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1--, PRODUCT o1 --去除冗余的表连接
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND co.soc_cd = sa1.soc
-- and co.soc_type = 'P'
)
AND S2.AGREEMENT_NO = S.AGREEMENT_NO
AND S2.SOC = CO2.SOC_CD
AND CO2.SOC_TYPE = 'P'
AND S2.EFFECTIVE_DATE = (SELECT MIN(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1--, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND co2.soc_cd = sa1.soc
-- and co2.soc_type = 'P'
)
AND S3.AGREEMENT_NO = S.AGREEMENT_NO
AND S3.SOC = CO3.SOC_CD
AND CO3.SOC_TYPE = 'P'
AND S3.EFFECTIVE_DATE =
(SELECT MAX(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
AND SA1.EFFECTIVE_DATE
(SELECT MAX(SA1.EFFECTIVE_DATE)
FROM SERVICE_DETAILS SA1--, PRODUCT o1
WHERE SA1.AGREEMENT_NO = SS.SUBSCRIBER_NO
and co3.soc_cd = sa1.soc
-- and co3.soc_type = 'P'
)
and co3.soc_cd = sa1.soc
--and co3.soc_type = 'P'
)
AND MO.ENTITY_ID = SS.SUBSCRIBER_NO
AND MO.ENTITY_TYPE_ID = 6
AND MO.MEMO_TYPE_ID = MOT.MEMO_TYPE_ID
-- AND TRUNC(MO.SYS_CREATION_DATE) = (select TRUNC(LO.LOGICAL_DATE - 1) from lo)
-- TRUNC(LO.LOGICAL_DATE - 1)
-- AND LO.EXPIRATION_DATE IS NULL
-- AND LO.LOGICAL_DATE_TYPE = 'B'
--AND LO.EXPIRATION_DATE IS NULL
AND (MOT.SHORT_DESC = 'BCN' OR MOT.SHORT_DESC = 'BCNM' OR
............
)
经过反复测试,速度都会保持在2分钟左右,相比40分钟和几个小时来说,绝对是性能的提升。