run time stats and x$ tables

From: Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com>
Date: Mon, 01 Nov 1999 14:23:52 GMT
Message-ID: <7vk7pl$1rg$1@nnrp1.deja.com>

I have begun tracking performance statistics on my Oracle databases. Right now I am focusing on cache miss ratio, using the following query:

select
  p.bp_name buffer_pool,
  lpad(to_char(100 * sum(s.pread) / sum(s.dbbget + s.conget), '990.00') || '%', 13) miss_rate
from
  sys.x_$kcbwds s,
  sys.x_$kcbwbpd p
where

  s.set_id >= p.bp_lo_sid and
  s.set_id <= p.bp_hi_sid and
  p.bp_size != 0

group by
  p.bp_name
/

(X_$kcbwds and x_$kcbwbpd are views that map directly to the tables X$kcbwds and x$kcbwbpd).

My basic question is – when are numbers in these ‘statistics gathering’ tables updated, and when are they zeroed? At its roots, the above query is selecting PREAD, DBBGET, and CONGET from x$kcbwds, and bp_name from x$kcbwbpd. But I don’t really know for what time frame these numbers are derived. If I run this query every day for a week, and the database is never shut down, do Tuesday’s numbers also include Monday’s numbers?

• Ed Stevens

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 01 1999 - 08:23:52 CST