原创 Oracle 作者： lovehewenyu 时间：2016-07-12 14:57:43 242
高效的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，请将核心表数据减少成"最优"效数据,也就是无关的数据都砍掉，无需关联。并考虑数据的累积，以天/月/年为基准使用有效数据。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
