Oracle SGA Concepts
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.
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
dynamic memory management. Also see my
notes on the Oracle
User Global Area (UGA).
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
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
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.