1.cpu使用率
cpu使用率降低35%
20110610 8:30-10:30 cpu使用率图
CPU:
User%
Sys%
Wait%
Idle%
CPU%
Avg
72.6
8.1jcq0
7.8
11.6
80.7
Max
87.2
10.2
12.4
22.8
93.6
Max:Avg
1.2
1.3
1.6
2.0
1.2
20110610 8:30-10:30 cpu使用率表
20110617 8:30-10:30 cpu使用率图
CPU:
User%
Sys%
Wait%
Idle%
CPU%
Avg
38.5
6.6jcq0
9.2
45.7
45.1
Max
57.1
8.8
13.4
55.2
64.5
Max:Avg
1.5
1.3
1.5
1.2
1.4
20110611 8:30-10:30 cpu使用率表
2.磁盘读写
磁盘IO减少60%
20110610 8:30-10:30 磁盘IO图
Disk total KB/s acctdb1
Disk Read KB/s
Disk Write KB/s
IO/sec
Avg.jcq0
143877.5967
9037.179167
7062.510833
WAvg.
8132.576167
3814.204166
75.90660406
Max.
96363.72717
13655.21667
1787.882563
20110610 8:30-10:30 磁盘IO表
20110617 8:30-10:30 磁盘IO图
Disk total KB/s acctdb1
Disk Read KB/s
Disk Write KB/s
IO/sec
Avg.
37839.5jcq0
9271.7
2893.5
WAvg.
26813.7
4145.1
488.9
Max.
57048.6
16887.9
2331.5
20110617 8:30-10:30 磁盘IO表
3.数据库层面
在6月10号的两个小时的采样中，平均事务响应时间为79ms
在6月17号的两个小时的采样中，平均事务响应时间为34ms
数据库的处理速度提高近一倍
1.)物理读和逻辑读
物理读减少71% 逻辑读减少65%
1st Per Sec
2nd Per Sec
%Diff
1st Per Txn
2nd Per Txn
%Diff
Redo size:
965,954.01
963,677.85
-0.24
1,915.95
1,996.63
4.21
Logical reads:
468,875.91
162,391.46
-65.37
930.00
336.46
-63.82
Block changes:
6,023.54
5,984.79
-0.64
11.95
11.95
0.00
Physical reads:
16,824.12
4,800.09
-71.47
33.37
9.95
-70.18
Physical writes:
720.71
752.73
4.44
1.43
1.56
9.09
User calls:
17,483.27
17,529.69
0.27
34.68
36.32
4.73
Parses:
8,392.87
9,188.08
9.47
16.65
19.04
14.35
Hard parses:
43.95
51.36
16.86
0.09
0.11
22.22
Sorts:
476.22
539.87
13.37
0.94
1.12
19.15
Logons:
1.20
1.20
0.00
0.00
0.00
0.00
Executes:
9,035.53
9,828.62
8.78
17.92
20.36
13.62
Transactions:
504.17
482.65
-4.27
对于每秒事务数的说明：
通过redo size ,physical writes,User calls的数据比对可以判断，两个时间段的业务量大致相同，通过应用确认，所调整的语句均为每隔固定的时间段调用一次，两个时间段的平均活动用户数(Avg Active Users)由38减至16个，可以判断应用有轻微的排队效应(由于Oracle对会话的采样时间是每隔1s钟进行一次，在调整前的一个语句如果执行27.45s,执行一次贡献的活动会话的次数为27，而调整后的语句执行时间为0.01s,贡献的会话次数为0或者1，所以优化后时间段得活动会话数会明显减少)，但是并不严重，所以每秒的事务数大致相同，而对于一个排队效应特别严重的系统，每秒的事务数将大大增加。
2.)集群间的争用
gc buffer busy占用的DB Time由7.75减至1.23
1st
2nd
Event
Waits
Time(s)
Percent Total DB Time
Event
Waits
Time(s)
Percent Total DB Time
CPU time
106,982.40
38.51
db file sequential read
6,930,981
54,823.60
47.03
db file sequential read
15,811,804
77,751.70
27.99
CPU time
43,495.90
37.31
db file scattered read
12,994,664
25,661.90
9.24
log file sync
3,461,377
16,276.20
13.96
log file sync
3,649,624
25,650.10
jcq09.23
db file scattered read
2,081,415
8,405.60
7.21
*gc buffer busy
24,320,912
21,528.40
7.75
*log file parallel write
1,956,609
4,333.30
3.72
-log file parallel write
1,740,962
5,045.20
1.82
-gc buffer busy
408,503
1,432.00
1.23
3.)CPU使用情况
数据库每秒钟对CPU的时间降低59%
per Second (Elapsed Time)
per Trans
Statistic Name
1st
2nd
%Diff
1st
2nd
%Diff
AVG_BUSY_TIME
56
25
-56.28
0
0
-54.55
AVG_IDLE_TIME
44
75
72.38
0
0
77.78
AVG_IOWAIT_TIME
17
12
-32.3
0
0
-33.33
AVG_SYS_TIME
jcq07
4
-35.13
0
0
0
AVG_USER_TIME
49
20
-59.25
0
0
-60
BUSY_TIME
2,025
886
-56.25
4
2
-54.23
IDLE_TIME
1,576
2,716
72.31
3
6
79.87
IOWAIT_TIME
614
416
-32.26
1
1
-29.51
SYS_TIME
248
161
-35.03
0
0
-32.65
USER_TIME
1,777
725
-59.21
4
2
-57.39
一周的时间，18*7的努力，每天休息时间不足6小时，能取得如此成果，实属不易，我个人对此非常满意，据说局方的也非常满意。
(需要引用, 请注明出处:痴情甲骨文http://space.itpub.net/14130873)
|
CPU: |
User% |
Sys% |
Wait% |
Idle% |
CPU% |
|
Avg |
72.6 |
8.1jcq0 |
7.8 |
11.6 |
80.7 |
|
Max |
87.2 |
10.2 |
12.4 |
22.8 |
93.6 |
|
Max:Avg |
1.2 |
1.3 |
1.6 |
2.0 |
1.2 |
|
CPU: |
User% |
Sys% |
Wait% |
Idle% |
CPU% |
|
Avg |
38.5 |
6.6jcq0 |
9.2 |
45.7 |
45.1 |
|
Max |
57.1 |
8.8 |
13.4 |
55.2 |
64.5 |
|
Max:Avg |
1.5 |
1.3 |
1.5 |
1.2 |
1.4 |
Disk total KB/s acctdb1
Disk Read KB/s
Disk Write KB/s
IO/sec
Avg.jcq0
143877.5967
9037.179167
7062.510833
WAvg.
8132.576167
3814.204166
75.90660406
Max.
96363.72717
13655.21667
1787.882563
Disk total KB/s acctdb1
Disk Read KB/s
Disk Write KB/s
IO/sec
Avg.
37839.5jcq0
9271.7
2893.5
WAvg.
26813.7
4145.1
488.9
Max.
57048.6
16887.9
2331.5
1st Per Sec
2nd Per Sec
%Diff
1st Per Txn
2nd Per Txn
%Diff
Redo size:
965,954.01
963,677.85
-0.24
1,915.95
1,996.63
4.21
Logical reads:
468,875.91
162,391.46
-65.37
930.00
336.46
-63.82
Block changes:
6,023.54
5,984.79
-0.64
11.95
11.95
0.00
Physical reads:
16,824.12
4,800.09
-71.47
33.37
9.95
-70.18
Physical writes:
720.71
752.73
4.44
1.43
1.56
9.09
User calls:
17,483.27
17,529.69
0.27
34.68
36.32
4.73
Parses:
8,392.87
9,188.08
9.47
16.65
19.04
14.35
Hard parses:
43.95
51.36
16.86
0.09
0.11
22.22
Sorts:
476.22
539.87
13.37
0.94
1.12
19.15
Logons:
1.20
1.20
0.00
0.00
0.00
0.00
Executes:
9,035.53
9,828.62
8.78
17.92
20.36
13.62
Transactions:
504.17
482.65
-4.27
1st
2nd
Event
Waits
Time(s)
Percent Total DB Time
Event
Waits
Time(s)
Percent Total DB Time
CPU time
106,982.40
38.51
db file sequential read
6,930,981
54,823.60
47.03
db file sequential read
15,811,804
77,751.70
27.99
CPU time
43,495.90
37.31
db file scattered read
12,994,664
25,661.90
9.24
log file sync
3,461,377
16,276.20
13.96
log file sync
3,649,624
25,650.10
jcq09.23
db file scattered read
2,081,415
8,405.60
7.21
*gc buffer busy
24,320,912
21,528.40
7.75
*log file parallel write
1,956,609
4,333.30
3.72
-log file parallel write
1,740,962
5,045.20
1.82
-gc buffer busy
408,503
1,432.00
1.23
per Second (Elapsed Time)
per Trans
Statistic Name
1st
2nd
%Diff
1st
2nd
%Diff
AVG_BUSY_TIME
56
25
-56.28
0
0
-54.55
AVG_IDLE_TIME
44
75
72.38
0
0
77.78
AVG_IOWAIT_TIME
17
12
-32.3
0
0
-33.33
AVG_SYS_TIME
jcq07
4
-35.13
0
0
0
AVG_USER_TIME
49
20
-59.25
0
0
-60
BUSY_TIME
2,025
886
-56.25
4
2
-54.23
IDLE_TIME
1,576
2,716
72.31
3
6
79.87
IOWAIT_TIME
614
416
-32.26
1
1
-29.51
SYS_TIME
248
161
-35.03
0
0
-32.65
USER_TIME
1,777
725
-59.21
4
2
-57.39
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14130873/viewspace-702581/ ，如需转载，请注明出处，否则将追究法律责任。
