oracle 判断负值,【案例】Oracle StatSpack报告中逻辑读为负值案例

天萃荷净

运维DBA巡检数据库性能时发现StatSpack报告中逻辑读为负值

最近遇到两次在sp报告中，显示逻辑读为负数。进行分析情况如下：

一台是运营商的crm库(aix 5.3+9.2.0.8+rac)

--系统版本

[zwq_crm2:/home/crm_oraeye]oslevel -s

5300-08-07-0920

--数据库版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

PL/SQL Release 9.2.0.8.0 - Production

CORE 9.2.0.8.0 Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

--数据库启动时间

SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')

2 FROM gv$instance a;

INST_ID TO_CHAR(STARTUP_TIM

---------- -------------------

2 2011-02-19 03:33:49

1 2011-02-19 03:56:17

--异常的逻辑读统计数据

SQL> SELECT b.snap_id,

2 TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),

3 TO_CHAR (A.VALUE, '999,999,999,999,999')

4 FROM stats$sysstat a, stats$snapshot b

5 WHERE a.NAME = 'session logical reads'

6 AND a.instance_number = 2

7 AND a.snap_id = b.snap_id

8 AND A.SNAP_ID >=47913

9 AND A.SNAP_ID <=47920

10 ORDER BY a.snap_id;

SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999

---------- ------------------- --------------------

479132012-02-10 20:004,764,705,272,783

479142012-02-10 21:004,761,539,910,574

479152012-02-10 22:004,749,529,436,021

479162012-02-10 23:004,745,952,040,146

479172012-02-11 00:004,738,052,256,634

479182012-02-11 01:004,738,894,245,521

479192012-02-11 02:004,739,587,095,184

479202012-02-11 03:004,740,409,262,259

另一台是运营商的开停机库(aix 5.3+9.2.0.8+rac)

--系统版本

[zwq_offon2:/home/oraeye]oslevel -s

5300-08-07-0920

--数据库版本

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

PL/SQL Release 9.2.0.8.0 - Production

CORE 9.2.0.8.0 Production

TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

--数据库启动时间

SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')

2 FROM gv$instance a;

INST_ID TO_CHAR(STARTUP_TIM

---------- -------------------

2 2010-01-23 19:16:46

1 2010-01-23 19:15:09

--异常的逻辑读统计数据

SQL> SELECT b.snap_id,

2 TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),

3 TO_CHAR (A.VALUE, '999,999,999,999,999')

4 FROM stats$sysstat a, stats$snapshot b

5 WHERE a.NAME = 'session logical reads'

6 AND a.instance_number = 2

7 AND a.snap_id = b.snap_id

8 AND A.SNAP_ID IN ('38271', '38272', '38339', '38340')

9 ORDER BY a.snap_id;

SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999

---------- ------------------- --------------------

38271 2012-01-31 19:00:05 4,269,858,122,434

38272 2012-01-31 20:00:02 4,266,001,522,867

38339 2012-02-02 09:00:02 4,275,651,080,526

38340 2012-02-02 10:00:02 4,250,263,107,466

这两个数据库中都出现了在数据库没有重启的情况下stats$sysstat.value的值出现波动情况，而且都是在40万亿以上的时候。不知道是特点的版本巧合(特定的数据库版本，特定的操作系统版本)，还是Oracle未公布bug。出现这样的情况，目前只能通过重启实例来使得statspack捕获到的逻辑读值变小，从而避免这样的波动，从而解决sp中出现逻辑读为负数的情况。

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

最权威、专业的Oracle案例资源汇总之【案例】Oracle StatSpack报告中逻辑读为负值案例