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

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.

• Monitoring the Cache Hit Ratio
  The key performance statistic related to the database buffer cache is the cache hit ratio. This tells you how often Oracle has to read a data block from disk as opposed to finding it already in memory, in the database buffer cache. The greater the percentage of blocks found in memory, the better your performance will be. The following SQL query will return the cache hit ratio for a running Oracle database:
  

   
  SELECT 1- (c.value / (a.value + b.value))
         cache_hit_ratio
  FROM v$sysstat a, v$sysstat b, v$sysstat c
  WHERE a.name = 'db block gets'
    AND b.name = 'consistent gets'
    AND c.name = 'physical reads';
  

  
  You normally need to be logged in as SYS or SYSTEM in order to run this query. It is generally accepted that a value of 0.90 or higher represents a well-tuned system. If your cache hit ratio is below 0.90, and you have the memory to spare, you might try increasing the db_block_buffers parameter to see whether you get an improvement.

There are at least two statistics related to the shared pool that you should monitor on a regular basis:

1. the GETHIT ratio and
2. the GETMISS ratio .

The gethit ratio tells you how often a SQL statement, or PL/SQL block, is found in the shared pool, already parsed, and with an execution plan that can be reused. This query returns the gethit ratio for several different object types:

SELECT namespace, gethitratio
FROM v$librarycache;

The closer the gethit ratio is to 1, the better. Values above 0.90 are generally accepted as indicators of good performance. A value of 0.90 means that only 10% of SQL statements need to be parsed, which means the shared pool is large enough to hold the 90% that are most frequently used. Another statistic to look at is the getmiss ratio. This applies to the data dictionary cache, and tells you how often Oracle has to go to disk for data dictionary information as opposed to finding that information already in memory, in the shared pool. The following query will give you the getmiss ratio:

SELECT SUM(getmisses)/SUM(gets) getmiss_ratio

FROM v$rowcache;

In this case, lower values are better. Oracle recommends keeping this number below 0.15.

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