一次调整

某局点数据库经常发生应用吊死显现，做statspack发现如下片段：

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K
… …
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 99.98
Buffer Hit %: 26.29 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 99.96
Execute to Parse %: 75.32 Latch Hit %: 99.01
Parse CPU to Parse Elapsd %: 97.28 % Non-Parse CPU: 81.91
… …
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read 338,288 2,359 71.17
db file sequential read 121,705 401 12.08
log file sync 5,693 318 9.60
CPU time 109 3.28
log file parallel write 6,446 68 2.06
-------------------------------------------------------------
… …
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
12 0.5 5,291.3 892.1 86.0 185
18 0.8 5,291.3 892.1 86.0 185
24 1.0 5,291.3 308.1 94.0 185
29 1.2 5,291.3 307.2 95.0 185
34 1.4 5,291.3 307.2 95.0 185
38 1.6 5,291.3 307.2 95.0 185
43 1.8 5,291.3 307.2 95.0 185
48 2.0 5,291.3 307.2 95.0 185
72 3.0 5,291.3 300.4 95.0 109
96 4.0 5,291.3 66.1 99.0 1
144 6.0 5,291.3 0.0 100.0 0
192 8.0 5,291.3 0.0 100.0 0
-------------------------------------------------------------

问题：
1：Buffer Cache:24M 数据库缓存设置过小
2：Shared Pool Size: 48M 共享池设置过小
3：Buffer Hit %: 26.29 数据库缓存命中率过低
4：在top 5中
db file scattered read 338,288 2,359 71.17
db file sequential read 121,705 401 12.08
排在前两位，造成离散读（scattered read）跟顺序读（sequential read）的原因是由于Buffer Cache缓存设置过小造成的。
5：pga_aggregate_target设置过小

在现场工程师按照自检文档修改过相关参数后，再次做statspack：
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 704M Std Block Size: 8K
Shared Pool Size: 256M Log Buffer: 512K
… …
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.91 In-memory Sort %: 100.00
Library Hit %: 99.96 Soft Parse %: 99.88
Execute to Parse %: 75.44 Latch Hit %: 98.39
Parse CPU to Parse Elapsd %: 96.80 % Non-Parse CPU: 72.89
… …
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
log file sync 7,174 128 45.41
CPU time 89 31.76
log file parallel write 10,925 37 13.01
db file sequential read 5,906 21 7.63
control file parallel write 348 6 2.05
-------------------------------------------------------------
… …
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
25 0.1 131.7 0.0 100.0 2
50 0.3 131.7 0.0 100.0 2
100 0.5 131.7 0.0 100.0 0
150 0.8 131.7 0.0 100.0 0
200 1.0 131.7 0.0 100.0 0
240 1.2 131.7 0.0 100.0 0
280 1.4 131.7 0.0 100.0 0
320 1.6 131.7 0.0 100.0 0
360 1.8 131.7 0.0 100.0 0
400 2.0 131.7 0.0 100.0 0
600 3.0 131.7 0.0 100.0 0
800 4.0 131.7 0.0 100.0 0
1,200 6.0 131.7 0.0 100.0 0
1,600 8.0 131.7 0.0 100.0 0
-------------------------------------------------------------

1：Buffer Hit%: 99.91 命中率上来了。
2：top 5中，scattered read事件已经消失，sequential read的等待时间由401下降为21
db file sequential read 5,906 21 7.63
3：但在top 5中发现
log file sync 7,174 128 45.41
log fle sync排在第一位，说明应用commit提交过频繁，结合statspack报告并查询应用，发现如下存储过程：xf_GetDebtNotice存在多次commit，减少非必要commit。

4：再次ststspack，发现log file sync已经由128降低为21。

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 81 68.64
log file sync 1,012 21 17.82
log file parallel write 1,634 9 7.36
control file parallel write 312 6 5.25
db file parallel write 120 1 .75

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/36754/viewspace-249021/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/36754/viewspace-249021/