Re: VLM and putting an entire database in memory

Yes, incrementaly increase db_block_buffers until the cache hit ratio is between 95-99%. Note that the database may contain gigs of data/index, but it is only the portion used that the db_block_buffers need to accommodate.

Database block buffer cache hit ratio is a basic statistic that bstat/estat collects handily. For a more advanced script, you may want to try statspack. There is a pre=816 release available for 805 at this link

http://www.oracle.com/oramag/oracle/00-Mar/statspack-other.html

The "seasoned" dbas I work with, swear by a 3rd party product called Precise. Haven't worked with it myself, but apparently it has a very small footprint that does not require a database connection because it samples server memory directly (every second). For example, it will identify waits and graph them over time. In this way, we were able to identify that redo log waits were consuming 1 1/2 hours of batch processing time per night. Cause was that the redo logs were striped along with all the other datafiles across disks. Striping does not lend itself well to sequential i/o as in the case of redo logs. Moving the redo logs onto their own disk eliminated the wait time.

In article <91d7p7$rgc$1_at_nnrp1.deja.com>,   dmnwork_at_my-deja.com wrote:
>
>
> > 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 15 2000 - 10:05:27 CST