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

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




通过sql_monitor得到了执行计划和对应的sql语句。
可以看到执行时间是昨天的下午，一直执行到今天的凌晨。

Instance ID	:	1
Session	:	xxxxxx (6839:20499)
SQL ID	:	9yn2xdw7uy0bq
SQL Execution ID	:	16777216
Execution Started	:	09/17/2014 13:44:20
First Refresh Time	:	09/17/2014 13:44:24
Last Refresh Time	:	09/18/2014 00:05:38
Duration	:	37279s
Module/Action	:	JDBC Thin Client/-
Service	:	xxxxxxx
Program	:	JDBC Thin Client

我从之前的监控记录中抓到了之前执行时间较短的执行计划来对比分析。看看性能瓶颈到底出在哪儿。
执行时间较短的时候，sql语句的资源使用情况。

Buffer Gets	IO Requests	Database Time	Wait Activity
. 324K	. 9298	. . 40s	. . 100%

下面是执行时间过长的资源情况。

Buffer Gets	IO Requests	Database Time	Wait Activity
. 78M	. 17M	. . 37832s	. . 100%

发现瓶颈还是主要在于IO request和buffer gets. 发送的io请求翻了180多倍。buffer gets翻了240多倍。
查看执行计划，发现主要的IO消耗都在SERVICE_DETAILS表中，这个表在数据迁移后有将近1亿条记录。发送了140多万次的io请求。按照这个情况read request有大概100G的样子。确实是个很高的比值。

Id	Operation	Name	Estimated Rows	Cost	Active Period (37279s)	Execs	Rows	Memory	Temp	IO Requests	CPU Activity	Wait Activity	Progress
.	0	SELECT STATEMENT	.	.	.	.	1	.	.	.		.	.	.
.	1	. HASH UNIQUE	.	1	165K	. . .	1	0	610.0KB	.		.	.	.
.	2	.. NESTED LOOPS	.	.	.	. . .	1	26	.	.		.	.	.
.	3	... NESTED LOOPS	.	1	165K	. . .	1	338	.	.		.	.	.
.	4	.... NESTED LOOPS	.	1	165K	. . .	1	338	.	.		.	.	.
.	5	..... NESTED LOOPS	.	1	165K	. . .	1	338	.	.		.	.	.
.	6	...... NESTED LOOPS	.	1	165K	. . .	1	338	.	.		.	.	.
.	7	....... HASH JOIN	.	1	165K	. .	1	338	396.0KB	.		.	.	.
.	8	........ MAT_VIEW ACCESS FULL	OFFER	1	48	. .	1	1	.	.		.	.	.
.	9	........ NESTED LOOPS	.	.	.	. .	1	8M	.	.		.	.	.
.	10	......... NESTED LOOPS	.	2762	165K	. .	1	10M	.	.		.	.	.
.	11	.......... NESTED LOOPS	.	482	164K	. .	1	586K	.	.		.	.	.
.	12	........... NESTED LOOPS	.	482	164K	. .	1	586K	.	.		.	.	.
->	13	............ NESTED LOOPS	.	423	163K	.	1	46822	.	.		.	.	.
->	14	............. NESTED LOOPS	.	27895	133K	.	1	3M	.	.		.	.	.
->	15	.............. TABLE ACCESS FULL	ADDRESS_NAME_LINK	27857	121K	.	1	5M	.	.	51967 (.3%)	. 4.5%	.42%	949s
->	16	.............. INLIST ITERATOR	.	.	.	.	5M	3M	.	.		. 1.0%		.
->	17	............... TABLE ACCESS BY INDEX ROWID	SUBSCRIBER	1	1	.	10M	3M	.	.	. 2M (10%)	. 8.0%	. 9.3%	.
->	18	................ INDEX RANGE SCAN	SUBSCRIBER_3IX	2	1	.	10M	3M	.	.	. 1M (6.4%)	. 16%	. 5.3%	.
->	19	............. TABLE ACCESS BY INDEX ROWID	SERVICE_DETAILS	1	1	.	3M	46822	.	.	. 12M (68%)	. 52%	. . 68%	.
->	20	.............. INDEX RANGE SCAN	SERVICE_DETAILS_PK	11	1	.	3M	47M	.	.	. 2M (12%)	. 18%	. 16%	.
.	21	............ TABLE ACCESS BY INDEX ROWID	SERVICE_DETAILS	1	1	. .	46822	586K	.	.	14582 (	.	.	.

而之前的执行计划中，io请求要少的多。只有100多M的样子。

0	SELECT STATEMENT	.	.	.	.	1	.	.	.		.	.	.
.	1	. HASH UNIQUE	.	1	165K	.	1	.	.	.		.	.	.
.	2	.. NESTED LOOPS	.	.	.	.	1	.	.	.		.	.	.
.	3	... NESTED LOOPS	.	1	165K	.	1	.	.	.		.	.	.
.	4	.... NESTED LOOPS	.	1	165K	.	1	.	.	.		.	.	.
.	5	..... NESTED LOOPS	.	1	165K	.	1	.	.	.		.	.	.
.	6	...... NESTED LOOPS	.	1	165K	.	1	.	.	.		.	.	.
->	7	....... HASH JOIN	.	1	165K	. .	1	0	396.0KB	.		.	.	.
.	8	........ MAT_VIEW ACCESS FULL	OFFER	1	48	. . .	1	1	.	.		.	.	.
->	9	........ NESTED LOOPS	.	.	.	. .	1	46747	.	.		.	.	.
->	10	......... NESTED LOOPS	.	2762	165K	. .	1	83607	.	.		.	.	.
->	11	.......... NESTED LOOPS	.	482	164K	. .	1	3357	.	.		.	.	.
->	12	........... NESTED LOOPS	.	482	164K	. .	1	3357	.	.		.	.	.
->	13	............ NESTED LOOPS	.	423	163K	. .	1	257	.	.		.	.	.
->	14	............. NESTED LOOPS	.	27895	133K	. .	1	9761	.	.		.	.	.
->	15	.............. TABLE ACCESS FULL	ADDRESS_NAME_LINK	27857	121K	. .	1	19955	.	.	14 (.2%)	.	.	. 14845s
->	16	.............. INLIST ITERATOR	.	.	.	. .	19956	9761	.	.		.	.	.
->	17	............... TABLE ACCESS BY INDEX ROWID	SUBSCRIBER	1	1	. .	39911	9761	.	.	. 1276 (13%)		. 20%	.
->	18	................ INDEX RANGE SCAN	SUBSCRIBER_3IX	2	1	. .	39911	9771	.	.	64 (.7%)	.	.	.
->	19	............. TABLE ACCESS BY INDEX ROWID	SERVICE_DETAILS	1	1	. .	9762	257	.	.	. 4753 (51%)		. 42%	.
->	20	.............. INDEX RANGE SCAN	SERVICE_DETAILS_PK	11	1	. .	9762	226K	.	.	. 654 (7.0%)		. 12%	.
->	21	............ TABLE ACCESS BY INDEX ROWID	SERVICE_DETAILS	1	1	. .	257	3357	.	.	52 (.6%)		. 5.0%	.
->	22	............. INDEX RANGE SCAN	SERVICE_DETAILS_PK	11	1	. .	257	6678	.	.		.	.	.
->	23	........... TABLE ACCESS BY INDEX ROWID	CUSTOMER	1	1	. .	3357	3357	.	.	. 133 (1.4%)		. 5.0%	.
->	24	............ INDEX UNIQUE SCAN	CUSTOMER_PK	1	1	. .	3357	3357	.	.	31 (.3%)		. 5.0%	.

对应的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