Sizing the (SGA) System Global Area

Back in Lesson 4, initCOIN.ora already picked up memory_target=2G as one of its starter parameters, which means COIN is already using Automatic Memory Management, Oracle sizing the SGA and PGA together from a single instance-wide target, without you hand-tuning individual components. That's a perfectly good choice and needs no changes. This lesson explains what that setting actually does, what the modern alternative looks like, and how to check whether whatever you've configured is actually sized well once the database is running.

With memory_target set, Oracle automatically redistributes memory between the SGA and the instance PGA as workload demands change, and individual SGA components, the buffer cache, shared pool, and others, are auto-tuned within that total. You don't set db_cache_size or shared_pool_size individually; Oracle handles the split.

Oracle AI Database 26ai introduces a related but distinct approach called Unified Memory, controlled by a single parameter, MEMORY_SIZE, instead of separately tuning SGA_TARGET and PGA_AGGREGATE_LIMIT. It's aimed specifically at consolidated, multi-workload environments and reduces or eliminates the need to restart the instance when memory configuration changes.
Worth knowing if you ever touch this setting: MEMORY_SIZE and MEMORY_TARGET are mutually exclusive, not layered on top of each other. If MEMORY_SIZE is set to a nonzero value, MEMORY_TARGET and MEMORY_MAX_TARGET need to be 0; setting either of them to a nonzero value alongside an active MEMORY_SIZE gets silently ignored, with only a warning written to the alert log, not an error. MEMORY_SIZE also has a minimum of 1536 MB (1.5 GB), well under COIN's existing 2G setting, so switching would be technically possible, but there's no need to for a small training database. This is genuinely useful to know for a real 26ai deployment: for COIN specifically, the existing memory_target=2G is fine exactly as it stands.

Older material, including earlier editions of this very course, taught manual SGA sizing through two parameters:

db_block_buffers:	Legacy parameter for buffer cache size
shared_pool_size:	Controls the size of the shared pool

db_block_buffers is worth understanding but not worth using today. Its actual default value is 0, not some populated number waiting to be tuned, and more importantly, it cannot coexist with the dynamic db_cache_size parameter at all: setting both in the same parameter file produces an outright error. It's a relic of manual, pre-dynamic-SGA-management Oracle, the same category as the old manual PGA sizing parameters already retired earlier in this course. If you ever do want to size the buffer cache by hand rather than automatically, the modern equivalent is db_cache_size. To reproduce the classic example this lesson used to teach, 4,000 buffers at a 4K block size, roughly 16MB, you'd write:

db_cache_size = 16m
shared_pool_size = 16m

But for COIN, and for most databases today, there's no real reason to do this by hand when automatic memory management already handles it well. This manual example exists here purely so you recognize the pattern if you encounter it in older documentation or an inherited system, not as something to actually add to initCOIN.ora.

Whichever approach you use, it's important to consider how much memory the machine actually has, and how much of that Oracle can reasonably claim without starving the operating system. You don't want to allocate so much memory that the OS is forced to page the SGA out to disk, which defeats the entire purpose of having it in memory. When sizing a real database's SGA, look at similar existing databases for a sense of scale, and lean on Oracle's own advisor views once the database has been running under representative load rather than guessing. Once your database is running, you can monitor cache hit ratio and monitor the key shared pool statistics, to see whether you actually have enough memory allocated.

• Monitoring the Cache Hit Ratio
  The key performance statistic related to the database buffer cache is the cache hit ratio. It tells you how often Oracle finds a data block already in memory versus having to read it from disk. The higher the percentage found in memory, the better your performance. This query returns 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 to run this. A value of 0.90 or higher is generally accepted as a well-tuned system. If your ratio sits below 0.90 and you have memory to spare, that's a reasonable signal to look at increasing your memory allocation, whether that means raising memory_target, memory_size, or, if you're managing components manually, db_cache_size specifically.

There are at least two shared pool statistics worth monitoring regularly:

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, with a reusable execution plan. This query returns the gethit ratio across several object types:

SELECT namespace, gethitratio
FROM v$librarycache;

The closer to 1, the better. Values above 0.90 generally indicate good performance, a 0.90 means only 10% of SQL statements need to be freshly parsed, which means the shared pool is large enough to hold the 90% that get reused most often. The other statistic worth watching is the getmiss ratio, which applies to the data dictionary cache and tells you how often Oracle has to go to disk for dictionary information rather than finding it already in the shared pool:

SELECT SUM(getmisses)/SUM(gets) getmiss_ratio
FROM v$rowcache;

Here, lower is better. Oracle recommends keeping this number below 0.15.

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