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

dbhelper發表於2014-11-26

在資料遷移完成之後，開始了例行的後期資料庫維護，早上一來就發現了一個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的樣子。確實是個很高的比值。

Operation

Name

Estimated
Rows

Cost

Active Period
(37279s)

Execs

Rows

Memory

Temp

IO Requests

CPU Activity

Wait Activity

Progress

SELECT STATEMENT

. HASH UNIQUE

165K

610.0KB

.. NESTED LOOPS

... NESTED LOOPS

165K

338

.... NESTED LOOPS

165K

338

..... NESTED LOOPS

165K

338

...... NESTED LOOPS

165K

338

....... HASH JOIN

165K

338

396.0KB

........ MAT_VIEW ACCESS FULL

OFFER

........ NESTED LOOPS

......... NESTED LOOPS

2762

165K

10M

.......... NESTED LOOPS

482

164K

586K

........... NESTED LOOPS

482

164K

586K

............ NESTED LOOPS

423

163K

46822

............. NESTED LOOPS

27895

133K

.............. TABLE ACCESS FULL

ADDRESS_NAME_LINK

27857

121K

51967 (.3%)

4.5%

.42%

949s

.............. INLIST ITERATOR

1.0%

............... TABLE ACCESS BY INDEX ROWID

SUBSCRIBER

10M

2M (10%)

8.0%

9.3%

................ INDEX RANGE SCAN

SUBSCRIBER_3IX

10M

1M (6.4%)

16%

5.3%

............. TABLE ACCESS BY INDEX ROWID

SERVICE_DETAILS

46822

12M (68%)

52%

68%

.............. INDEX RANGE SCAN

SERVICE_DETAILS_PK

47M

2M (12%)

18%

16%

............ TABLE ACCESS BY INDEX ROWID

SERVICE_DETAILS

46822

586K

14582 (<0.1%)

而之前的執行計劃中，io請求要少的多。只有100多M的樣子。

SELECT STATEMENT

. HASH UNIQUE

165K

.. NESTED LOOPS

... NESTED LOOPS

165K

.... NESTED LOOPS

165K

..... NESTED LOOPS

165K

...... NESTED LOOPS

165K

....... HASH JOIN

165K

396.0KB

........ MAT_VIEW ACCESS FULL

OFFER

........ NESTED LOOPS

46747

......... NESTED LOOPS

2762

165K

83607

.......... NESTED LOOPS

482

164K

3357

........... NESTED LOOPS

482

164K

3357

............ NESTED LOOPS

423

163K

257

............. NESTED LOOPS

27895

133K

9761

.............. TABLE ACCESS FULL

ADDRESS_NAME_LINK

27857

121K

19955

14 (.2%)

14845s

.............. INLIST ITERATOR

19956

9761

............... TABLE ACCESS BY INDEX ROWID

SUBSCRIBER

39911

9761

1276 (13%)

20%

................ INDEX RANGE SCAN

SUBSCRIBER_3IX

39911

9771

64 (.7%)

............. TABLE ACCESS BY INDEX ROWID

SERVICE_DETAILS

9762

257

4753 (51%)

42%

.............. INDEX RANGE SCAN

SERVICE_DETAILS_PK

9762

226K

654 (7.0%)

12%

............ TABLE ACCESS BY INDEX ROWID

SERVICE_DETAILS

257

3357

52 (.6%)

5.0%

............. INDEX RANGE SCAN

SERVICE_DETAILS_PK

257

6678

........... TABLE ACCESS BY INDEX ROWID

CUSTOMER

3357

133 (1.4%)

5.0%

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

CUSTOMER_PK

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1273375/，如需轉載，請註明出處，否則將追究法律責任。

生產環境sql語句調優實戰第二篇
2014-11-27
SQL
生產環境sql語句調優實戰第三篇
2014-11-26
SQL
生產環境sql語句調優實戰第四篇
2014-11-26
SQL
生產環境sql語句調優實戰第五篇
2014-11-26
SQL
生產環境sql語句調優實戰第六篇
2014-11-26
SQL
生產環境sql語句調優實戰第八篇
2014-11-26
SQL
生產環境sql語句調優實戰第九篇
2014-11-26
SQL
生產環境sql語句調優實戰第十篇
2014-11-26
SQL
生產環境大型sql語句調優實戰第一篇（一）
2014-11-26
SQL
生產環境大型sql語句調優實戰第一篇（二）
2014-11-26
SQL
kafka生產環境規劃-kafka 商業環境實戰
2018-10-27
Kafka
使用hint來調優sql語句
2014-11-27
SQL
實戰生產環境vCenter HA配置（VCSA6.5）
2020-11-26
kafka生產者Producer引數設定及引數調優建議-kafka 商業環境實戰
2018-10-28
Kafka
Eureka：生產環境優化總結。
2020-11-09
優化
生產系統pl/sql調優案例
2014-11-27
SQL
MySQL調優篇 | SQL調優實戰（5）
2024-02-05
MySql
oracle 對比sql語句執行環境
2010-06-12
OracleSQL
SQL 語句調優_減少或者避免笛卡爾乘積的發生
2010-10-27
SQL
生產sql調優之統計資訊分析
2015-12-26
SQL
透過使用hint unnest調優sql語句
2015-02-24
SQL
通過使用hint unnest調優sql語句
2015-02-06
SQL
生產SQL語句突然變慢問題定位
2017-11-03
SQL
一條sql語句的建議調優分析
2015-06-19
SQL
redmine生產環境搭建
2017-12-14
Django生產環境搭建
2017-08-03
Django
SQL語句優化
2019-05-08
SQL優化
使用USE_HASH Hint調優一個SQL語句
2016-09-12
SQL
使用SQL調整顧問進行語句優化
2014-02-12
SQL優化
HBase 核心元件協調及RegionServer JVM引數調優-OLAP商業環境實戰
2018-11-27
元件ServerJVM
Vue 移動端專案生產環境的優化
2018-04-12
Vue優化
Java生產環境效能監控與調優—基於JDK命令列工具的監控
2018-10-10
JavaJDK命令列
MYSQL SQL語句優化
2019-02-22
MySql優化
sql語句效能優化
2021-01-03
SQL優化
SQL語句的優化
2017-11-14
SQL優化
求助：SQL語句優化
2009-03-03
SQL優化
SQL Server優化之SQL語句優化
2016-01-12
SQLServer優化
soar-PHP - SQL 語句優化器和重寫器的 PHP 擴充套件包、方便框架中 SQL 語句調優
2019-07-09
PHPSQL優化套件框架

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

相關文章