| Lesson 8 | Sizing the SGA |
| Objective | Size 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