SGA_TARGET vs 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.


In simple terms sga_target is less than or equal to sga_max_size


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



By using one parameter we don't need to use all other SGA parameters like.

• DB_CACHE_SIZE (DEFAULT buffer pool)

• SHARED_POOL_SIZE (Shared Pool)

• LARGE_POOL_SIZE (Large Pool)

• JAVA_POOL_SIZE (Java Pool)


Enable SGA_TARGET

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0 ---------------As value is zero is not enabled

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 600M


As we can see out automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

SQL> alter system set sga_target=400m;
System altered.

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- -------
sga_target big integer 400M



Resize SGA_TARGET


• 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

SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_max_size big integer 600M

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_target big integer 400M


SQL> alter system set sga_target=500m


WE can resize it to only 600m if we will try to increase it from 600m we will get error.


SQL> alter system set sga_target=605m;

alter system set sga_target=605m *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
For that we must first increase our SGA_MAX_SIZE parameter value.But we must restart out instance because its STATIC parameter.
SQL> alter system set sga_max_size=956 scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size 1337492 bytes
Variable Size 624953196 bytes
Database Buffers 369098752 bytes
Redo Buffers 4800512 bytes
Database mounted.
Database opened.

SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- --------
sga_max_size big integer 956M

SQL> alter system set sga_target=900m;
System altered.

Disable SGA_TARGET

We can Disable our automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.
SQL> alter system set sga_target=0;

System altered.