Basic Oracle Tutorial: Setting the SGA command in the global zone of the system

Today, we will briefly introduce how to set up SGA.

1. sga_target Parameter
Alter system set sga_target = 40000 m scope = both;
Features:
1). ASMM automatic shared memory management
A: ASMM (automatic shared memory management) is a new feature of Oracle. However, it also indicates the maximum size of SGA, which is the same as that of SGA_MAX_SIZE, so it has a limit, that is, its size cannot be greater than the size of SGA_MAX_SIZE. Once a value is specified for SGA_TARGET (0 by default, that is, ASMM is not enabled ), the ASMM feature is automatically enabled.
2). Relationship between sga_target and sga_max_size
A: When SGA_TARGET <SGA_MAX_SIZE, oracle will ignore the value of SGA_MAX_SIZE. oracle's SGA and SGA_TARGET will prevail. It can dynamically change the size, but cannot exceed the value of SGA_MAX_SIZE, it can be smaller.
3). sga_target Dynamic Parameters
A: SGA_TARGET can be dynamically modified in this instance without restarting the database instance. The so-called dynamic parameter means that it can be directly modified to take effect in the memory, you do not need to restart the database to load parameter files.
4) which memory zones will be affected by ASMM automatic shared memory management?
A: When the new ASMM feature of Oracle is enabled, not all memory areas of SGA are dynamically shared. Only the memory sizes of the following areas can be dynamically shared, the memory size of other regions in SGA is still fixed and not shared.
* Buffer cache (DB_CACHE_SIZE)
* Shared pool (SHARED_POOL_SIZE)
* Large pool (LARGE_POOL_SIZE)
* Java pool (JAVA_POOL_SIZE)
* Streams pool (STREAMS_POOL_SIZE)

2. sga_max_size Parameter
Alter system set sga_max_size = 40000 m scope = spfile;
Features:
1). sga_max_size static parameter
It is used to control the maximum size of the virtual memory used by SGA. After the instance is started, each memory area only allocates the minimum size required by the instance. During subsequent running, expand their size as needed, and their total size is limited by SGA_MAX_SIZE. "To modify the size of SGA_MAX_SIZE, you must restart the database instance" because it is a static parameter. Static Parameters take effect in the spfile parameter file after modification, but do not take effect in the memory. Therefore, you must restart the database to load the parameter file to make it take effect.

3. db_cache_size parameter [database buffer cache]
Alter system set db_cache_size = 2000 m scope = both;
1 ). this parameter is a dynamic parameter used to cache the memory area of "valid data" in the database. The size of this memory area has a great impact on the data retrieval speed, if the retrieved data can be found in this area, it is much faster than finding data indirectly in the hard disk. So after the system is stable, you can set the value of this buffer statically.

4. scope = [memory | spfile | both]
1). If scope = memory, the modified parameters will only take effect in the memory. After the database is restarted, it will return to the original state value [used to modify dynamic parameters]
2 ). if scope = spfile indicates that the modified parameters only take effect in the parameter file, but the current environment does not take effect after the modification, you must restart the database to load the parameter file for the modification to take effect [used to modify static parameters]
3 ). if scope = both indicates that the modified parameters take effect in both the current environment (memory) and spfile (parameter file), both = memory + spfile. After the modification, the current environment takes effect, it also takes effect after the database is restarted.

Conclusion: only when we understand these important parameters in SGA can we allocate our limited memory space to maximize the performance of our system, it allows oracle to run faster and better.

Oracle SGA

Automatic Oracle SGA management feature (SGA_target parameter)

Enable sga large page (large page) in Oracle on AIX)

Modify the size of the Oracle database SGA and PGA

Oracle 10g Parameter Adjustment (processes and SGA)