What is
SGA_TARGET and SGA_MAX_SIZE ?
SGA_MAX_SIZE is the
largest amount of memory that will be available for the SGA in the instance and
it will be allocated from memory. You do not have to use it all, but it will be
potentially wasted if you set it too high and don’t use it. It is not a dynamic
parameter. Basically it gives you room for the Oracle instance to grow.
SGA_TARGET is actual memory
in use by the current SGA. This parameter is dynamic and can be increased up to
the value of SGA_MAX_SIZE.
SGA_MAX_SIZE and
SGA_TARGET both are the parameter are used to change the SGA SIZE.
SGA_MAX_SIZE sets the
maximum value for sga_target.
SGA_TAGET is 10G feature
used to change the sga size dynamically .it specifies the total amount of SGA
memory available to an instance.
this feature is called
Automatic Shared Memory Management. With ASMM, the parameters java_pool_size,
shared_pool_size, large_pool_size and db_cache_size are affected.
SGA_MAX_SIZE &
SGA_TARGET
SGA_MAX_SIZE sets the
overall amount of memory the SGA can consume but is not dynamic.
The SGA_MAX_SIZE
parameter is the max allowable size to resize the SGA Memory area parameters.
If the SGA_TARGET is set to some value then the Automatic Shared Memory
Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the
SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
I.e. If SGA_MAX_SIZE=4GB
and SGA_TARGET=2GB, later period of time, if you want you can resize your
SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can’t resize
the SGA_TARGET value to more than 4GB.
It is significant that
SGA_TARGET includes the entire memory for the SGA, in contrast to earlier
releases in which memory for the internal and fixed SGA was added to the sum of
the configured SGA memory parameters. Thus, SGA_TARGET gives you precise
control over the size of the shared memory region allocated by the database. If
SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the
latter is bumped up to accommodate SGA_TARGET
Do not dynamically set
or unset the SGA_TARGET parameter. This should be set only at startup.
SGA_TARGET is a database
initialization parameter (introduced in Oracle 10g) that can be used for
automatic SGA memory sizing.
SGA_TARGET provides the
following:
§ Single parameter for
total SGA size
§ Automatically sizes
SGA components
§ Memory is transferred
to where most needed
§ Uses workload
information
§ Uses internal advisory
predictions
§ STATISTICS_LEVEL must
be set to TYPICAL
§ SGA_TARGET is dynamic
§ Can be increased till
SGA_MAX_SIZE
§ Can be reduced till
some component reaches minimum size
§ Change in value of
SGA_TARGET affects only automatically sized components
--------------------------------------------------------------
what will happen
if SGA_TARGET =0 ?
Disable automatic SGA
tuning by setting sga_target=0
Disable ASMM by setting
SGA_TARGET=0
http://www.orafaq.com/wiki/SGA_target
SGA_TARGET is a database
initialization parameter (introduced in Oracle 10g) that can be used for
automatic SGA memory sizing.
Default value 0 (SGA
auto tuning is disabled)
--------------------------------------------------------------
What is
PGA_AGGREGRATE_TARGET parameter?
PGA_AGGREGATE_TARGET
specifies the target aggregate PGA memory available to all server processes
attached to the instance
--------------------------------------------------------------
What parameter is useful for refreshing materialized views?
JOB_QUEUE_PROCESSES. Default value=0
What parameter is useful for refreshing materialized views?
JOB_QUEUE_PROCESSES. Default value=0
No comments:
Post a Comment