一个使用柱状图的案例[转]
客户的系统突然WIO超高：
09:02:23 %usr %sys %wio %idle
09:02:24 15 3 79 4
09:02:25 6 4 86 4
09:02:26 5 3 92 0
09:02:27 8 2 89 1
09:02:28 10 3 82 5
09:02:29 7 2 90 1
09:02:30 7 2 91 0
09:02:31 5 4 90 1
09:02:32 6 1 87 6
通过STATSPACK报告看到：
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 98.84 Redo NoWait %: 100.00
Buffer Hit %: 20.33 In-memory Sort %: 100.00
Library Hit %: 94.41 Soft Parse %: 89.96
Execute to Parse %: 2.39 Latch Hit %: 99.53
Parse CPU to Parse Elapsd %: 95.73 % Non-Parse CPU: 98.75
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 153,738 920 67.67
CPU time 377 27.73
buffer busy waits 4,820 26 1.91
db file parallel write 1,776 12 .86
SQL*Net message from dblink 23,712 9 .67
-------------------------------------------------------------
DB CACHE的命中率只有20%，经过检查发现有几个TOP SQL：
SQL ordered by Reads for DB: PCS Instance: pcs Snaps: 654 -655
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
369,695 14 26,406.8 21.6 26.49 143.65 1920692241
Module: 。。。 (TNS V1-V3)
select a.filename, b.source_id, b.source_path, b.localnet_
abbr from SCH_XXXXX a, source b where a.deal_flag='W' and
a.validflag='Y' and b.pipe_id=:szPipeId and a.source_id=b.sourc
e_id
类似的SQL有几个，看样子必须解决这几个SQL才能解决这个问题。看了看执行计划
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 1104374539 ----| | | 3486 |
|HASH JOIN | | 79K| 7M| 3486 |
| TABLE ACCESS BY INDEX ROWID |SOURCE | 2 | 98 | 2 |
| INDEX RANGE SCAN |FK_SOURCE_WORKFLOW | 1 | | 1 |
| TABLE ACCESS FULL |SCH_XXXXX | 1M| 61M| 3474 |
--------------------------------------------------------------------------------
SCH_XXXXX表有250万条记录。做全表扫描是不合适的。
分析WHERE条件发现，在该表上有过滤器：
a.deal_flag='W' and a.validflag='Y'
deal_flag和validflag都是状态字段，取值范围都很小，只有2、3个值。客户以前也创建过索引，不过优化器无法选择索引，因此就把索引去掉了。
通过检查发现validflag的取值绝大多数都是'Y'，而deal_flag是一个倾斜的列，绝大多数记录是'Y',只有少量记录是'W'，并且这个SQL在WHERE条件的A.DEAL_FLAG上没有使用绑定变量，因此可以在deal_flag上创建一个索引，并且通过分析柱状图来解决这个问题。
CREATE INDEX IDX_XXX_DEALFLAG ON SHCJ.SCH_XXXXX(DEAL_FLAG) ;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SHCJ', tabname=>'SCH_XXXXX', estimate_percent=>30, -
method_opt=>'for all indexed columns size skewonly',cascade=>true, degree=>4);
通过上述操作后：
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 94.03 Soft Parse %: 89.37
Execute to Parse %: 2.39 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 94.51 % Non-Parse CPU: 96.88
使用sar -d命令：
11:46:13 %usr %sys %wio %idle
11:46:14 0 0 1 99
11:46:15 6 1 2 91
11:46:16 4 0 0 96
11:46:17 1 2 2 95
11:46:18 1 0 1 98
11:46:19 15 0 12 73
11:46:20 6 1 22 71
11:46:21 1 0 5 94
11:46:22 9 0 1 90
11:46:23 1 0 0 99
Average 4 0 5 91
故障解决。这个案例可以学到以下的知识：
1、对于取值较少的字段，如果是倾斜的，而我们的查询结果是数据量较小的，这种情况，在这个字段上创建B树索引，是可以起作用的
2、索引起作用的前提是必须对该字段采集柱状图信息，并且SQL中该字段的条件不能使用绑定变量（如果使用绑定变量由于BIND PEEKING，会导致执行计划不稳定）
