联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近遇到两次在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 ---------- ------------------- -------------------- 47913 2012-02-10 20:00 4,764,705,272,783 47914 2012-02-10 21:00 4,761,539,910,574 47915 2012-02-10 22:00 4,749,529,436,021 47916 2012-02-10 23:00 4,745,952,040,146 47917 2012-02-11 00:00 4,738,052,256,634 47918 2012-02-11 01:00 4,738,894,245,521 47919 2012-02-11 02:00 4,739,587,095,184 47920 2012-02-11 03:00 4,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中出现逻辑读为负数的情况。