在一份statspack报告中发现redo size 为负数
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
CRM 3413823439 crm2 2 9.2.0.8.0 YES zwq_crm2
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 47654 05-Feb-12 11:00:04 2,301 20.0
End Snap: 47655 05-Feb-12 12:00:02 2,298 20.3
Elapsed: 59.97 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 36,448M Std Block Size: 8K
Shared Pool Size: 10,240M Log Buffer: 20,480K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
负数=====> Redo size: -359,121.01 -7,828.69
Logical reads: 349,787.58 7,625.22
Block changes: 5,656.13 123.30
Physical reads: 12,521.51 272.96
Physical writes: 427.44 9.32
User calls: 22,223.63 484.47
Parses: 4,673.27 101.88
Hard parses: 46.78 1.02
Sorts: 4,027.70 87.80
Logons: 10.57 0.23
Executes: 10,480.35 228.47
Transactions: 45.87
一时之间感觉很奇怪,在运行的数据库redo size不可能为负数(只要数据库在运行redo size都应该大于0).查询MOS[ID 308414.1]发现原来是一个bug引起(Bug:1713403 NEGATIVE VALUE IN V$SESSTAT FOR STATISTIC REDO SIZE),sp中的redo size其本质还是来源于V$SESSTAT.
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 9.2.0.8
This problem can occur on any platform.
Symptoms
Redo Size is displayed as a Negative number in a Statspack report.
For example:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: -17,931.33 -55,277.73
Logical reads: 31,095.80 95,860.43
...
Cause
Bug:1713403 NEGATIVE VALUE IN V$SESSTAT FOR STATISTIC REDO SIZE
Overflow of 'redo size' statistic. This is fixed in Oracle10g and above.
Patches do not exist for earlier releases.
Solution
Use 'redo blocks written' instead to measure the amount of redo.
Ignore negative 'redo size'.
