一次调整
上一篇 / 下一篇 2008-04-23 12:51:54 / 个人分类：Oracle Performance Tunning
某局点数据库经常发生应用吊死显现，做statspack发现如下片段：
d/@*|8?2W0ITPUB个人空间0r z4}3D _ E;N
Cache Sizes (end)
3s^v!_J8N6g z0~~~~~~~~~~~~~~~~~
J m%e'h;X0Buffer Cache: 24M Std Block Size: 8K
%g x ] OA _'Q6t|N0Shared Pool Size: 48M Log Buffer: 512K
0p([ Pf.b8qr g'u&c0… …
*|$Gz g!T[;p0Instance Efficiency Percentages (Target 100%)
a'i"j!Gu h2c7c0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*omP T+R,c | U0Buffer Nowait %: 99.98 Redo NoWait %: 99.98
i\;Ptt xZz0Buffer Hit %: 26.29 In-memory Sort %: 100.00
u7l1M b@%R0Library Hit %: 100.00 Soft Parse %: 99.96ITPUB个人空间 Gz K8b3?i
Execute to Parse %: 75.32 Latch Hit %: 99.01
:Z W _V s0Parse CPU to Parse Elapsd %: 97.28 % Non-Parse CPU: 81.91ITPUB个人空间N`3Wm(~
… …ITPUB个人空间0A(b,Q*B7V^V$N#h A q
Top 5 Timed EventsITPUB个人空间b R9n|(De
~~~~~~~~~~~~~~~~~~ % TotalITPUB个人空间(R'y y g M;_Bo
Event Waits Time (s) Ela Time
1SkI$V \r0-------------------------------------------- ------------ ----------- --------ITPUB个人空间6qa1vH$M [h e1\(n6M
db file scattered read 338,288 2,359 71.17
D't9i1t#x1p#I0db file sequential read 121,705 401 12.08ITPUB个人空间N C.{ `&c
log file sync 5,693 318 9.60ITPUB个人空间3v&k(nUv*q'm
CPU time 109 3.28ITPUB个人空间"D/n J _ hnJ
log file parallel write 6,446 68 2.06
e[1p @@xBuT0-------------------------------------------------------------
S3EY|"LP%^JG:Rs0… …
D'k m iE-{_0Estd Extra Estd PGA Estd PGA
%O+\@'PF*^+~ u K%P G:W0PGA Target Size W/A MB W/A MB Read/ Cache OverallocITPUB个人空间*nYT3P3g6^*`
Est (MB) Factr Processed Written to Disk Hit % CountITPUB个人空间5G eC N9I/G1p
---------- ------- ---------------- ---------------- -------- ----------
&}/P:k5qG2}1V012 0.5 5,291.3 892.1 86.0 185ITPUB个人空间5aPc$l-[
18 0.8 5,291.3 892.1 86.0 185ITPUB个人空间vR,ah0w
24 1.0 5,291.3 308.1 94.0 185ITPUB个人空间$@ ?!Ck6{4g p+O
29 1.2 5,291.3 307.2 95.0 185
V8Bj @P h3yR`034 1.4 5,291.3 307.2 95.0 185ITPUB个人空间S?5X2VK
38 1.6 5,291.3 307.2 95.0 185
&l.c{;k6q6]I^\ Z043 1.8 5,291.3 307.2 95.0 185
6^|eZ xV$\048 2.0 5,291.3 307.2 95.0 185ITPUB个人空间~ V rP w"l u
72 3.0 5,291.3 300.4 95.0 109
L(hd!MnI096 4.0 5,291.3 66.1 99.0 1ITPUB个人空间:@-J q c%p9G
144 6.0 5,291.3 0.0 100.0 0
+W:O D e#s5_ ^'H a P&R y0192 8.0 5,291.3 0.0 100.0 0ITPUB个人空间W6er.WP \ ]
-------------------------------------------------------------
o_;R,f%T g/Va \$C0
1E u.u3m'p a:_2V)W*k0问题：
3dW MK| U01：Buffer Cache:24M 数据库缓存设置过小
4C @0C4{ y f$}02：Shared Pool Size: 48M 共享池设置过小ITPUB个人空间7rU9^6kym%S
3：Buffer Hit %: 26.29 数据库缓存命中率过低ITPUB个人空间2z(ps ?+|8T
4：在top 5中ITPUB个人空间 uA]u Ky2U
db file scattered read 338,288 2,359 71.17ITPUB个人空间vk xx2B3`:Nn3N4P+l
db file sequential read 121,705 401 12.08
Q B$S!ah(c+Ss&D0x0排在前两位，造成离散读（scattered read）跟顺序读（sequential read）的原因是由于Buffer Cache缓存设置过小造成的。
-Z Jk ?3f.W ZJ05：pga_aggregate_target设置过小ITPUB个人空间"V%BS N3m'HU^
ITPUB个人空间z'?5k ? C9j O js l
在现场工程师按照自检文档修改过相关参数后，再次做statspack：
V2~O J;p5N y0Cache Sizes (end)ITPUB个人空间u%d |&G"M
~~~~~~~~~~~~~~~~~
!_8l|*y;D-v,H0Buffer Cache: 704M Std Block Size: 8K
3_ IX-U]U0Shared Pool Size: 256M Log Buffer: 512K
+}ws9MO5a0… …ITPUB个人空间 L7~"{,m'p
Instance Efficiency Percentages (Target 100%)ITPUB个人空间/Bo B } @'z3]}3U
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
}9Q1p+wd[J V*Sf0Buffer Nowait %: 100.00 Redo NoWait %: 100.00
ul Uqd }J u0Buffer Hit %: 99.91 In-memory Sort %: 100.00ITPUB个人空间 |PPx _R
Library Hit %: 99.96 Soft Parse %: 99.88
`#j;w)zW P{0Execute to Parse %: 75.44 Latch Hit %: 98.39ITPUB个人空间LT:bB z"|J?^
Parse CPU to Parse Elapsd %: 96.80 % Non-Parse CPU: 72.89ITPUB个人空间W.z*`uR }o[
… …
'D&{ WL*v [u%A0Top 5 Timed EventsITPUB个人空间q I.D MJ#d
~~~~~~~~~~~~~~~~~~ % Total
m&MMODz;ak0Event Waits Time (s) Ela Time
'IE ` ^U8b3]D6G0-------------------------------------------- ------------ ----------- --------ITPUB个人空间pl'Z)H w F*zX+c PR
log file sync 7,174 128 45.41
RKg7|1L l0CPU time 89 31.76ITPUB个人空间ET h"E xZ
log file parallel write 10,925 37 13.01
3}O:O.Dv TQ J K0db file sequential read 5,906 21 7.63ITPUB个人空间E&x)@X!y/] b
control file parallel write 348 6 2.05
i?P-|-V E"|2r&e0-------------------------------------------------------------
j+SMD'rN N,z0… …
GF1Ta6s0Estd Extra Estd PGA Estd PGA
|` U,],KT0PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
l*b5M g Hp0Est (MB) Factr Processed Written to Disk Hit % Count
O z(V{5}2a0---------- ------- ---------------- ---------------- -------- ----------
XtB4sJ025 0.1 131.7 0.0 100.0 2ITPUB个人空间 A2kH GM p
50 0.3 131.7 0.0 100.0 2ITPUB个人空间.J8e cxV
100 0.5 131.7 0.0 100.0 0ITPUB个人空间B6d'q]7do
150 0.8 131.7 0.0 100.0 0
![ f Ay"~!M'|'d0200 1.0 131.7 0.0 100.0 0
GL N:d/sRH0240 1.2 131.7 0.0 100.0 0
q/Q8Q w0E&Z Y_ @0280 1.4 131.7 0.0 100.0 0ITPUB个人空间 W5z#R5v1z.S7P(Cu v
320 1.6 131.7 0.0 100.0 0
"yP6M,aoP*B0360 1.8 131.7 0.0 100.0 0ITPUB个人空间4@ @ UE#i W"P
400 2.0 131.7 0.0 100.0 0
1f0Y)HV x"x{I0600 3.0 131.7 0.0 100.0 0ITPUB个人空间#U `i/O:Y,~ n4W
800 4.0 131.7 0.0 100.0 0
%W-j`r^ s01,200 6.0 131.7 0.0 100.0 0
h4d$?2J l_2e?2_b01,600 8.0 131.7 0.0 100.0 0ITPUB个人空间Hh2FW/gI J i
-------------------------------------------------------------ITPUB个人空间.\ ~pg c]W(u
ITPUB个人空间8j6x"`-XT k:`&{ K
1：Buffer Hit%: 99.91 命中率上来了。
0G{y ?GH02：top 5中，scattered read事件已经消失，sequential read的等待时间由401下降为21ITPUB个人空间1K s;V2p Y&J&F
db file sequential read 5,906 21 7.63ITPUB个人空间 ^8M8@M+_:C@t p
3：但在top 5中发现
`6uXa ~x0log file sync 7,174 128 45.41
)RL r^!n0log fle sync排在第一位，说明应用commit提交过频繁，结合statspack报告并查询应用，发现如下存储过程：xf_GetDebtNotice存在多次commit，减少非必要commit。
Y9dJcuk*]%S Z0
.zre7}/P![f%m,K*B*k2a ~04：再次ststspack，发现log file sync已经由128降低为21。
l?-Z9s;f8O(Y-U6lH0
JO r"Z XD0Top 5 Timed EventsITPUB个人空间%`T,u](_|d"I0U m
~~~~~~~~~~~~~~~~~~ % Total
.w u;U1j'aBF0Event Waits Time (s) Ela Time
&WD(B"XU$Lq.T7t^6F0-------------------------------------------- ------------ ----------- --------ITPUB个人空间%\B'r$d ?
CPU time 81 68.64ITPUB个人空间l&C#I:d v
log file sync 1,012 21 17.82
G8S/A)x?w0log file parallel write 1,634 9 7.36
q^O+Y9Z/i2m0W0control file parallel write 312 6 5.25ITPUB个人空间 E]9r*`7K0o/\
db file parallel write 120 1 .75
d/@*|8?2W0ITPUB个人空间0r z4}3D _ E;N
Cache Sizes (end)
3s^v!_J8N6g z0~~~~~~~~~~~~~~~~~
J m%e'h;X0Buffer Cache: 24M Std Block Size: 8K
%g x ] OA _'Q6t|N0Shared Pool Size: 48M Log Buffer: 512K
0p([ Pf.b8qr g'u&c0… …
*|$Gz g!T[;p0Instance Efficiency Percentages (Target 100%)
a'i"j!Gu h2c7c0~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*omP T+R,c | U0Buffer Nowait %: 99.98 Redo NoWait %: 99.98
i\;Ptt xZz0Buffer Hit %: 26.29 In-memory Sort %: 100.00
u7l1M b@%R0Library Hit %: 100.00 Soft Parse %: 99.96ITPUB个人空间 Gz K8b3?i
Execute to Parse %: 75.32 Latch Hit %: 99.01
:Z W _V s0Parse CPU to Parse Elapsd %: 97.28 % Non-Parse CPU: 81.91ITPUB个人空间N`3Wm(~
… …ITPUB个人空间0A(b,Q*B7V^V$N#h A q
Top 5 Timed EventsITPUB个人空间b R9n|(De
~~~~~~~~~~~~~~~~~~ % TotalITPUB个人空间(R'y y g M;_Bo
Event Waits Time (s) Ela Time
1SkI$V \r0-------------------------------------------- ------------ ----------- --------ITPUB个人空间6qa1vH$M [h e1\(n6M
db file scattered read 338,288 2,359 71.17
D't9i1t#x1p#I0db file sequential read 121,705 401 12.08ITPUB个人空间N C.{ `&c
log file sync 5,693 318 9.60ITPUB个人空间3v&k(nUv*q'm
CPU time 109 3.28ITPUB个人空间"D/n J _ hnJ
log file parallel write 6,446 68 2.06
e[1p @@xBuT0-------------------------------------------------------------
S3EY|"LP%^JG:Rs0… …
D'k m iE-{_0Estd Extra Estd PGA Estd PGA
%O+\@'PF*^+~ u K%P G:W0PGA Target Size W/A MB W/A MB Read/ Cache OverallocITPUB个人空间*nYT3P3g6^*`
Est (MB) Factr Processed Written to Disk Hit % CountITPUB个人空间5G eC N9I/G1p
---------- ------- ---------------- ---------------- -------- ----------
&}/P:k5qG2}1V012 0.5 5,291.3 892.1 86.0 185ITPUB个人空间5aPc$l-[
18 0.8 5,291.3 892.1 86.0 185ITPUB个人空间vR,ah0w
24 1.0 5,291.3 308.1 94.0 185ITPUB个人空间$@ ?!Ck6{4g p+O
29 1.2 5,291.3 307.2 95.0 185
V8Bj @P h3yR`034 1.4 5,291.3 307.2 95.0 185ITPUB个人空间S?5X2VK
38 1.6 5,291.3 307.2 95.0 185
&l.c{;k6q6]I^\ Z043 1.8 5,291.3 307.2 95.0 185
6^|eZ xV$\048 2.0 5,291.3 307.2 95.0 185ITPUB个人空间~ V rP w"l u
72 3.0 5,291.3 300.4 95.0 109
L(hd!MnI096 4.0 5,291.3 66.1 99.0 1ITPUB个人空间:@-J q c%p9G
144 6.0 5,291.3 0.0 100.0 0
+W:O D e#s5_ ^'H a P&R y0192 8.0 5,291.3 0.0 100.0 0ITPUB个人空间W6er.WP \ ]
-------------------------------------------------------------
o_;R,f%T g/Va \$C0
1E u.u3m'p a:_2V)W*k0问题：
3dW MK| U01：Buffer Cache:24M 数据库缓存设置过小
4C @0C4{ y f$}02：Shared Pool Size: 48M 共享池设置过小ITPUB个人空间7rU9^6kym%S
3：Buffer Hit %: 26.29 数据库缓存命中率过低ITPUB个人空间2z(ps ?+|8T
4：在top 5中ITPUB个人空间 uA]u Ky2U
db file scattered read 338,288 2,359 71.17ITPUB个人空间vk xx2B3`:Nn3N4P+l
db file sequential read 121,705 401 12.08
Q B$S!ah(c+Ss&D0x0排在前两位，造成离散读（scattered read）跟顺序读（sequential read）的原因是由于Buffer Cache缓存设置过小造成的。
-Z Jk ?3f.W ZJ05：pga_aggregate_target设置过小ITPUB个人空间"V%BS N3m'HU^
ITPUB个人空间z'?5k ? C9j O js l
在现场工程师按照自检文档修改过相关参数后，再次做statspack：
V2~O J;p5N y0Cache Sizes (end)ITPUB个人空间u%d |&G"M
~~~~~~~~~~~~~~~~~
!_8l|*y;D-v,H0Buffer Cache: 704M Std Block Size: 8K
3_ IX-U]U0Shared Pool Size: 256M Log Buffer: 512K
+}ws9MO5a0… …ITPUB个人空间 L7~"{,m'p
Instance Efficiency Percentages (Target 100%)ITPUB个人空间/Bo B } @'z3]}3U
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
}9Q1p+wd[J V*Sf0Buffer Nowait %: 100.00 Redo NoWait %: 100.00
ul Uqd }J u0Buffer Hit %: 99.91 In-memory Sort %: 100.00ITPUB个人空间 |PPx _R
Library Hit %: 99.96 Soft Parse %: 99.88
`#j;w)zW P{0Execute to Parse %: 75.44 Latch Hit %: 98.39ITPUB个人空间LT:bB z"|J?^
Parse CPU to Parse Elapsd %: 96.80 % Non-Parse CPU: 72.89ITPUB个人空间W.z*`uR }o[
… …
'D&{ WL*v [u%A0Top 5 Timed EventsITPUB个人空间q I.D MJ#d
~~~~~~~~~~~~~~~~~~ % Total
m&MMODz;ak0Event Waits Time (s) Ela Time
'IE ` ^U8b3]D6G0-------------------------------------------- ------------ ----------- --------ITPUB个人空间pl'Z)H w F*zX+c PR
log file sync 7,174 128 45.41
RKg7|1L l0CPU time 89 31.76ITPUB个人空间ET h"E xZ
log file parallel write 10,925 37 13.01
3}O:O.Dv TQ J K0db file sequential read 5,906 21 7.63ITPUB个人空间E&x)@X!y/] b
control file parallel write 348 6 2.05
i?P-|-V E"|2r&e0-------------------------------------------------------------
j+SMD'rN N,z0… …
GF1Ta6s0Estd Extra Estd PGA Estd PGA
|` U,],KT0PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
l*b5M g Hp0Est (MB) Factr Processed Written to Disk Hit % Count
O z(V{5}2a0---------- ------- ---------------- ---------------- -------- ----------
XtB4sJ025 0.1 131.7 0.0 100.0 2ITPUB个人空间 A2kH GM p
50 0.3 131.7 0.0 100.0 2ITPUB个人空间.J8e cxV
100 0.5 131.7 0.0 100.0 0ITPUB个人空间B6d'q]7do
150 0.8 131.7 0.0 100.0 0
![ f Ay"~!M'|'d0200 1.0 131.7 0.0 100.0 0
GL N:d/sRH0240 1.2 131.7 0.0 100.0 0
q/Q8Q w0E&Z Y_ @0280 1.4 131.7 0.0 100.0 0ITPUB个人空间 W5z#R5v1z.S7P(Cu v
320 1.6 131.7 0.0 100.0 0
"yP6M,aoP*B0360 1.8 131.7 0.0 100.0 0ITPUB个人空间4@ @ UE#i W"P
400 2.0 131.7 0.0 100.0 0
1f0Y)HV x"x{I0600 3.0 131.7 0.0 100.0 0ITPUB个人空间#U `i/O:Y,~ n4W
800 4.0 131.7 0.0 100.0 0
%W-j`r^ s01,200 6.0 131.7 0.0 100.0 0
h4d$?2J l_2e?2_b01,600 8.0 131.7 0.0 100.0 0ITPUB个人空间Hh2FW/gI J i
-------------------------------------------------------------ITPUB个人空间.\ ~pg c]W(u
ITPUB个人空间8j6x"`-XT k:`&{ K
1：Buffer Hit%: 99.91 命中率上来了。
0G{y ?GH02：top 5中，scattered read事件已经消失，sequential read的等待时间由401下降为21ITPUB个人空间1K s;V2p Y&J&F
db file sequential read 5,906 21 7.63ITPUB个人空间 ^8M8@M+_:C@t p
3：但在top 5中发现
`6uXa ~x0log file sync 7,174 128 45.41
)RL r^!n0log fle sync排在第一位，说明应用commit提交过频繁，结合statspack报告并查询应用，发现如下存储过程：xf_GetDebtNotice存在多次commit，减少非必要commit。
Y9dJcuk*]%S Z0
.zre7}/P![f%m,K*B*k2a ~04：再次ststspack，发现log file sync已经由128降低为21。
l?-Z9s;f8O(Y-U6lH0
JO r"Z XD0Top 5 Timed EventsITPUB个人空间%`T,u](_|d"I0U m
~~~~~~~~~~~~~~~~~~ % Total
.w u;U1j'aBF0Event Waits Time (s) Ela Time
&WD(B"XU$Lq.T7t^6F0-------------------------------------------- ------------ ----------- --------ITPUB个人空间%\B'r$d ?
CPU time 81 68.64ITPUB个人空间l&C#I:d v
log file sync 1,012 21 17.82
G8S/A)x?w0log file parallel write 1,634 9 7.36
q^O+Y9Z/i2m0W0control file parallel write 312 6 5.25ITPUB个人空间 E]9r*`7K0o/\
db file parallel write 120 1 .75
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
