原創(chuàng) Oracle 作者： lovehewenyu 時(shí)間：2016-07-12 14:57:43 242
公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來(lái)驚喜。創(chuàng)新互聯(lián)推出密山免費(fèi)做網(wǎng)站回饋大家。
高效的SQL（函數(shù)索引優(yōu)化VIEW一例）
業(yè)務(wù)人員反映系統(tǒng)執(zhí)行超級(jí)慢，查看系統(tǒng)資源發(fā)現(xiàn)CPU負(fù)載已經(jīng)接近100%。挑戰(zhàn)的CASE來(lái)了，十分激動(dòng)。哈哈哈。
1.遇到性能問(wèn)題
先分析系統(tǒng)資源
，發(fā)現(xiàn)CPU負(fù)載持續(xù)100%左右。11.2.0.4 2 nodes RAC架構(gòu)，每個(gè)節(jié)點(diǎn)CPU負(fù)載都很高
System: bmcdb1 Tue Jun 28 17:17:06 2016
Load averages: 21.06, 17.79, 13.17
687 processes: 417 sleeping, 270 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 20.37 95.8% 0.0% 4.2% 0.0% 0.0% 0.0% 0.0% 0.0%
2 22.15 95.8% 0.0% 4.2% 0.0% 0.0% 0.0% 0.0% 0.0%
4 20.79 90.3% 0.0% 9.7% 0.0% 0.0% 0.0% 0.0% 0.0%
6 19.88 91.1% 0.0% 8.9% 0.0% 0.0% 0.0% 0.0% 0.0%
8 20.54 97.0% 0.0% 3.0% 0.0% 0.0% 0.0% 0.0% 0.0%
10 21.11 98.0% 0.0% 2.0% 0.0% 0.0% 0.0% 0.0% 0.0%
12 19.15 99.0% 0.0% 1.0% 0.0% 0.0% 0.0% 0.0% 0.0%
14 24.51 95.7% 0.0% 4.3% 0.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 21.06 95.4% 0.0% 4.6% 0.0% 0.0% 0.0% 0.0% 0.0%
System: bmcdb2 Tue Jun 28 17:17:26 2016
Load averages: 22.63, 18.72, 13.23
695 processes: 450 sleeping, 244 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 21.69 98.2% 0.0% 1.8% 0.0% 0.0% 0.0% 0.0% 0.0%
2 21.87 99.0% 0.0% 1.0% 0.0% 0.0% 0.0% 0.0% 0.0%
4 23.55 96.2% 0.0% 3.8% 0.0% 0.0% 0.0% 0.0% 0.0%
6 22.04 98.0% 0.0% 2.0% 0.0% 0.0% 0.0% 0.0% 0.0%
8 21.89 94.9% 0.0% 5.1% 0.0% 0.0% 0.0% 0.0% 0.0%
10 22.55 97.8% 0.0% 2.2% 0.0% 0.0% 0.0% 0.0% 0.0%
12 24.17 96.0% 0.0% 4.0% 0.0% 0.0% 0.0% 0.0% 0.0%
14 23.27 96.4% 0.0% 3.6% 0.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 22.63 97.0% 0.0% 3.0% 0.0% 0.0% 0.0% 0.0% 0.0%
2.分析AWR報(bào)告
節(jié)點(diǎn)1
Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 17049 28-Jun-16 15:00:15 241 6.7 2
End Snap: 17050 28-Jun-16 16:00:19 282 6.2 2
Elapsed: 60.08 (mins)
DB Time: 1,710.37 (mins)
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 24.2K 23.6
latch: cache buffers chains 54,799 9199.7 168 9.0 Concurrency
<==latch: cache buffers chains等待嚴(yán)重
log file sync 206,339 1927.7 9 1.9 Commit
direct path read 91,627 367 4 .4 User I/O
latch free 2,307 319 138 .3 Other
latch: row cache objects 2,775 309 111 .3 Concurrency
gc current grant busy 172,410 220.6 1 .2 Cluster
gc cr multi block request 110,803 119.9 1 .1 Cluster
reliable message 140,184 102.2 1 .1 Other
gc buffer busy acquire 13,083 99.8 8 .1 Cluster
節(jié)點(diǎn)2
Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 17049 28-Jun-16 15:00:15 244 3.1 2
End Snap: 17050 28-Jun-16 16:00:20 289 3.1 2
Elapsed: 60.08 (mins)
DB Time: 1,813.76 (mins)
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 24.1K 22.2
latch: cache buffers chains 52,429 9193.1 175 8.4 Concurrency
<==latch: cache buffers chains等待嚴(yán)重
log file sync 206,024 1777.8 9 1.6 Commit
latch: row cache objects 2,115 382.1 181 .4 Concurrency
latch free 2,191 364.7 166 .3 Other
gc buffer busy acquire 20,663 255.9 12 .2 Cluster
gc cr multi block request 153,940 245.7 2 .2 Cluster
gc cr block 2-way 109,222 169.7 2 .2 Cluster
gc current grant busy 121,973 143.7 1 .1 Cluster
gc current block 2-way 79,675 119.5 1 .1 Cluster
3.找到問(wèn)題SQL，優(yōu)化SQL減少邏輯讀
latch: cache buffers chains等待嚴(yán)重CASE處理
參考：Troubleshooting 'latch: cache buffers chains' Wait Contention (文檔 ID 1342917.1)
SQL ordered by Gets =>Segments by Logical Reads
結(jié)果找出問(wèn)題SQL
SQL_ID 68uj68brn2nvs
SQL TEXT select sum(a.N_RINGING) as
N_RINGING, sum(a.T_RINGING) as T_RINGING, sum(a.N_INBOUND) as N_INBOUND,
sum(a.T_INBOUND) as T_INBOUND, sum(a.N_TRANSFERS) as N_TRANSFERS,
sum(a.N_WORK) as N_WORK, sum(a.T_WORK) as T_WORK from V_RPT_AGENT_DAY a
where a.AGENT_ID='2193' and a.TIME_DAY='20160628'
4.優(yōu)化問(wèn)題SQL
根據(jù)AWR中的SQL_ID查詢執(zhí)行計(jì)劃
select * from table(dbms_xplan.display_awr('68uj68brn2nvs'));
SQL_ID 68uj68brn2nvs
--------------------
select sum(a.N_RINGING) as N_RINGING,sum(a.T_RINGING) as T_RINGING,
sum(a.N_INBOUND) as N_INBOUND, sum(a.T_INBOUND) as
T_INBOUND,sum(a.N_TRANSFERS) as N_TRANSFERS, sum(a.N_WORK) as N_WORK,
sum(a.T_WORK) as T_WORK from V_RPT_AGENT_DAY a where a.AGENT_ID='2193'
and a.TIME_DAY='20160628'
Plan hash value: 2468449739
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 51984 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| IVRREPORTDETAIL | 1 | 24 | 3 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | PK_CALLID | 1 | | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 125 | | |
| 4 | HASH JOIN | | 2846 | 347K| 51984 (2)| 00:10:24 |
| 5 | HASH JOIN | | 1906 | 191K| 46029 (2)| 00:09:13 |
| 6 | VIEW | V_RPT_AGENT_DAY_TEMP | 1287 | 70785 | 40095 (1)| 00:08:02 |
| 7 | HASH GROUP BY | | 1287 | 134K| 40095 (1)| 00:08:02 |
| 8 | HASH JOIN | | 1287 | 134K| 40094 (1)| 00:08:02 |
| 9 | TABLE ACCESS FULL | OBJECT | 1 | 24 | 6 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN | PK_R_21_STAT_RES | 136K| 10M| 40087 (1)| 00:08:02 |
| 11 | VIEW | V_DETAIL_TEMP | 13919 | 652K| 5934 (2)| 00:01:12 |
| 12 | HASH GROUP BY | | 13919 | 611K| 5934 (2)| 00:01:12 |
| 13 | TABLE ACCESS FULL | IVRREPORTDETAIL | 553K| 23M| 5895 (2)| 00:01:11 |
| 14 | VIEW | V_DETAIL_TEMP2 | 14036 | 301K| 5955 (3)| 00:01:12 |
| 15 | HASH GROUP BY | | 14036 | 246K| 5955 (3)| 00:01:12 |
| 16 | TABLE ACCESS FULL | IVRREPORTDETAIL | 551K| 9685K| 5916 (2)| 00:01:11 |
----------------------------------------------------------------------------------------------------
| 8 | HASH JOIN | | 1287 | 134K| 40094 (1)| 00:08:02 |
| 9 | TABLE ACCESS FULL | OBJECT | 1 | 24 | 6 (0)| 00:00:01 |
| 10 | INDEX FAST FULL SCAN | PK_R_21_STAT_RES | 136K| 10M| 40087 (1)| 00:08:02 |
注意：10行消耗COST很多，13,16行TAF都值得關(guān)注。
執(zhí)行一次語(yǔ)句收集更準(zhǔn)確的執(zhí)行計(jì)劃。
Plan hash value: 1272971961
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name | Starts | E-Rows | A-Rows | A-Time | Buffers
| OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 1 | | 1 |00:00:30.19 |
1424K| | | |
|* 1 | TABLE ACCESS BY INDEX ROWID|
IVRREPORTDETAIL | 556K| 1 | 1881 |00:00:06.76 |
1233K| | | |
|* 2 | INDEX UNIQUE SCAN |
PK_CALLID | 556K| 1 | 556K|00:00:03.22 |
676K| | | |
| 3 | SORT AGGREGATE
| | 1 | 1 | 1 |00:00:30.19 |
1424K| | | |
|* 4 | HASH JOIN
| | 1 | 21M| 1 |00:00:30.19 |
1424K| 1857K| 1857K| 5305K (0)|
| 5 | VIEW |
V_DETAIL_TEMP2 | 1 | 551K| 14066 |00:00:01.75 | 21454
| | | |
| 6 | HASH GROUP BY
| | 1 | 551K| 14066 |00:00:01.74 | 21454
| 36M| 6735K| 2658K (0)|
|* 7 | TABLE ACCESS FULL |
IVRREPORTDETAIL | 1 | 551K| 554K|00:00:00.61 | 21454
| | | |
|* 8 | HASH JOIN
| | 1 | 362K| 1 |00:00:28.43 |
1402K| 1229K| 1229K| 421K (0)|
| 9 | VIEW |
V_RPT_AGENT_DAY_TEMP | 1 | 6153 | 1 |00:00:13.00 |
148K| | | |
| 10 | HASH GROUP BY
| | 1 | 6153 | 1 |00:00:13.00 |
148K| 691K| 691K| 704K (0)|
|* 11 | HASH JOIN
| | 1 | 6153 | 96 |00:00:12.88 |
148K| 1245K| 1245K| 433K (0)|
|* 12 | TABLE ACCESS FULL |
OBJECT | 1 | 5 | 1 |00:00:00.01 | 15
| | | |
|* 13 | INDEX FAST FULL SCAN |
PK_R_21_STAT_RES | 1 | 136K| 43104 |00:00:12.94 |
148K| | | |
## E-Rows=136k與A-Rows=43104 相差4倍左右，執(zhí)行計(jì)劃值得關(guān)注 ##
| 14 | VIEW |
V_DETAIL_TEMP | 1 | 553K| 13950 |00:00:15.43 |
1254K| | | |
| 15 | HASH GROUP BY
| | 1 | 553K| 13950 |00:00:15.42 |
1254K| 59M| 4907K| 3047K (0)|
|* 16 | TABLE ACCESS FULL |
IVRREPORTDETAIL | 1 | 553K| 556K|00:00:00.60 | 21454
| | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T1"."CUSTHANGUPTIME" IS NOT NULL AND "T1"."RINGTIME" IS NOT NULL))
2 - access("T1"."CALLID"=:B1)
4 - access("C"."TIME"="A"."TIME_DAY" AND "C"."USERID"="A"."AGENT_ID")
7 - filter(("T"."TURNONTIME" IS NOT NULL AND "T"."CUSTHANGUPTIME" IS NULL))
8 - access("A"."TIME_DAY"="B"."TIME" AND "A"."AGENT_ID"="B"."USERID")
11 - access("OBJECT_ID"="O"."OBJECT_ID")
12 - filter((SUBSTR("O"."OBJECT_NAME",1,4)='2193' AND "O"."OBJECT_TYPE_ID"=0))
13 - filter(SUBSTR("TIME_KEY",1,8)='20160628')
16 - filter("T"."RINGTIME" IS NOT NULL)
## filter部分值得關(guān)注,主要優(yōu)化方法優(yōu)化函數(shù)SUBSTR值列與NULL值列 ##
## 使用INDEX RANGE SCAN來(lái)代替
INDEX FAST FULL SCAN
## 函數(shù)列可以增加函數(shù)索引,NULL值列可以添加組合索引 ##
5.分析業(yè)務(wù)SQL
5.1 根據(jù)業(yè)務(wù)人員反應(yīng)這段問(wèn)題SQL是時(shí)時(shí)更新的業(yè)務(wù)類型。
5.2
語(yǔ)句雖然簡(jiǎn)單，但是是多個(gè)VIEW嵌套而成，想優(yōu)化還需要找到基表
bmc_etl.V_RPT_AGENT_DAY 視圖包含以下表
-bmc_etl.V_RPT_AGENT_DAY_temp a,
---FROM bmc_etl.V_RPT_AGENT_NO_AGG
----bmc_etl.R_AGENT_TFSP_NO_AGG U,
------bmc_etl.R_21_STAT_RES <=基表
----bmc_etl.V_O_AGENT A
------ bmc_etl.object <=基表
-bmc_etl.v_detail_temp b,
---from cms.ivrreportdetail t<=基表
-bmc_etl.v_detail_temp2 c
---from cms.ivrreportdetail t
添加函數(shù)索引,并收集統(tǒng)計(jì)信息
12 - filter((SUBSTR("O"."OBJECT_NAME",1,4)='2193' AND "O"."OBJECT_TYPE_ID"=0))
13 - filter(SUBSTR("TIME_KEY",1,8)='20160628')
create index idx_sub_R_21 on R_21_STAT_RES (SUBSTR(TIME_KEY, 1, 8));
create index idx_sub_object on object (substr(object_name,1,4));
exec dbms_stats.gather_table_stats(user,'R_21_STAT_RES',cascade=>true);
exec dbms_stats.gather_table_stats(user,'OBJECT',cascade=>true);
Execution Plan
----------------------------------------------------------
Plan hash value: 3127161072
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 142 | 12204 (2)| 00:02:27 |
|* 1 | TABLE ACCESS BY INDEX ROWID | IVRREPORTDETAIL | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_CALLID | 1 | | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 142 | | |
|* 4 | HASH JOIN | | 14 | 1988 | 12204 (2)| 00:02:27 |
|* 5 | HASH JOIN | | 4 | 340 | 6321 (2)| 00:01:16 |
| 6 | VIEW | V_RPT_AGENT_DAY_TEMP | 1 | 54 | 401 (1)| 00:00:05 |
| 7 | HASH GROUP BY | | 1 | 125 | 401 (1)| 00:00:05 |
| 8 | NESTED LOOPS | | 37 | 4625 | 401 (1)| 00:00:05 |
|* 9 | TABLE ACCESS BY INDEX ROWID| OBJECT | 1 | 23 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_SUB_OBJECT | 1 | | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_R_21_STAT_RES | 36 | 3672 | 399 (1)| 00:00:05 |
|* 12 | INDEX RANGE SCAN | IDX_SUB_R_21 | 16739 | | 110 (0)| 00:00:02 |
| 13 | VIEW | V_DETAIL_TEMP2 | 2999 | 92969 | 5920 (2)| 00:01:12 |
| 14 | HASH GROUP BY | | 2999 | 53982 | 5920 (2)| 00:01:12 |
|* 15 | TABLE ACCESS FULL | IVRREPORTDETAIL | 4240 | 76320 | 5919 (2)| 00:01:12 |
| 16 | VIEW | V_DETAIL_TEMP | 3013 | 167K| 5883 (2)| 00:01:11 |
| 17 | HASH GROUP BY | | 3013 | 132K| 5883 (2)| 00:01:11 |
|* 18 | TABLE ACCESS FULL | IVRREPORTDETAIL | 4262 | 187K| 5881 (2)| 00:01:11 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."CUSTHANGUPTIME" IS NOT NULL AND "T1"."RINGTIME" IS NOT NULL)
2 - access("T1"."CALLID"=:B1)
4 - access("A"."TIME_DAY"="B"."TIME" AND "A"."AGENT_ID"="B"."USERID")
5 - access("C"."TIME"="A"."TIME_DAY" AND "C"."USERID"="A"."AGENT_ID")
9 - filter("O"."OBJECT_TYPE_ID"=0)
10 - access(SUBSTR("OBJECT_NAME",1,4)='2193')
11 - access(SUBSTR("TIME_KEY",1,8)='20160628')
filter("OBJECT_ID"="O"."OBJECT_ID")
12 - access(SUBSTR("TIME_KEY",1,8)='20160628')
15 - filter("T"."TURNONTIME" IS NOT NULL AND "T"."USERID"='2193' AND "T"."CUSTHANGUPTIME" IS
NULL)
18 - filter("T"."RINGTIME" IS NOT NULL AND "T"."USERID"='2193')
Statistics
----------------------------------------------------------
50 recursive calls
0 db block gets
114148 consistent gets
0 physical reads
0 redo size
969 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
## 函數(shù)索引部分已經(jīng)從filter轉(zhuǎn)成access。COST也從5W降到1W多?，F(xiàn)在優(yōu)化NULL值列部分，使用組合索引。
## 此業(yè)務(wù)SQL經(jīng)使用組合索引測(cè)試，效果不明顯。
優(yōu)化后一周后，CPU負(fù)載情況如下
System: bmcdb1 Mon Jul 4 14:49:38 2016
Load averages: 0.47, 0.47, 0.51
532 processes: 440 sleeping, 92 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.47 22.2% 0.0% 2.2% 75.6% 0.0% 0.0% 0.0% 0.0%
2 0.46 28.5% 0.0% 0.8% 70.7% 0.0% 0.0% 0.0% 0.0%
4 0.49 25.7% 0.0% 2.0% 72.3% 0.0% 0.0% 0.0% 0.0%
6 0.47 41.0% 0.0% 3.4% 55.6% 0.0% 0.0% 0.0% 0.0%
8 0.47 22.2% 0.0% 0.6% 77.2% 0.0% 0.0% 0.0% 0.0%
10 0.46 19.4% 0.0% 2.6% 78.0% 0.0% 0.0% 0.0% 0.0%
12 0.43 34.7% 0.0% 1.8% 63.6% 0.0% 0.0% 0.0% 0.0%
14 0.50 24.2% 0.0% 1.6% 74.3% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.47 27.3% 0.0% 2.0% 70.8% 0.0% 0.0% 0.0% 0.0%
總結(jié)：
1.復(fù)雜的業(yè)務(wù)類型如果想使用VIEW，請(qǐng)將核心表數(shù)據(jù)減少成"最優(yōu)"效數(shù)據(jù),也就是無(wú)關(guān)的數(shù)據(jù)都砍掉，無(wú)需關(guān)聯(lián)。并考慮數(shù)據(jù)的累積，以天/月/年為基準(zhǔn)使用有效數(shù)據(jù)。
重慶分公司
重慶分公司
