This content has been marked as final. Show 7 replies
Oracle Database start instances with minimal memory and allow the instance to use more memory by expanding the memory allocated for SGA components, up to a maximum determined by the SGA_MAX_SIZE initialization parameter.
If the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file (SPFILE) is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE.
The SGA_TARGET initialization parameter reflects the total size of the SGA and includes memory for the following components:
Fixed SGA and other internal allocations needed by the Oracle Database instance
The log buffer
The shared pool
The Java pool
The buffer cache
The keep and recycle buffer caches (if specified)
Nonstandard block size buffer caches (if specified)
The Streams pool
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
Please visit here
Additionally you can visit here:
Is it possible to alter the value of sga_max_size
it is possible and if you are using spfile then
ALTER SYSTEM SET sga_max_size=<value> SCOPE=SPFILE|MEMORY|BOTH;
What about sga_target?
can i give the command like this?
alter system set sg_target=<value > .
SGA_TARGET is Modifiable with ALTER SYSTEM.
SQL> alter system set sga_target=100M;Adith
SGA_MAX_SIZE is the size of shared memory that will be allocated at startup of the instance.
If the operating system supports virtual shared memory, not all of the shared memory comes from physical memory, but the operating system believes it is all allocated.
SGA_MAX_SIZE can be set at any time, but does not take effect until next instance restart.
SGA_TARGET is the maximum shared memory that will be allocated by the Memory Manager, especially in management of the automatically adjusted pools.
The maximum value for SGA_TARGET is SGA_MAX_SIZE.
SGA_TARGET can be reset at any time, and takes effect immediately if SCOPE=CURRENT or BOTH. However, it may take some time for the target to be reached especially if the new target is lower and th system has used area 'above' the new target. That area needs to be flushed, and that flush can only occur after all transactions are finished using the area.