Database Reference
In-Depth Information
SGA Memory Management
The SGA memory-related parameters are classified into one of two areas:
•
Auto-tuned SGA parameters
: Currently these are
DB_CACHE_SIZE
,
SHARED_POOL_SIZE
,
LARGE_POOL_SIZE
,
JAVA_POOL_SIZE
, and
STREAMS_POOL_SIZE
.
•
Manual SGA parameters
: These include
LOG_BUFFER
,
DB_NK_CACHE_SIZE
,
DB_KEEP_CACHE_SIZE
,
and
DB_RECYCLE_CACHE_SIZE
.
At any time in Oracle 10
g
and above, you may query
V$SGAINFO
to see which components of the SGA
are resizable:
EODA@ORA12CR1> select * from V$SGAINFO;
NAME BYTES RES CON_ID
-------------------------------- ---------- --- ----------
Fixed SGA Size 2287336 No 0
Redo Buffers 4890624 No 0
Buffer Cache Size 67108864 Yes 0
Shared Pool Size 184549376 Yes 0
Large Pool Size 4194304 Yes 0
Java Pool Size 4194304 Yes 0
Streams Pool Size 0 Yes 0
Shared IO Pool Size 4194304 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
Maximum SGA Size 267227136 No 0
Startup overhead in Shared Pool 169940696 No 0
Free SGA Memory Available 0 0
13 rows selected.
For the SGA memory components that can be auto-tuned, there are three ways to manage these:
•
Manual shared memory management
: Setting all of the necessary pool and cache parameters.
•
Starting in Oracle 10g and above, automatic shared (or SGA) memory management
: Setting the
SGA_TARGET
parameter. By setting the
SGA_TARGET
parameter, you are allowing the instance to
size and resize various SGA components.
•
Starting in Oracle 11g and above, automatic memory management
: Setting the
MEMORY_TARGET
parameter. By setting the
MEMORY_TARGET
parameter, you are allowing the instance to size and
resize the SGA and PGA memory areas.
We will discuss each in turn.
Manual Shared Memory Management
If you require some degree of control over the auto-tunable areas of the SGA memory, then set
MEMORY_TARGET
and
SGA_TARGET
to zero. When
MEMORY_TARGET
is set to zero, this disables automatic memory management; when
SGA_
TARGET
is set to zero, this disables automatic shared memory management.
