Re: HELP! Double the memory, now where to put it?

In article <8a65ej$tvb$1_at_nnrp1.deja.com>,   tandym_at_hotmail.com wrote:
> I just recently started working for a new client and they were
> concerned with performance issues and ordered another 4GB of memory
for
> their alpha OpenVMS 7.1 server (oracle 7.3.3.6).
>
> Currently they have allocated 1201 MB to Oracle. The entire server
> currently has 4 GB. They will be upgrading this to 8 GB and want to
> double the size allocated to oracle to 2401 MB.
>
> I have little experience with tuning memory, and so am at a loss... I
> have checked the cahce-hit ratio for the DB Buffer Cache, the Library
> cache, and the data dictionary cache and they all are operating
> somewhere between 93% - 98%. During peak hours of day, they have have
> as little as 2 mb of free memory when I query the v$sgastat view.
>
> What do I do with all of this memory? Where would you guys put it if
> you had it? I am aware that making the SGA too big can make
> performance worse, but how do I know what's too big? Please help me
on
> this one.
>
> sonya
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Options (in order):

• Up db_block_buffers - Cache hit ratio is a good indicator of usage, but not the entire picture. Many users can be banging away at some medium sized tables that are cached at 100%, but a bigger long running querys that runs less often could be at 30%. Also - it's hard to make this too big so that performance is reduced.
• Shared_pool_size - IF you are seeing a lot of sql being reparsed and/or data dictionary trashing
• Sort_area_size (and Sort_area_retained_size) - Each connected user get a hung of memory for sorting among other things. Up this if you are seeing a lot of disk sorts instead of memory sorts.
• Log_buffer - More here can really speed up redo log processes. Not too big however.

In general 'more memory good'. Disk I/O becuse of lack of memory allocated bad. Swap I/O becuse too much memory allocaed bad.

--
Doug Coan
Oracle Certified Professional DBA
AEGON USA
dcoan_at_aegonusa.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 08 2000 - 16:18:07 CST