Oracle's setup system global Zone SGA Command

Today, a brief introduction to the target= "_self" >sga setting method

1.sga_target parameters

alter system Set Sga_target = 40000m Scope=both;

Characteristics:

1). ASMM Automatic shared memory management

A: Sga_target parameter control ASMM (automatic shared memory management) is a new feature of Oracle, but its meaning is the same as sga_max_size, it also represents the maximum size of the SGA, so it also has a limitation, that is, its size cannot be greater than sga_max_ Size, the ASMM feature is automatically started once the value is assigned to the Sga_target (default is 0, that is, the ASMM is not started)

2). Relationship between Sga_target and Sga_max_size

A: That is, when sga_target< sga_max_size, Oracle will ignore the value of Sga_max_size, Oracle's SGA and Sga_target, it can dynamically change size, but not greater than sga_max_ The value of size, which can be less than.

3). Sga_target Dynamic Parameters

A: Sga_target can be dynamically modified in this instance, do not need to restart the database instance, the so-called dynamic parameters can be directly modified that is in memory, do not need to restart the database to load the parameter file into effect.

4). ASMM what memory areas are affected by automatic shared memory management?

This paper url:http://www.bianceng.cn/database/oracle/201410/45636.htm

A: When Oracle's ASMM new features are enabled, the size of all memory areas that are not SGA is started to be shared dynamically, and only the following areas of memory size can be dynamically shared, while the memory size of other areas in the 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 parameters

Alter system set sga_max_size=40000m Scope=spfile;

Characteristics:

1). sga_max_size Static parameters

It is used to control the maximum size of the SGA using virtual memory, and when the instance is started, each memory area allocates only the minimum size needed for the instance, and in subsequent runs, expands their size as needed, and their total size is limited by sga_max_size. "To modify the size of the sga_max_size, you must restart the database instance" because it is a static parameter. The so-called static parameter refers to the modification that is in the SPFile parameter file in effect, not in memory, so must restart the database to load the parameter file to make it effective

3.db_cache_size parameter "Database buffer cache"

Alter system set db_cache_size=2000m Scope=both;

1). This parameter is a dynamic parameter, the memory area used to cache valid data that is in use in the database has a significant impact on the speed at which data is retrieved, and if the retrieved data is found in this area, it is much faster than if it is indirectly to the hard drive. So the value of this buffer can be set statically after the system is stabilized

4.scope = "Memory | SPFile | Both

1. If scope=memory describes that the modified parameters are only in memory, restart the database and revert to the original state value "for modifying dynamic Parameters"

2. If scope=spfile indicates that the modified parameter is only valid in the parameter file, the current environment does not take effect after the modification, and the database must be restarted to load the parameter file to make it effective "for modifying static parameters"

3. If Scope=both describes the modified parameters in the current environment (memory) and SPFile (parameter file) are in effect, both=memory+spfile, the current environment after the modification of the effective, restart the database will also take effect.

Summary: When we understand these important parameters in the SGA, we can easily allocate our limited memory space to maximize the performance of our system, so that Oracle runs faster and better.