oracle负数比较大小,【案例】Oracle StatSpack报告中redo size为负数案例分析

天萃荷净

运维DBA反映，在巡检时发现StatSpack报告中的redo size为负数，分析原因为数据库BUG导致

1.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'.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle StatSpack报告中redo size为负数案例分析