Re: SGA size -- should it be big or small?
Date: 7 Apr 93 08:41:48 GMT
Message-ID: <4368_at_ncrsoph.Sophia.NCR.COM>
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?
>--
>Peter Steele Unix Services Manager peter.steele_at_acadiau.ca
>Acadia Univ., Wolfville, NS, Canada B0P 1X0 902-542-2201 Fax: 902-542-4364
Seriously consider increasing the size of your SGA with that number of users. Depending on the size of your average row, number of accesses, etc, improvements of performance can be expected by increasing the number of DB_BLOCK_BUFFERS, CACHE parameters, and the read counts. Other parameters which could affect your performance include DDL and DML locks, latches, etc.
Check the ORACLE Performance Tuning Guide out. As for overall size of the SGA, I tend to make mine as large as possible given the following constraints -
- Keep the SGA small enough to fit in RAM without swapping
- Increase the SGA siye in small increments until there is no more or just a little gain in performance in relation to the increase.
- Keep under the magic 20MB limit. ORACLE (at least they used to) wants you to call them if you plan on going above a 20MB SGA.
As for the UNIX Tuneables, SHMALL and SHMMAX must be large enough to fit the entire SGA. ORACLE will crash if it has to use multiple segments in the SGA. For 50 concurrent users, your system may be light on memory. Unix takes up about 12 to 18MB (not sure exactly on SPARC), about 12to 16 for your SGA, and according to ORACLE you need 2-3MB RAM per concurrent user. Thats pretty tight. After you configure the kernel check your swapping. If its high get more memory. As for the other tuneables, check your sar reports. If you are running low on resourse than increase them.
Most of my experince is on ATT Unix V R3 and R4, running on AT&T 3B2s, StarServers and Pyramids, as well as NCR 3000s.
Good Luck
These views are my own and do not represent those of my company.
Tom Donnelly | WIN: Tom.Donnelly_at_Sophia NCR Europe Group ISS | UUCP: ..ncrcom!ncrsoph!tdonnell Sophia Antipolis, Valbonne | France | TEL: 33-92 94 45 40Received on Wed Apr 07 1993 - 10:41:48 CEST