V$SYSMETRIC and V$SYSMETRIC_HISTORY

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. 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>