Discrepancy between v$sysmetric and v$sysstat

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Wed, 16 Sep 2009 11:17:42 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112B5D3054_at_ONEWS06.oneneck.corp>

I'm just now getting around to digging into the metrics introduced in 10g via v$sysmetric and related views and I'm noticing a pretty big discrepancy between the values for metric "Physical Reads Per Sec" and the values derived from "physical reads" in v$sysstat. For example, if I look at a statspack report for a given time period, I see the following indicating the average was 26.99 reads per second:

Snapshot Snap Id Snap Time

~~~~~~~~    ---------- ------------------
Begin Snap:      12192 11-Sep-09 14:15:02
  End Snap:      12195 11-Sep-09 15:00:02
   Elapsed:               45.00 (mins)

Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
             Physical reads:                 26.99                318.17

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
physical reads                                72,862           27.0        318.2

But if I look at v$sysmetric_history for the same period, I get the results shown below and if I take the average of all those values I only get 15.9 reads per second. I've repeated this for several different time ranges and the numbers never match up on this system, which is running Oracle 10.2.0.4 on Oracle Linux 5.2, but when I try the same comparison on another system running Oracle 10.2.0.2 on AIX, the numbers match up almost perfectly. I already searched Metalink for bugs and couldn't find anything. Has anyone else noticed this problem? Any idea what might be causing it?

Thanks,
Brandon

SYS_at_d517>select begin_time, end_time, value from v$sysmetric_history where metric_name = 'Physical Reads Per Sec' and intsize_csec > 5900 order by 1;

BEGIN_TIME         END_TIME                VALUE
------------------ ------------------ ----------


...

11-SEP-09 14:15:17 11-SEP-09 14:16:18          0
11-SEP-09 14:16:18 11-SEP-09 14:17:18 498.518395
11-SEP-09 14:17:18 11-SEP-09 14:18:18  .03329449
11-SEP-09 14:18:18 11-SEP-09 14:19:18          0
11-SEP-09 14:19:18 11-SEP-09 14:20:18 119.843516
11-SEP-09 14:20:18 11-SEP-09 14:21:18          0
11-SEP-09 14:21:18 11-SEP-09 14:22:18          0
11-SEP-09 14:22:18 11-SEP-09 14:23:18  .08322237
11-SEP-09 14:23:18 11-SEP-09 14:24:18          0
11-SEP-09 14:24:18 11-SEP-09 14:25:17          0
11-SEP-09 14:25:17 11-SEP-09 14:26:17          0
11-SEP-09 14:26:17 11-SEP-09 14:27:17 7.20585788
11-SEP-09 14:27:17 11-SEP-09 14:28:17 .532534532
11-SEP-09 14:28:17 11-SEP-09 14:29:18          0
11-SEP-09 14:29:18 11-SEP-09 14:30:18          0
11-SEP-09 14:30:18 11-SEP-09 14:31:18          0
11-SEP-09 14:31:18 11-SEP-09 14:32:18 .049933422
11-SEP-09 14:32:18 11-SEP-09 14:33:18          0
11-SEP-09 14:33:18 11-SEP-09 14:34:18          0
11-SEP-09 14:34:18 11-SEP-09 14:35:18          0
11-SEP-09 14:35:18 11-SEP-09 14:36:18          0
11-SEP-09 14:36:18 11-SEP-09 14:37:18 53.7527043
11-SEP-09 14:37:18 11-SEP-09 14:38:17 .084616686
11-SEP-09 14:38:17 11-SEP-09 14:39:17 2.24700399
11-SEP-09 14:39:17 11-SEP-09 14:40:17          0
11-SEP-09 14:40:17 11-SEP-09 14:41:17          0
11-SEP-09 14:41:17 11-SEP-09 14:42:18 3.04695305
11-SEP-09 14:42:18 11-SEP-09 14:43:18          0
11-SEP-09 14:43:18 11-SEP-09 14:44:18          0
11-SEP-09 14:44:18 11-SEP-09 14:45:18          0
11-SEP-09 14:45:18 11-SEP-09 14:46:18          0
11-SEP-09 14:46:18 11-SEP-09 14:47:18  .04995005
11-SEP-09 14:47:18 11-SEP-09 14:48:18          0
11-SEP-09 14:48:18 11-SEP-09 14:49:18          0
11-SEP-09 14:49:18 11-SEP-09 14:50:18  .04995005
11-SEP-09 14:50:18 11-SEP-09 14:51:18 .083250083
11-SEP-09 14:51:18 11-SEP-09 14:52:18          0
11-SEP-09 14:52:18 11-SEP-09 14:53:17          0
11-SEP-09 14:53:17 11-SEP-09 14:54:17          0
11-SEP-09 14:54:17 11-SEP-09 14:55:17          0
11-SEP-09 14:55:17 11-SEP-09 14:56:17          0
11-SEP-09 14:56:17 11-SEP-09 14:57:17 2.43090243
11-SEP-09 14:57:17 11-SEP-09 14:58:18 24.9833555

11-SEP-09 14:58:18 11-SEP-09 14:59:18 3.79683597 11-SEP-09 14:59:18 11-SEP-09 15:00:18 .116550117
...

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 16 2009 - 13:17:42 CDT