Problem solve Get help with specific problems with your technologies, process and projects.

Increasing the size of the SGA

How can I increase the size of the SGA? What is the best setup? Example: The biggest table that I have has three million records, and we have a 40-user connection maximum per day.

The SGA is comprised of the total of shared_pool_size, db_block_buffers (or db_buffer_cache in 9i), log_buffer, large_pool_size, and java_pool_size combined. Depending on the version of Oracle you are running, you may have a limit on the maximum size of the SGA (if you're running 32-bit Oracle you will be limited to somewhere around 2-3 GB max; 64-bit is unlimited). To increase the size of the SGA you simply increase the values assigned to one or more of these parameters.

With the information you gave, I can't really tell you how big your SGA should be. Many people will look to give approximately 50-60% of their total physical memory to Oracle. For instance, if you had 4GB on the server, you would look at using 2 GB for Oracle (if you are only running one instance). I'd allocate anywhere from 128K to 512K for log_buffer, large pool and java pool setting can be minimal if you're not using MTS or Java but for example purposes, let's give 10M to each. The rest I'd split about evenly between the shared pool and the database buffer cache parameters.

There's no one formula that works all the time, every time... because every server configuration and Oracle version and user base is different. Try to give Oracle as much as you can and then watch your performance to see if you need to readjust periodically.

For More Information

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.