Tuesday, September 25, 2012

V$SYSMETRIC and V$SYSMETRIC_HISTORY



I read about this on page 713 of the book advertised above.
 
In an earlier example, I looked at buffer cache hit ratio. The result was an average value since instance startup.
 
The example here was tested on Oracle 11.2 but, according to the book above, it also works on Oracle 10g. You can now see the current value of buffer cache hit ratio in V$SYSMETRIC. The first value is calculated over 1 minute and the second is calculated over 15 seconds:

SQL> l
  1  select
  2  to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') begin_time,
  3  to_char(end_time,'DD-MON-YYYY HH24:MI:SS') end_time,
  4  value
  5  from v$sysmetric
  6  where metric_name = 'Buffer Cache Hit Ratio'
  7* order by begin_time
SQL> /
 
BEGIN_TIME           END_TIME                  VALUE
-------------------- -------------------- ----------
25-SEP-2012 16:28:57 25-SEP-2012 16:29:57  97.194687
25-SEP-2012 16:30:27 25-SEP-2012 16:30:42  91.864139
 
SQL>
 
You can get the values for the last hour from V$SYSMETRIC_HISTORY:
 
SQL> l
  1  select
  2  to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') begin_time,
  3  to_char(end_time,'DD-MON-YYYY HH24:MI:SS') end_time,
  4  value
  5  from v$sysmetric_history
  6  where metric_name = 'Buffer Cache Hit Ratio'
  7* order by begin_time, end_time
SQL> /
 
BEGIN_TIME           END_TIME                  VALUE
-------------------- -------------------- ----------
25-SEP-2012 15:49:57 25-SEP-2012 15:50:57 97.5303316
25-SEP-2012 15:50:57 25-SEP-2012 15:51:57 96.2754449
25-SEP-2012 15:51:57 25-SEP-2012 15:52:57 96.6650953
25-SEP-2012 15:52:57 25-SEP-2012 15:53:57 96.7953002
25-SEP-2012 15:53:57 25-SEP-2012 15:54:57 98.1617859
25-SEP-2012 15:54:57 25-SEP-2012 15:55:58 98.1856913
25-SEP-2012 15:55:58 25-SEP-2012 15:56:58 99.1994269
25-SEP-2012 15:56:58 25-SEP-2012 15:57:58 99.8990431
25-SEP-2012 15:57:58 25-SEP-2012 15:58:57 99.8517867
25-SEP-2012 15:58:57 25-SEP-2012 15:59:57 99.6710275
25-SEP-2012 15:59:57 25-SEP-2012 16:00:57 90.5124721
25-SEP-2012 16:00:57 25-SEP-2012 16:01:57 96.7805768
25-SEP-2012 16:01:57 25-SEP-2012 16:02:57 95.9747129
25-SEP-2012 16:02:57 25-SEP-2012 16:03:57 95.3531629
25-SEP-2012 16:03:57 25-SEP-2012 16:04:57 94.5228342
25-SEP-2012 16:04:57 25-SEP-2012 16:05:58 95.6838002
25-SEP-2012 16:05:58 25-SEP-2012 16:06:58 95.4702619
25-SEP-2012 16:06:58 25-SEP-2012 16:07:58 99.7447147
25-SEP-2012 16:07:58 25-SEP-2012 16:08:57 97.2875844
25-SEP-2012 16:08:57 25-SEP-2012 16:09:57 96.3138593
25-SEP-2012 16:09:57 25-SEP-2012 16:10:57 97.4352075
25-SEP-2012 16:10:57 25-SEP-2012 16:11:57 97.8508368
25-SEP-2012 16:11:57 25-SEP-2012 16:12:57 97.2685509
25-SEP-2012 16:12:57 25-SEP-2012 16:13:57 95.4675739
25-SEP-2012 16:13:57 25-SEP-2012 16:14:57 97.9981712
25-SEP-2012 16:14:57 25-SEP-2012 16:15:58 96.6777483
25-SEP-2012 16:15:58 25-SEP-2012 16:16:58 97.6958152
25-SEP-2012 16:16:58 25-SEP-2012 16:17:58 98.3318701
25-SEP-2012 16:17:58 25-SEP-2012 16:18:57 97.3715671
25-SEP-2012 16:18:57 25-SEP-2012 16:19:57 98.7109485
25-SEP-2012 16:19:57 25-SEP-2012 16:20:57 96.2517824
25-SEP-2012 16:20:57 25-SEP-2012 16:21:57 96.4621925
25-SEP-2012 16:21:57 25-SEP-2012 16:22:57 98.2080659
25-SEP-2012 16:22:57 25-SEP-2012 16:23:57  97.953139
25-SEP-2012 16:23:57 25-SEP-2012 16:24:57 89.2580395
25-SEP-2012 16:24:57 25-SEP-2012 16:25:58 97.5403648
25-SEP-2012 16:25:58 25-SEP-2012 16:26:58  97.578567
25-SEP-2012 16:26:58 25-SEP-2012 16:27:58 93.7777583
25-SEP-2012 16:27:58 25-SEP-2012 16:28:57 85.2168157
25-SEP-2012 16:28:57 25-SEP-2012 16:29:57  97.194687
25-SEP-2012 16:29:57 25-SEP-2012 16:30:57  98.117032
25-SEP-2012 16:30:57 25-SEP-2012 16:31:57 98.2240788
25-SEP-2012 16:31:57 25-SEP-2012 16:32:57  97.110952
25-SEP-2012 16:32:57 25-SEP-2012 16:33:57 94.5899407
25-SEP-2012 16:33:57 25-SEP-2012 16:34:57   96.80441
25-SEP-2012 16:34:57 25-SEP-2012 16:35:57  98.133023
25-SEP-2012 16:35:57 25-SEP-2012 16:36:58 97.5633308
25-SEP-2012 16:36:58 25-SEP-2012 16:37:58 98.9988528
25-SEP-2012 16:37:58 25-SEP-2012 16:38:58 93.9215307
25-SEP-2012 16:38:58 25-SEP-2012 16:39:57   98.35585
25-SEP-2012 16:39:57 25-SEP-2012 16:40:57 96.7044666
25-SEP-2012 16:40:57 25-SEP-2012 16:41:57 98.1190344
25-SEP-2012 16:41:57 25-SEP-2012 16:42:57 97.3454653
25-SEP-2012 16:42:57 25-SEP-2012 16:43:57 98.8355824
25-SEP-2012 16:43:57 25-SEP-2012 16:44:57 97.4232899
25-SEP-2012 16:44:57 25-SEP-2012 16:45:57 94.0274377
25-SEP-2012 16:45:57 25-SEP-2012 16:46:57 95.7594464
25-SEP-2012 16:46:57 25-SEP-2012 16:47:58 96.1082737
25-SEP-2012 16:47:58 25-SEP-2012 16:48:58 96.9696389
25-SEP-2012 16:48:28 25-SEP-2012 16:48:43 98.4661672
25-SEP-2012 16:48:43 25-SEP-2012 16:48:58  95.309712
25-SEP-2012 16:48:58 25-SEP-2012 16:49:13 89.0358259
25-SEP-2012 16:48:58 25-SEP-2012 16:49:58 98.8512452
25-SEP-2012 16:49:13 25-SEP-2012 16:49:28 96.9536584
25-SEP-2012 16:49:28 25-SEP-2012 16:49:43 99.4072881
25-SEP-2012 16:49:43 25-SEP-2012 16:49:58 99.5088527
25-SEP-2012 16:49:58 25-SEP-2012 16:50:13 97.4177137
25-SEP-2012 16:49:58 25-SEP-2012 16:50:57 97.3807871
25-SEP-2012 16:50:13 25-SEP-2012 16:50:28 88.0492281
25-SEP-2012 16:50:28 25-SEP-2012 16:50:42 98.4126273
25-SEP-2012 16:50:42 25-SEP-2012 16:50:57 98.6083744
25-SEP-2012 16:50:57 25-SEP-2012 16:51:12 99.7137951
25-SEP-2012 16:51:12 25-SEP-2012 16:51:27 96.7031208
25-SEP-2012 16:51:27 25-SEP-2012 16:51:42 97.4735416
 
74 rows selected.
 
SQL>

No comments: