SUMMARY: SGA size -- should it be big or small

My original posting was:

>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?

The answer naturally is not a simple, clear cut one. The size of the SGA depends on the number of users, the application, and other variables. In some cases, a larger SGA might make performance worse. Many people suggested I check out the RDBMS Oracle Tuning Guide (which I didn't even know we had--the dba was hoarding it).

Here are some of the more useful answers I received:

>From: tdonnell_at_ncrsoph.Sophia.NCR.COM (Tom Donnelly)

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.

>From: hu_at_coulomb.pcc.oz.au (Peng Hu)

Apart from the UNIX system parameters you have mentioned, a set of ORACLE parameters you also wish to adjust. Those parameters are mainly the DB_ and DC_ parameters specified in your INIT.ORA file.

The values for those parameters are very application dependant. According to your setup (i.e. # of users, system memory, etc.), however, you should not increase your SGA to much so that memory swapping is avoided.

>From: "Jonathan Creighton" <jcreighta_at_us.oracle.com>

I think the amount of shared memory configured into your Unix kernel needs to be at least as large as you want to make the SGA. Otherwise, Oracle won't start at all. Beyond the SGA size, further increases in the amount of available shared memory won't increase performance (unless the SGA is also increased). And having too much shared memory configured could be a problem as it consumes space in the kernel...I don't know how much.

Oracle uses your configured shared memory for the SGA and the Fast two-task driver, so if you want to use the Fast driver you may want to configure a little extra.

>From: pihlab_at_hhcs.gov.au

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.

>From: "Jonathan Creighton" <jcreighta_at_us.oracle.com>

For many of these, SQL*DBA MONITOR provides statistics that you can use to tell if you need to increase them. The values for such parameters are strongly dependent on your application, so it's not possible to say 'just increase parameter bletch and your application will run faster', without knowing that bletch is a bottleneck. This is why I'd recommend you MONITOR while your application runs before increasing lots of init.ora parameters.

>From: Jonathan Creighton <jcreight_at_us.oracle.com>

It depends on the application. Before increasing the size of the SGA, you should collect statistics using the STATISTICS option of the SQL*DBA MONITOR command. These will tell you whether you need to increase the SGA size.

The tuning of the buffer cache is covered in the "Database Tuning" chapter, on page 20-11 of my DBA guide.

-- 
Peter Steele        Unix Services Manager            peter.steele_at_acadiau.ca 
Acadia Univ., Wolfville, NS, Canada B0P 1X0  902-542-2201  Fax: 902-542-4364