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)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14130873/viewspace-702581/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14130873/viewspace-702581/