在資料遷移完成之後，開始了例行的後期資料庫維護，早上一來就發現了一個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

.

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

.

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

.

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

.

46822

586K

.

14582 (<0.1%)

.

而之前的執行計劃中，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

.

->

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

.

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

.

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

.

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

.

3357

.

133 (1.4%)

.

5.0%

.

->

24

............ INDEX UNIQUE SCAN

CUSTOMER_PK

1

.

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

生產環境sql語句調優實戰第七篇

相關文章