'db_block_buffers' suck and weird

From: <pysiek_at_my-deja.com>
Date: Mon, 26 Jul 1999 10:34:13 GMT
Message-ID: <7nhdj6$6ug$1@nnrp1.deja.com>

There is new database to be rolled-out. During weekend bunch of users was uploading number of document from paper to DB, as part of system migration process. Because system is brand new and never beed tunned, there were few hickups but this one.....

---

  1 SELECT (1-SUM(DECODE(NAME,'physical reads',VALUE,0)) / (SUM(DECODE
(NAME,'db block gets', value, 0)) +

  2* sum(decode(name, 'consistent gets', value, 0)))) * 100 from v$sysstat
SQL> /
(1-SUM(DECODE(NAME,'PHYSICALREADS',VALUE,0))/(SUM(DECODE
(NAME,'DBBLOCKGETS',VALUE,0))+SUM(DECODE(NAM

---

---

                   38.148656

• So buffer sucks ! Less than 40% of data is cached, but look at this !

SQL> select state, count(*) from x$bh group by state;

    STATE COUNT(*)
--------- ---------

        0     85407	-- haven't been used
        1     61106	-- have been used
        3     13487	-- are used

• Only half of cache blocks were ever used !

And this is something I cannot explain. Database is cached poorly, but for some reason there is still plenty of cache space to be used. Does anybody could put some light on it ?
I didn't call Oracle, as they usually refuse to answer performance problems. Does anybody have any idea what is wrong ?

Jarek

ps. Please answer on jarek.pyszkiewicz_at_eds.com --
Jarek Pyszkiewicz, EDS Ireland

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 26 1999 - 05:34:13 CDT