Re: VLM and putting an entire database in memory

In our last gripping episode dmnwork_at_my-deja.com wrote:
>
>
> With an Alpha GS140 and gigabytes of RAM, you can apparently use
> something called VLM to put an entire database into memory.
>
> However, when I'm looking at a database said to be configured this way
> (I didn't configure the hardware or the database - Oracle 8.0.5 on
> OpenVMS), I see the database buffer cache hit ratio hovering around
> 50%; a far cry from the 95-99% figure said to be desirable.
>
> The shared pool is around 30% utilizied and is about 150MB. No obvious
> problem there.
>
> But the database buffer cache is only set to be 5MB! (five). So now
 I'm
> confused. Might the hit ratio be grossly skewed if the "entire
> database" is "in memory"? Any reason why someone wouldn't increase the
> DB_BLOCK_BUFFERS if memory were plentiful? Also noted these values:
>
> db_block_size = 2048
> db_file_multiblock_read_count = 16
>
> Isn't 2048 bytes ridiculously low? Shouldn't it at least be doubled
 for
> a large production system (not that I can rebuild the entire database
> at the moment)? What about multiblock_read_counts? Any reason it
> shouldn't be doubled to 32?
>
> Thanks.
>
> - Dana
>
> Sent via Deja.com
> http://www.deja.com/
>

I cannot speak for the person or persons who created the database, however for a high-volume, transaction-intensive database a db_block_size value of 2048, the default from Oracle, is, quite honestly, ridiculously low. I would have made it 8192. And as for the db_multiblock_read_count value, it should be 32 for a 2k block database -- db_block_size (in K) * db_multiblock_read_count = 64K.

If the database is truly in memory in its entirety the dictionary cache could show a low percentage of 'hits' as disk access would never be necessary, although I would not expect this behaviour. Another cause of these low percentages could very well be the 5M buffer cache. Since there is not enough cache memory to contain the SQL statements they are not being aged out but likely being "pushed" out, replaced by more recent SQL. Any user wishing to re-use a query may need to reload the query into cache thereby reducing the cache hit ratio.

Since you cannot change the db_block_size without recreating the database that is, most probably, not an option at this time. Increase the buffer cache; you'll likely see the dictionary cache hit ratios increase.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/