Q

Ratio for SGA and database size

What is the ideal general ratio between SGA and the database size for good performance?

What should be the general ratio between SGA and the database for good performance? Let's say my database size is 20 to 30 GB.
There is no general ratio between the SGA size and the database size. Knowing your database is between 20 and 30 GB is not sufficient information to adequately determine how big your SGA should be. What defines an optimal SGA size is the usage patterns of your database, not the size of the database. You will need to have enough memory devoted to the various SGA components so as not to hamper your database performance. My 20 GB database may only need 200 MB for its buffer cache, whereas your 20 GB database may need 500 MB for its buffer cache.

You will have to pay attention to how each component of the SGA performs to decide the optimal size. The Oracle

Performance Tuning Guide for your version has details on how to do just this. Please look for this document here.

Oracle 10g does help by letting you devote a chunk of memory to the total SGA and then managing the individual components within the SGA for you. Simply set the SGA_TARGET parameter and Oracle will take care of the rest for you. As a rule of thumb, Oracle Corp. is recommending that you set SGA_TARGET to one-third of your database server's physical memory.

This was first published in July 2006

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close