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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our applications, PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.