RelationalDBDesign RelationalDBDesign

DB Creation   «Prev  Next»
Lesson 8Sizing the SGA
ObjectiveSize the SGA for the COIN Database

Sizing the (SGA)System Global Area

There are two key initialization parameters that affect the size of the SGA.
These are:
db_block_buffers: Controls the size of the database buffer cache
shared_pool_size: Controls the size of the shared pool

Both of these parameters can have a significant impact on database performance. The default value for db_block_buffers in Oraclei is 4000. That means that enough memory will be allocated to hold 4,000 database blocks in memory. Assuming a 4K block size, that translates into 4000 * 4K, which equals 16MB. The default shared_pool_size is also 16MB.
These values are more than enough for our small, single-user coin database, so add the following two lines to the initCOIN.ora file:

db_block_buffers = 4000
shared_pool_size = 16m

Memory considerations

It is important when sizing the SGA to consider how much memory you have on the machine and to know how much of that can be used for Oracle and how much is needed for the operating system. You do not want to allocate so much memory that the operating system is forced to page the SGA out to disk.
Whenever I size the SGA for a database, I look at other, similar databases, to see what values they use. Oracle also provides some recommendations in the default initialization file that comes with the starter database. Once your database is running, you can monitor cache hit ratio and monitor the key shared pool statistics, to determine whether you have enough memory allocated.

Create Database - Quiz

Click the Quiz link below to test what you have learned.
Create Database - Quiz