The Oracle system global area (SGA) is just shared memory structures that are created at
instance startup, hold information about the instance and control its
stated, the system global area (SGA) is just shared memory structures that are created at
instance startup, hold information about the instance and control its behavior. The
following figure gives a brief synopsis of the particular components of the SGA. Note,
that the blue components are managed by AMM, whereas the
red component is manually managed.
can be seen from this figure, is that
there are many options available to
setup the SGA and without a complete understanding of what the applications are doing in
the background, the ability to guess
the appropriate amount of memory to give each of these individual components is not
always optimal. AMM can solve this complex configuration part - but gives the possibility
to size an extra, manually sized buffer cache for objects within the extra tablespace. In
our example, we used 16K, but Oracle
offers additional blocksizes of 2K, 4k, 8k, 16k and 32K.
Components of the SGA
Size controlled by
Areas of Influence
- Shared SQL areas
- PL/SQL procedures
- Various control
Oracle needs to allocate & deallocate
memory as SQL or procedural code is executed based on the individual needs of
users' sessions and in accordance to the LRU algorithm.
- Row cache
- Library cache
Highly accessed memory structures that provide
information on object structures to SQL statements being parsed.
- Run state
- Session code
- Data in JVM
Memory available for the Java memory manager
to use for all things Java.
- Stream activity
New to Oracle 10g, memory available for stream
Redo Log Buffer
- Redo entries
Holds changes made to data and allows for the
reconstruction of data in the case of failure.
DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_KEEP_CACHE_SIZE
- Write list
- LRU list
Holds copies of data requested by SQL and
reduces requests to disk by having data in memory. You may have many different
buffer caches that help segregate on usage patterns.
- Shared server
- Oracle XA
- I/O server
- Backup &
For large memory allocations.
can look at the size of your SGA by looking at the initialization parameters that control
its size. Here is a simple query and its output.
Automatically managed SGA
is really nothing to switching into automatic shared memory tuning. You only need to set
the SGA_TARGET parameter in the INIT.ORA.
# Using Automatic SGA
sga_target = 300M
sga_max_size = 400M
shared_pool_size = 0
java_pool_size = 0
large_pool_size = 0
Multiple Block Sizes
Oracle supports multiple block sizes as
It has a standard block size, as set by the
DB_BLOCK_SIZE initialization parameter. The standard block size is used for
the SYSTEM tablespace and most other tablespaces.
The most commonly used block size
should be picked as the standard block size. In many cases, this is the only block
size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K.
If not specified, the default data block size is operating system specific, and is
The block size cannot be changed after database creation, except by re-creating the
database. If a database's block size is different from the operating system block
size, make the database block size a multiple of the operating system's block
For example, if your operating system's block size is 2K (2048 bytes), the
following setting for the DB_BLOCK_SIZE initialization parameter is
DB_BLOCK_SIZE = 4096
Additionally up to 4
nonstandard block sizes. Nonstandard block sizes are specified when creating
tablespaces. Multiple block size support allows for the transporting of tablespaces
with unlike block sizes between databases.
Tablespaces of non-standard block sizes can be
created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE
clause. These non-standard block sizes can have any power-of-two value between
2K and 32K: specifically, 2K, 4K, 8K, 16K or 32K. Platform-specific restrictions
regarding the maximum block size apply, so some of these sizes may not be allowed
on some platforms.
To use non-standard block sizes, you must
configure sub-caches within the buffer cache area of the SGA memory for all of the
non-standard block sizes that you intend to use
Configure sub-caches for non-standard block
If you intend to use multiple block sizes in your
database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE
parameter set. Oracle assigns an appropriate default value to the DB_CACHE_SIZE
parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size
caches are configured.
The DB_CACHE_SIZE initialization parameter replaces the
DB_BLOCK_BUFFERS initialization parameter that was used in previous releases. The
DB_CACHE_SIZE parameter specifies the size of the cache of standard block size buffers,
where the standard block size is specified by DB_BLOCK_SIZE. However, this have
only be done if you don't use AMM.
The sizes and numbers of non-standard block size
buffers are specified by the following initialization parameters:
Each parameter specifies the size of the buffer
cache for the corresponding block size.
DB_BLOCK_SIZE = 4096
DB_CACHE_SIZE = 12M
DB_2K_CACHE_SIZE = 8M
DB_8K_CACHE_SIZE = 4M
In the above example, the parameters specify that
the standard block size of the database will be 4K. The size of the cache of standard
block size buffers will be 12M. Additionally, 2K and 8K caches will be configured with
sizes of 8M and 4M respectively.
These parameters cannot be used to size the cache
for the standard block size. For example, if the value of DB_BLOCK_SIZE is 2K, it is
illegal to set DB_2K_CACHE_SIZE. The size of the cache for the standard block size is
always determined from the value of DB_CACHE_SIZE.
Database 10.2.0.3, Standard Blocksize with 4K, automatically managed SGA (AMM) but
with extra tablespace with 16K Blocksize for very wide tables.
### Basic Configuration Parameters
### Database Buffer Cache, I/O
# The Parameter SGA_TARGET enables Automatic Shared Memory Management
# Using Automatic SGA Management
Creating the tablespace with nonstandard block sizes
SIZE 50M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
Check Sizes in Oracle Enterprise Manager
You can see, the manually configured 16K buffer
cache and all other automatically sized components.