Re: SGA size -- should it be big or small?

In article <1993Apr6.111813.5348_at_dragon.acadiau.ca>, peter_at_dragon.acadiau.ca (Peter Steele) writes:
> What are the ramifications on Oracle performance if I increase the
> size of the SGA? Right now, I have the SGA on our Sparc-10 system
> set at 4MB. This is a system that will run a dedicated Oracle
> application supporting as many as 50 simultaneous users. Would
> performance be improved if the SGA is increased to, say, 8M. The
> Sparc-10 has 128MB of memory. These are the kernel parameters I've
> defined:
>
> options SHMSIZE=4096 # maximum shared memory segment size (in Kbytes)
> options SEMMNI=26 # of semaphore identifiers
> options SEMMNS=500 # of semaphores in system
> options SEMUME=10 # max # of undo entries per process
> options SEMMNU=135 # of undo structures in system
>
> Are there other parameters I should change? Is there a performance
> document archived somewhere describing any of this?

I don't know much about Sparcs but ...

If you've got Oracle V6+ then you should have a tuning manual with it.

The advantages of a large SGA are that you can give your users more DB_BLOCK_BUFFERS (database buffers); the more you have the less disk activity you will have and the faster the queries will run. Bare in mind that there are trade offs between high CPU activity versus high DISK activity and also that once you get to a certain number of buffers you won't get much more improvement by adding additional buffers. You will have to find your best balance and the tuning manual tells you about it.

Be aware that each user maps the SGA into their own memory address space (logicly) so in a VMS environment it means a much larger paging file is needed.

Also make sure that your DC_* parameters (dictionary cache) are large enough otherwise important dictionary details won't be in memory and you'll incur additional I/Os to get them.

-- 

Bruce...        pihlab_at_hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************