8.1.6/HP-UX 11/Data Warehouse

Hi All! Happy Wednesday!

I have been reading lately (on the list and in other materials) about the venerable Cache Hit Ratio not really being a good measure of performance. I agree, but there's something I can't quite grasp.

I have this performance report from one of my data marts (the report queries the Statspack table STATS$SYSSTAT):

                                              BUFFER
SNAPSHOT      DB BLOCK CONSISTENT   PHYSICAL   CACHE
TIME              GETS       GETS      READS    HIT%
----------- ---------- ---------- ---------- -------
05/01 12:01       3082    3940345      77913   98.02
05/01 12:16      10030  114062688    1972731   98.27
05/01 12:31       8494  126757798    1488216   98.83
05/01 12:47       6967  145269614     948446   99.35
05/01 13:02       5702  126382287    1166288   99.08
05/01 13:17       6325   96525651    2145440   97.78
05/01 13:33       6312  109787906    2295739   97.91
05/01 13:48       6065  135562099    2491945   98.16
05/01 14:04       5948  129659382    2299102   98.23

I know (KNOW!) that there have been a lot of one-off queries that use fixed values (as opposed to bind variables) in the WHERE clause executed on this instance all morning (from COGNOS cube builds). I know (KNOW!) that most of these queries have done full table scans of multi-gigabyte tables which would have to result in flushing and re-filling the buffer cache. The buffer cache is 1.2G - not enough to cache all of any single table.

I still do not understand why that result does not show up in this query. I think the CHR in this report should be in the 10-20% range at best. Can anybody help educate me?

Thanks,
Mike

P.S. And NO...I did not run up the CHR with Connor's script!

---
===========================================================================
Michael P. Vergara
Oracle DBA
Guidant Corporation



-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 

Author: Vergara, Michael (TEM)
  INET: mvergara_at_guidant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).