生产环境sql语句调优实战第七篇(r2笔记99天)
生产环境sql语句调优实战第七篇(r2笔记99天)
jeanron100
发布于 2018-03-14 17:34:09
发布于 2018-03-14 17:34:09
在数据迁移完成之后，开始了例行的后期数据库维护，早上一来就发现了一个sql执行时间很长了。达到了37279秒。最后在改进调优之后执行速度在1分钟以内。 这个速度是毫无疑问的性能问题，但是是否是因为数据迁移直接导致的呢，通过简单的脚本分析，得出了如下的图表。 显示了同样的sql语句在7月份至今的执行情况，还真是奇怪，昨天以前一直正常，期间也进行过两次数据迁移，昨天的数据迁移完成以后，性能就出奇的差。
通过sql_monitor得到了执行计划和对应的sql语句。 可以看到执行时间是昨天的下午，一直执行到今天的凌晨。
Instance ID | : | xxxxxx (6839:20499) |
|---|---|---|
SQL ID | : | 16777216 |
Execution Started | : | 09/17/2014 13:44:24 |
Last Refresh Time | : | 37279s |
Module/Action | : | xxxxxxx |
Program | : | <span "="" style="word-wrap: break-word;">JDBC Thin Client |
我从之前的监控记录中抓到了之前执行时间较短的执行计划来对比分析。看看性能瓶颈到底出在哪儿。
发现瓶颈还是主要在于IO request和buffer gets. 发送的io请求翻了180多倍。buffer gets翻了240多倍。
查看执行计划，发现主要的IO消耗都在SERVICE_DETAILS表中，这个表在数据迁移后有将近1亿条记录。发送了140多万次的io请求。按照这个情况read request有大概100G的样子。确实是个很高的比值。
而之前的执行计划中，io请求要少的多。只有100多M的样子。 对应的sql语句如下：
select /*+ index(s SERVICE_DETAILS_2FK) index(d CHARGE_DISTRIBUTE_1IX) */
DISTINCT t.L9_IDENTIFICATION,
rtrim(n.name_Elem2),
rtrim(n.name_Elem4),
c.BAN,
rtrim(s.PRIM_RESOURCE_VAL),
s.SUB_STATUS,
p.PAYMENT_METHOD,
rtrim(o1.SOC_NAME),
to_char(g1.EFFECTIVE_DATE, 'dd/mm/yyyy'),
to_char(g1.EXPIRATION_DATE, 'dd/mm/yyyy'),
to_char(g.EFFECTIVE_DATE, 'dd/mm/yyyy'),
to_char(g.EXPIRATION_DATE, 'dd/mm/yyyy')
from subscriber s,
PAY_CHANNEL p,
charge_distribute d,
ACCOUNT c,
address_name_link a,
name_data n,
customer t,
SERVICE_DETAILS g,
OFFER o,
SERVICE_DETAILS g1,
OFFER o1
where g.soc = o.soc_cd
and o.soc_name = 'DGT024'
and o.soc_type = 'D'
and (o.SALE_EXP_DATE is null or o.SALE_EXP_DATE > trunc(sysdate - 1))
and trunc(g.effective_date) = trunc(sysdate - 1)
and (g.expiration_date is null or g.expiration_date > trunc(sysdate - 1))
and d.agreement_no = g.agreement_no
and (d.expiration_date is null or d.expiration_date > trunc(sysdate - 1))
and p.pym_channel_no = d.target_pcn
and p.pcn_status = 'O'
and c.ban = p.ban
and s.prim_resource_tp = 'C'
and s.subscriber_no = g.agreement_no
and s.sub_status in ('A', 'S') and a.entity_id = s.customer_id
and a.entity_type = 'CUSTOMER'
and a.link_type = 'C'
and a.EXPIRATION_DATE is null
and n.name_id = a.name_id
and t.customer_id = s.customer_id
and g1.agreement_no = g.agreement_no
and (g1.expiration_date is null or
g1.expiration_date > trunc(sysdate - 1))
and o1.soc_cd = g1.soc
and o1.soc_type = 'P'
首先来分析表的关联情况，里面还需要有一定的业务知识，我有开发基础，这些业务的分析还能独立来做。最后的分析结果就是发现关联的表有11个。这么多表关联，大表小表在一起，数据库来分析可能分析不到业务的程度，我发现执行的顺序有问题，其实可以从service_details里面得到一个过滤后的数据集，过滤之后的数据就从亿条降低为几万条。在这个基础上在和其他的大表关联速度就好的多了。
所以我采用了leading的方式，提示oracle按照我指定的顺序来过滤数据。
在这个基础上毕竟service_details的过滤条件没有对应的索引列，做全表扫描也是无奈之举，但是我们还能够做点什么，来个并行。
这样速度就会提高很多。
改进后的hint如下。
/*+ leading(g,o,s,t,d,p,c,a,n,g1,o1) parallel(g,8) */
改进后的执行计划如下：
Plan hash value: 2129239343
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 232 | 55927 (1)| 00:11:12 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 232 | 55927 (1)| 00:11:12 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 1 | 232 | 55927 (1)| 00:11:12 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | | | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | | | | | Q1,01 | P->P | HASH |
| 6 | NESTED LOOPS | | | | | | Q1,01 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 232 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 8 | NESTED LOOPS | | 1 | 212 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 9 | NESTED LOOPS | | 1 | 188 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 10 | NESTED LOOPS | | 1 | 166 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 11 | NESTED LOOPS | | 2 | 282 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 12 | NESTED LOOPS | | 2 | 270 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 13 | NESTED LOOPS | | 2 | 236 | 55925 (1)| 00:11:12 | Q1,01 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 100 | 55924 (1)| 00:11:12 | Q1,01 | PCWP | |
| 15 | NESTED LOOPS | | 1 | 80 | 55924 (1)| 00:11:12 | Q1,01 | PCWP | |
|* 16 | HASH JOIN | | 1 | 52 | 55924 (1)| 00:11:12 | Q1,01 | PCWP | |
| 17 | PX BLOCK ITERATOR | | 22157 | 519K| 55870 (1)| 00:11:11 | Q1,01 | PCWC | |
|* 18 | TABLE ACCESS FULL | SERVICE_DETAILS | 22157 | 519K| 55870 (1)| 00:11:11 | Q1,01 | PCWP | |
| 19 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 20 | PX RECEIVE | | 1 | 28 | 54 (0)| 00:00:01 | Q1,01 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ10000 | 1 | 28 | 54 (0)| 00:00:01 | | S->P | BROADCAST |
|* 22 | TABLE ACCESS FULL | OFFER | 1 | 28 | 54 (0)| 00:00:01 | | | |
|* 23 | TABLE ACCESS BY INDEX ROWID| SUBSCRIBER | 1 | 28 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 24 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 25 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 20 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 26 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 27 | TABLE ACCESS BY INDEX ROWID | CHARGE_DISTRIBUTE | 6 | 108 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 28 | INDEX RANGE SCAN | CHARGE_DISTRIBUTE_1IX | 15 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 29 | TABLE ACCESS BY INDEX ROWID | PAY_CHANNEL | 1 | 17 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 30 | INDEX UNIQUE SCAN | PAY_CHANNEL_PK | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 31 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | 6 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 32 | TABLE ACCESS BY INDEX ROWID | ADDRESS_NAME_LINK | 1 | 25 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 33 | INDEX RANGE SCAN | ADDRESS_NAME_LINK_11IX | 2 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 34 | TABLE ACCESS BY INDEX ROWID | NAME_DATA | 1 | 22 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 35 | INDEX UNIQUE SCAN | NAME_DATA_PK | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 36 | TABLE ACCESS BY INDEX ROWID | SERVICE_DETAILS | 1 | 24 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 37 | INDEX RANGE SCAN | SERVICE_DETAILS_PK | 11 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 38 | INDEX RANGE SCAN | OFFER_1IX | 1 | | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 39 | TABLE ACCESS BY INDEX ROWID | OFFER | 1 | 20 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
16 - access("G"."SOC"="O"."SOC_CD")
18 - filter(("G"."EXPIRATION_DATE" IS NULL OR "G"."EXPIRATION_DATE">TRUNC(SYSDATE@!-1)) AND
TRUNC(INTERNAL_FUNCTION("G"."EFFECTIVE_DATE"))=TRUNC(SYSDATE@!-1))
22 - filter("O"."SOC_NAME"='DGT024' AND "O"."SOC_TYPE"='D' AND ("O"."SALE_EXP_DATE">TRUNC(SYSDATE@!-1) OR "O"."SALE_EXP_DATE" IS NULL))
23 - filter(("S"."SUB_STATUS"='A' OR "S"."SUB_STATUS"='S') AND "S"."PRIM_RESOURCE_TP"='C')
24 - access("S"."SUBSCRIBER_NO"="G"."AGREEMENT_NO")
26 - access("T"."CUSTOMER_ID"="S"."CUSTOMER_ID")
27 - filter("D"."EXPIRATION_DATE" IS NULL OR "D"."EXPIRATION_DATE">TRUNC(SYSDATE@!-1))
28 - access("D"."AGREEMENT_NO"="G"."AGREEMENT_NO")
29 - filter("P"."PCN_STATUS"='O')
30 - access("P"."PYM_CHANNEL_NO"="D"."TARGET_PCN")
31 - access("C"."BAN"="P"."BAN")
32 - filter("A"."EXPIRATION_DATE" IS NULL)
33 - access("A"."ENTITY_ID"="S"."CUSTOMER_ID" AND "A"."ENTITY_TYPE"='CUSTOMER' AND "A"."LINK_TYPE"='C')
35 - access("N"."NAME_ID"="A"."NAME_ID")
36 - filter("G1"."EXPIRATION_DATE" IS NULL OR "G1"."EXPIRATION_DATE">TRUNC(SYSDATE@!-1))
37 - access("G1"."AGREEMENT_NO"="G"."AGREEMENT_NO")
38 - access("O1"."SOC_CD"="G1"."SOC")
39 - filter("O1"."SOC_TYPE"='P')
69 rows selected.
无论是资源消耗还是执行时间来说，都是很大的提高。
我在测试环境做了一定的测试之后，在生产开始一个简单的测试。
执行时间要快很多，竟然不到30秒。执行2次的结果如下：
Elapsed: 00:00:21.17
Elapsed: 00:00:28.01本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2014-09-18，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读