生产环境大型sql语句调优实战第一篇(二)

继续昨天的部分,上一篇的链接为:
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
.


.

.

17%

.

-> 2 .. HASH JOIN
.

10G 305K

.

.

1 4G 7.3MB
.

.

63%

.


.

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
.


.

.

13526 (36%)

.

1.1%

.

24%

done

.

6 .... TABLE ACCESS FULL SUBSCRIBER 1M 8495

.

.

.

1 1M
.


.


.


.


.

-> 7 ... TABLE ACCESS FULL CUSTOMER 1M 7441

.

.

1 464K
.


.

.16%

.

734s


.

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
.


.

.

3.9%

.

-> 7 ... PX COORDINATOR
.


.


.

.

.

9 260M
.


.

.

17%

.

-> 8 .... PX SEND QC (RANDOM) :TQ10006 20T 30G

.

.

8 260M
.


.

.

38%

.

-> 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
.

.

.97%

.


.

17 ......... BUFFER SORT
.


.


.

.

.

8 10M
.


.

.

.42%

.


.

18 .......... PX RECEIVE
.

944K 5947

.

.

8 10M
.


.

.14%

.


.

19 ........... PX SEND BROADCAST :TQ10001 944K 5947

.

.

.

1 10M
.


.


.


.


.


.

20 ............ TABLE ACCESS FULL ACCOUNT 944K 5947

.

.

1 1M
.


.

.

2224 (8.2%)
.28%
.27%

.

可以使用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分钟和几个小时来说,绝对是性能的提升。

时间: 2024-09-22 20:12:00

生产环境大型sql语句调优实战第一篇(二)的相关文章

生产环境大型sql语句调优实战第一篇(一)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时.sql语句比较长,需要点耐心往下看.我对表的数据量都做了简单的说明. 首先拿到sql语句,一看到关联的表有一长串,而且都是很大的表.如果性能很差,可能和走全表扫描有关,还有可能和多表关联时,表的查取顺序也有一定的关系. SELECT   DISTINCT CA.L9_CONVERGENCE_CODE AS ATB2,                 CU.CUST_SUB_TYP

生产环境sql语句调优实战第二篇

在生产环境通过sql monitor监控到有一条sql执行效率很差.执行了大约5个小时,得到的sql monitor报告如下: Global Information: EXECUTING Instance ID : 1 Buffer Gets IO Requests Database Time Wait Activity . 40M . 17M . . 8450s . . . 100% Session : PRODUSER(14:22343) SQL ID : fkzafpjs28d6d SQL

生产环境sql语句调优实战第九篇

生产环境中有一些sql语句是不定时炸弹,不声不响的运行着,可能相关的表很大,运行时间达数小时甚至数天. 上周在生产环境中发现一条sql语句,运行时间几乎是按照天来计算的.看到这种sql语句,就希望能够马上能够找到立竿见影的效果,不过欲速则不达,需要做不少工作.一定要反复验证可行. sql语句的运行情况如下: SQL Execution ID : 16777217  Execution Started : 10/18/2014 11:46:30  First Refresh Time : 10/1

生产环境sql语句调优实战第八篇

生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话.会有很大的成就感,同时如果某个sql语句执行10秒,能够优化到1秒,感觉提升的幅度不是很大,但是如果这条语句执行极为频繁的话,那这种调优还是更有成就感的. 执行时间是sql调优的一把标尺,但是同时也需要考虑到系统资源的平衡. 今天在系统中发现一条sql语句执行时间很长.平均一个查询要执行一个半小时左右,而且系统的资源消耗极大. 需要说明的service_details 是数据量过亿的表.

生产环境sql语句调优实战第六篇

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时间有1,2分钟左右,但是sql语句本身有潜在的性能问题,通过awr是定位不到的,ash尽管能够查到,但是我们在未知的情况下怎么知道问题发生的精确时间点,通过sql monitor能够查到一些实时的性能问题,但是还是需要按照自己的情况和要求来不间断地进行性能的监控.通过一个工具一劳永逸是不现实的. 今天想做数据迁移也有些日子了,看看生产环境

生产环境sql语句调优实战第七篇

在数据迁移完成之后,开始了例行的后期数据库维护,早上一来就发现了一个sql执行时间很长了.达到了37279秒.最后在改进调优之后执行速度在1分钟以内. 这个速度是毫无疑问的性能问题,但是是否是因为数据迁移直接导致的呢,通过简单的脚本分析,得出了如下的图表. 显示了同样的sql语句在7月份至今的执行情况,还真是奇怪,昨天以前一直正常,期间也进行过两次数据迁移,昨天的数据迁移完成以后,性能就出奇的差. 通过sql_monitor得到了执行计划和对应的sql语句. 可以看到执行时间是昨天的下午,一直执

生产环境sql语句调优实战第十篇

陆陆续续写了九篇关于生产环境sql语句的调优案例,发现了不少问题,可能有些问题回头来看是比较低级的错误,稍加改动就能够运行在秒级,有些可能是在秒级到毫秒级的小步提升等等,不管调优的改进多大,从dba的角度来看,好多问题都是基于资源来调优的,比如添加索引,降低IO,降低CPU消耗,提高CPU利用率等等.如果有时候从业务角度来下下功夫,可能某种程度上效果要更好于基于资源/代价的调优. 最近客户反馈有几条sql语句IO消耗很高,希望我们能够给提点建议. sql语句很短,但是运行时间在9秒左右.运行频率

生产环境sql语句调优实战第三篇

生产环境有一条sql语句执行比较频繁,占用了大量的cpu资源.原本执行需要花费11秒.在一次排查中引起了我的注意,决定看看cpu消耗到底在哪儿? sql语句是比较简单的,通过查询SUBSCRIBER_FA_V是一个视图.在视图中关联了几个和业务核心表. SELECT TO_CHAR(SUBSCRIBER_NO) SUBSCRIBER_ID,        SUB_STATUS,        SUB_STS_RSN_CD,        TO_CHAR(SUB_STATUS_DATE, 'yyy

生产环境sql语句调优实战第四篇

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session : PRODBUSER (1560:61133) SQL ID : 1hg2wcuapy3y3 SQL Execution ID : 16871963 Execution Started : 07/21/2014 12:30:20 First Refresh Time : 07/21/2014 12:30:24 Last Refresh Time : 07/21/2014 12:30:37 Duration :