原创 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 IdSnap TimeSessionsCursors/SessionInstances
Begin Snap:1704928-Jun-16 15:00:152416.72
End Snap:1705028-Jun-16 16:00:192826.22
Elapsed:60.08 (mins)
DB Time:1,710.37 (mins)
Top 10 Foreground Events by Total Wait Time
EventWaitsTotal Wait Time (sec)Wait Avg(ms)% DB timeWait Class
DB CPU24.2K23.6
latch: cache buffers chains54,7999199.71689.0Concurrency <==latch: cache buffers chains等待严重
log file sync206,3391927.791.9Commit
direct path read91,6273674.4User I/O
latch free2,307319138.3Other
latch: row cache objects2,775309111.3Concurrency
gc current grant busy172,410220.61.2Cluster
gc cr multi block request110,803119.91.1Cluster
reliable message140,184102.21.1Other
gc buffer busy acquire13,08399.88.1Cluster

节点2
Snap IdSnap TimeSessionsCursors/SessionInstances
Begin Snap:1704928-Jun-16 15:00:152443.12
End Snap:1705028-Jun-16 16:00:202893.12
Elapsed:60.08 (mins)
DB Time:1,813.76 (mins)
Top 10 Foreground Events by Total Wait Time
EventWaitsTotal Wait Time (sec)Wait Avg(ms)% DB timeWait Class
DB CPU24.1K22.2
latch: cache buffers chains52,4299193.11758.4Concurrency <==latch: cache buffers chains等待严重
log file sync206,0241777.891.6Commit
latch: row cache objects2,115382.1181.4Concurrency
latch free2,191364.7166.3Other
gc buffer busy acquire20,663255.912.2Cluster
gc cr multi block request153,940245.72.2Cluster
gc cr block 2-way109,222169.72.2Cluster
gc current grant busy121,973143.71.1Cluster
gc current block 2-way79,675119.51.1Cluster

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，请将核心表数据减少成"最优"效数据,也就是无关的数据都砍掉，无需关联。并考虑数据的累积，以天/月/年为基准使用有效数据。