The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM).

All Oracle processes use the SGA to hold information. The SGA is used to store incoming data (the data buffers as defined by the db_cache_size parameter), and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle “initialization parameters”.  These might include db_cache_size, shared_pool_size and log_buffer.

In Oracle Database 10g you only need to define two parameters (sga_target and sga_max_size) to configure your SGA. If these parameters are configured, Oracle will calculate how much memory to allocate to the different areas of the SGA using a feature called Automatic Memory Management (AMM). As you gain experience you may want to manually allocate memory to each individual area of the SGA with the initialization parameters.

Google: ”oracle amm”

We have already noted that the SGA was sub-divided into several memory structures that each have different missions. The main areas contained in the SGA that you will be initially interested in have complicated names, but are actually quite simple:

* The buffer cache (db_cache_size)

* The shared pool (shared_pool_size)

* The redo log buffer (log_buffer)

Let’s look at these memory areas in more detail.

Note:  AMM and dynamic Oracle memory management has measurable overhead.  See my important notes on

Oracle dynamic memory management.

Inside the Data Buffer Cache

The Buffer Cache (also called the database buffer cache) is where Oracle stores data blocks.  With a few exceptions, any data coming in or going out of the database will pass through the buffer cache.

The total space in the Database Buffer Cache is sub-divided by Oracle into units of storage called “blocks”. Blocks are the smallest unit of storage in Oracle and you control the data file blocksize when you allocate your database files.

An Oracle block is different from a disk block.  An Oracle block is a logical construct -- a creation of Oracle, rather than the internal block size of the operating system. In other words, you provide Oracle with a big whiteboard, and Oracle takes pens and draws a bunch of boxes on the board that are all the same size. The whiteboard is the memory, and the boxes that Oracle creates are individual blocks in the memory. 

Each block inside a file is determined by your db_block_size parameter and the size of your “default” blocks are defined when the database is created. You control the default database block size, and you can also define tablespaces with different block sizes.  For example, many Oracle professionals place indexes in a 32k block size and leave the data files in a 16k block size.

Google: ”oracle multiple blocksizes”

When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. This practice allows to server to avoid unnecessary I/O. In an ideal world, DBAs would be able to create one buffer for each database page, thereby ensuring that Oracle Server would read each block only once.

The db_cache_size and shared_pool_size parameters define most of the size of the in-memory region that Oracle consumes on startup and determine the amount of storage available to cache data blocks, SQL, and stored procedures.

Google: ”oracle sga size”

The default size for the buffer pool (64k) is too small. We suggest you set this to a value of 1m when you configure Oracle.  

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It’s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.