Re: VLM and putting an entire database in memory

> If the database were all in memory, meaining every data and index
> block, the cache hit ratio would be something like 99.99...%
> Increase the db_block_buffers until the cache hit ratio is between 95
> and 99% as you mentioned.

Thanks Allan. No, the tablespace certainly goes far beyond 5M into gigabytes.

> It is tempting to allocate hundreds of MBs
> However, if the number of
> db_block_buffers greatly exceeds the number of blocks of data and
 index,
> scans of the buffer cache for required data or index blocks may take
> longer than retrieving them from disk, because of the amount of time
> spent scanning empty blocks in the buffer cache.

Interesting; hadn't come across that warning/consideration. The mantra in Oracle Tuning seems to be, overall: "Disk I/O bad; Memory I/O good." There had to be an exception somewhere (probably too much memory to the point of paging/swapping). Absolutes of every other kind fail us; why not tuning absolutes as well?

So will incrementally increasing DB_BLOCK_BUFFERS just until I reach the 95-99% db buffer cache ratio insulate me from the memory problem you described?

Do you know of a good script I can run to collect performance info (hit ratios and other info from V$SYSSTAT) over time? Say, a period of a week, so I can get a good idea of how a system is running before I make any changes? I need a good benchmark and I'm not sure UTLBSTAT and UTLESTAT will do the trick.

Thanks again.

• Dana

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 15 2000 - 07:53:45 CST