SGA target
SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
Parameter description:
SGA_TARGET Property Description Parameter type Big integer Syntax SGA_TARGET = integer [K | M | G] Default value 0 (SGA autotuning is disabled) Modifiable ALTER SYSTEM Range of values 64 to operating system-dependent Basic Yes
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 SQL> show parameter sga_max_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 600M
As we can see our automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.
SQL> alter system set sga_target=500m; System altered. SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- ------- sga_target big integer 500M
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 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=956m 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.
Be careful when you are using 32 bit system, sga_max_size should be less than 4GB otherwise Oracle instance will fail to startup. Also in 32 bit system, it is observed that total sga+total pga should be less than the actual physical memory available.
It appear that on Windows 32 bits, maximum size for (SGA + PGA + Oracle memory for connection) must be < 2000 MO
On Windows 32 bits with Windows "/3GB" and "/PAE" options (Windows 2003 Server and later), maximum size for (SGA + PGA + Oracle memory for connection) must be < 3000 MO
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.
