sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【案例】Oracle StatSpack报告中redo size为负数案例分析

时间:2016-06-27 20:11   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维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为负数案例分析

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/593.html

Oracle研究中心

关键词:

StatSpack

StatSpack报告中redo size为负数案例