René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
SGA related init params | ||
Auto tuned SGA parameters
These parameters are called auto tuned because
automatic shared memory managment can
dynamically change the sizes of these pools if it is enabled.
DB_CACHE_SIZE
The value of this parameter affects the size of the SGA: It
sets the size of the default buffer pool.
According to metalink note 223299.1, this is one of the top parameters affecting performance.
With Oracle 10g, one can turn on
Automatic Shared Memory Management which distributes shared memory as required. See also
sga_target.
JAVA_POOL_SIZE
The value of this parameter affects the size of the SGA.
With Oracle 10g, one can turn on
Automatic Shared Memory Management which distributes shared memory as required. See also
sga_target.
SHARED_POOL_SIZE
The value of this parameter affects the size of the SGA or more appropriately the
size of the shared pool within the SGA.
Apparently, when installing JServer, this parameter must at least be set to 24M.
LARGE_POOL_SIZE
With Oracle 10g, one can turn on
Automatic Shared Memory Management which distributes shared memory as required. See also
sga_target.
Manual SGA parameters
DB_KEEP_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
DB_RECYCLE_CACHE_SIZE
According to metalink note 223299.1, this is one of the top parameters affecting performance.
LOG_BUFFERUp to 9i
The value of this parameter defines the size of the
redo log buffer. As the redo log buffer is part of the
SGA, it affects the size of the SGA as well.
Setting this value to anything greater than 3M is useless as the log buffer is flushed
anyway when it is filled up to 1M or when it is reaches one third of its capacity, whichever comes first. (Thanks to William White who
notified me of an error here).
10g
From 10g onwards, there is a lot more to do about the initialization parameter log_buffer. Also, Metalink note 351857.1 states
that the size of the log buffer cannot be changed with this paramter from 10gR2, instead, the size will be set by Oracle.
STREAMS_POOL_SIZEOther parameters
See also other init parameters.
DB_BLOCK_BUFFERS
The value of this parameter affects the size of the SGA, or more precisely,
the size of the buffer cache. This
parameter is deprecated since 9i, db_cache_size should be used instead.
With Oracle 10g, one can turn on
Automatic Shared Memory Management which distributes shared memory (of which the buffer
cache is part) as required. See also sga_target.
DB_BLOCK_CHECKSUMDB_BLOCK_SIZE
Determines the size of a database blocks.
SGA_MAX_SIZE
sga_max_size sets the maximum value for sga_target
If sga_max_size is less than the sum of
db_cache_size +
log_buffer +
shared_pool_size +
large_pool_size
at initialization time, then the value of sga_max_size is ignored.
According to metalink note 223299.1, this is one of the top parameters affecting performance.
SGA_TARGET
This parameter is new with Oracle 10g. It specifies the total amaount of SGA memory available to an
instance. Setting this parameter makes Oracle distribute the available memory among various components - such as
shared pool (for SQL and PL/SQL), Java pool,
large_pool and buffer cache - as required.
This new feature is called Automatic Shared Memory Management.
With ASMM, the parameters
java_pool_size,
shared_pool_size,
large_pool_size and
db_cache_size need not be specified explicitely anymore.
sga_target cannot be higher than sga_max_size.
Thanks
Thanks to Guy Lambregts who helped me improving this page (section log_buffer and a typo).
|