First, an overview of the “Oracle memory structure of-PGA Chapter” mentioned in the article, PGA is a dedicated server process private memory area, while the SGA is the shared memory area.
SGA by a number of parts:
1, the fixed SGA (Fixed SGA)
2, block buffer (Db cache)
3, redo log buffer (Redo log buffer)
4, Java pool (Java pool)
5, large pool (Large pool)
6, shared pool (Shared pool)
7, stream pool (Stream pool)
The following parameters to control the size of shared pool of related components:
1, JAVA_POOL_SIZE: Control Java pool size.
2, SHARED_POOL_SIZE: 9i shared pool to control the largest part of the occupier, 10g shared pool size over the control.
3, LARGE_POOL_SIZE: control of large pool size.
4, DB_ * K_CACHE_SIZE: control the size of the different pieces of the buffer size.
5, LOG_BUFFER: redo log buffer size control.
6, SGA_TARGET: 10g or more automatic SGA memory management control of the total memory size.
7, SGA_MAX_SIZE: can control the maximum SGA size, need to restart the database to change.
The following will describe in more detail the role of the various parts and recommended settings.
Second, SGA role of the various components
1, the fixed SGA:
As the name suggests, is a change of memory areas, in other parts of SGA point, Oracle, through its SGA to find other areas, can be simply understood as a section of memory used to manage the district.
2, block buffer:
Query, Oracle will first read the data from the disk Add memory, later inquiries related data do not read the disk again. Insert and update, Oracle will cache data in the area, and then wrote the hard drive volume. Buffer through the block, Oracle can increase the disk cache memory of the I / O performance.
Block buffer in three regions:
default pool (Default pool): By default all the data in the cache here.
maintain pool (Keep pool): used to cache data reuse many times.
recycling pool (Recycle pool): rarely used to cache data reuse.
There is only one pool of the original default, all data in the cache here. This will have a problem: a large number of very little reuse of data will need to reuse the data “out of” buffer zone, resulting in disk I / O increase, decrease speed. Later, the separation and recovery of maintaining pool pool often re-used depending on whether the data cache, respectively.
This three-part memory areas need to manually determine the size, and are not shared. For example: keep the pool has been filled, and the recovery of a large number of free memory pool, then the recovery of the memory pool will be allocated to maintain the pool.
9i onwards, can also set db_nk_cache. Before 9i database can use the same block size. 9i to start a database can be used with a wide range of block size (2KB, 4KB, 8KB, 16KB, 32KB), these blocks need to db_nk_cache in their cache. If the table space for different designated a different block size, the need to set their own buffer zone.
3, redo log buffer (Redo log buffer):
Data writes redo log files in the cache before, the trigger in the following situations:
every 3 seconds
reached 1MB cache, or 1 / 3 full
When users submit
buffer before the data is written to disk
4, Java pool (Java pool):
In the database used to run Java code in this part of memory. For example: the preparation of Java stored procedures running on the server. Note that the Java memory and prepared common B / S system is not the relationship. JAVA language instead of using PL / SQL language in the database will be used to write stored procedures in this part of memory.
5, large pool (Large pool):
The use of the following three conditions to the large pool:
parallel execution: the storage process of the buffer zone between the news
RMAN: under certain circumstances for disk I / O buffer
shared server mode: shared server mode, the distribution of UGA in the pool (if set up a large pool)
6, shared pool (Shared pool)
SGA shared pool is the most important one of the memory segment. Shared pool too big and too small will seriously affect server performance.
SQL and PL / SQL to explain the plan, code, data, data dictionary cache and so on are here.
SQL and PL / SQL code will be conducted before the implementation of “hard analysis” to obtain permission to verify the implementation of plans and operations and other related auxiliary. “Hard analysis” It is time-consuming. Very short response time for inquiries, “hard to resolve” all the time can be accounted for 2 / 3. Longer response time for the statistics operation, “hard analysis” of the time occupied by the ratio will drop a lot. Implementation plan and the necessary data dictionary cache in a shared data pool, so follow-up to the same query can be reduced a lot of time.
Do not use “bind variable” result in:
system needs to spend a lot of resources to resolve queries.
never shared pool of code reuse, the system spent a large part of the cost of memory management.
On to discuss the advantages and disadvantages of shared variables has exceeded the scope of this article, simply speaking, the short response time of queries to use shared variables, response time statistics do not use shared variables.
It should be noted that, SHARED_POOL_SIZE control parameters in 9i shared pool occupied the largest part, 10g more than control the total size of shared pool.
7, stream pool (Stream pool)
An increase of more than 9iR2 “streaming” technology, 10g more than the increase in the SGA in the stream pool. Flow is used to copy the data sharing and tools.
Third, SGA did not set up common settings, all settings should be based on system load, operational needs and hardware environment to be adjusted. Here only a general summary of the settings, SGA to avoid the problems caused by improper settings.
1, automatic SGA memory management in Oracle 10g introduced Automatic SGA memory management features, DBA can set SGA_TARGET the SGA can be used to tell Oracle how much memory, according to system load from Oracle to dynamically adjust the size of the components, the corresponding number will be stored in the control file, restart the database after they remember the size of the components.
Need to note the following points:
To use the automatic SGA memory management, STATISTICS_LEVEL parameters must be set to TYPICAL or ALL, the system automatically collects information for the corresponding set of dynamic adjustment of SGA.
can set the value of a component, Oracle components use this value for the smallest size
Dynamically adjust the parameters:
DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE.
Need to manually set the parameters:
LOG_BUFFER, STREAMS_POOL, DB_NK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE.
2, manual SGA memory management
1) 32bit and 64bit limit of 32-bit operating system, Oracle’s largest memory available for the 1.75g, that is to say SGA + PGA <= 1.75g, the memory exceed this limit will not be used in Oracle.
32 can be loaded into the Oracle operating system 64, the 64-bit Oracle can not load 32-bit operating systems.
2) See Oracle version:
SQL> select * from v $ version;
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL / SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
3) the component settings:
JAVA_POOL_SIZE: If useless java client to the database system, 30MB enough.
LOG_BUFFER: defaults to MAX (512KB, 128KB * CPU number). 1MB general system enough to run large-scale affairs can be set to 2MB, so that 1 / 3 full into the log files can continue to write buffer, and then there is no great significance.
SHARED_POOL_SIZE: too much too small will seriously affect the system performance, 1GB of memory can be set to 100MB, 2GB of memory can be set to 150MB, 4GB of memory can be set to 300MB. First of all, hit shared pool is too low to adjust the application rather than the expansion of shared pool. The use of bind variables can reduce the demand for shared pool to improve the hit rate and reduce the burden-sharing pool of management and competitive LATCH.
LARGE_POOL_SIZE: the use of a dedicated service model can be set to 30MB, unless necessary, or does not recommend the use of shared server mode.
DB_CACHE_SIZE: to remove the above-mentioned memory can be used other than memory are allocated to the region.
1G memory: SHARED_POOL_SIZE = 100MB, DB_CACHE_SIZE = 0.5GB;
2G memory: SHARED_POOL_SIZE = 150MB, DB_CACHE_SIZE = 1.25GB;
4G memory: SHARED_POOL_SIZE = 200MB, DB_CACHE_SIZE = 2.5GB;
8G memory: SHARED_POOL_SIZE = 400MB, DB_CACHE_SIZE = 5GB;
12G memory: SHARED_POOL_SIZE = 500MB, DB_CACHE_SIZE = 8GB
To stress once again that more than just avoid the problem caused by improper SGA general settings, the system according to the specific load and operational tools such as the logic of combining fine-tune Stackpack.