高效的SQL（函式索引優化VIEW一例）
高效的SQL（函式索引優化VIEW一例）
業務人員反映系統執行超級慢，檢視系統資源發現CPU負載已經接近100%。挑戰的CASE來了，十分激動。哈哈哈。
1.遇到效能問題先分析系統資源，發現CPU負載持續100%左右。11.2.0.4 2 nodes RAC架構，每個節點CPU負載都很高
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報告
節點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等待嚴重
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
節點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等待嚴重
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.找到問題SQL，優化SQL減少邏輯讀
latch: cache buffers chains等待嚴重CASE處理
參考：Troubleshooting 'latch: cache buffers chains' Wait Contention (文件 ID 1342917.1)
SQL ordered by Gets =>Segments by Logical Reads
結果找出問題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.優化問題SQL
根據AWR中的SQL_ID查詢執行計劃
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都值得關注。
執行一次語句收集更準確的執行計劃。
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倍左右，執行計劃值得關注 ##
| 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部分值得關注,主要優化方法優化函式SUBSTR值列與NULL值列 ##
## 使用INDEX RANGE SCAN來代替INDEX FAST FULL SCAN
## 函式列可以增加函式索引,NULL值列可以新增組合索引 ##
5.分析業務SQL
5.1 根據業務人員反應這段問題SQL是時時更新的業務型別。
5.2 語句雖然簡單，但是是多個VIEW巢狀而成，想優化還需要找到基表
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
新增函式索引,並收集統計資訊
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
## 函式索引部分已經從filter轉成access。COST也從5W降到1W多。現在優化NULL值列部分，使用組合索引。
## 此業務SQL經使用組合索引測試，效果不明顯。
優化後一週後，CPU負載情況如下
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%
總結：
1.複雜的業務型別如果想使用VIEW，請將核心表資料減少成"最優"效資料,也就是無關的資料都砍掉，無需關聯。並考慮資料的累積，以天/月/年為基準使用有效資料。
########################################################################################
版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文連結：http://blog.itpub.net/26442936/viewspace-2121906/
########################################################################################
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-2121906/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化--函式索引SQL優化函式索引
- 複合索引與函式索引優化一例索引函式優化
- SQL優化案例-自定義函式索引（五）SQL優化函式索引
- MySQL函式索引及優化MySql函式索引優化
- 高效的SQL（Function-based Indexes 函式、運算優化思路）SQLFunctionIndex函式優化
- SQL優化中索引列使用函式之靈異事件SQL優化索引函式事件
- sql調優一例---索引排序hintSQL索引排序
- oracle優化一例之sql優化Oracle優化SQL
- SQL優化-索引SQL優化索引
- PL/SQL優化一例SQL優化
- Oracle優化案例-自定義函式索引（五）Oracle優化函式索引
- 函式索引使用細節——自定義函式的索引化函式索引
- SQL最佳化案例-自定義函式索引（五）SQL函式索引
- 利用函式索引，最佳化因cluster factor過高導致不走索引一例函式索引
- 聊聊索引和SQL優化索引SQL優化
- sql優化一例(index_desc)SQL優化Index
- sql優化之多列索引的使用SQL優化索引
- 「MySQL」高效能索引優化策略MySql索引優化
- 藉助索引+非空優化distinct操作一例索引優化
- SQL優化之利用索引排序SQL優化索引排序
- SQL優化（二）（聯合索引的使用）SQL優化索引
- 高效的SQL（Index unique scan最優化）SQLIndex優化
- 高效的SQL（index range scan優化排序）SQLIndex優化排序
- SQL Server 聚合函式演算法優化技巧SQLServer函式演算法優化
- sql優化案例一:使用了表示式不會使用索引SQL優化索引
- sql優化用group by 函式代替分析函式SQL優化函式
- 高效的SQL（隱式轉換導致不走索引）SQL索引
- pl/sql儲存過程優化一例SQL儲存過程優化
- [SQL] rollup & cube 分組函式用法及一例SQL函式
- SQL最佳化中索引列使用函式之靈異事件SQL索引函式事件
- Excel 優化函式Excel優化函式
- MySQL SQL 優化之覆蓋索引MySql優化索引
- SQL優化案例-正確的使用索引（二）SQL優化索引
- [zt] 基於索引的SQL語句優化索引SQL優化
- React函式式元件的效能優化React函式元件優化
- 記錄一次SQL函式和優化的問題SQL函式優化
- mysql優化 | 儲存引擎，建表，索引，sql的優化建議MySql優化儲存引擎索引
- [Oracle]高效的SQL語句之分析函式（三）OracleSQL函式