SGA - Oracle's system global space
SGA - data cache pool (DB Buffer)
| |--------- Default buffer pool
| |--------- Maintain the buffer pool
| |--------- Regeneration buffer pool
| --- Shared cache pool
| |-------- Library cache
| | |------ Shared SQL area (compiled SQL)
| | |------ PL / SQL areas (procedures, functions, etc. ..)
| |-------- Data dictionary cache (common table names, column names, etc. used to resolve the contents of SQL statements)
| --- Redo log buffer
| --- Large shared area (optional feature)
| --- Fixed SGA
Memory structure and dynamic memory management
Memory is an important factor affecting database performance.
oracle8i static memory management, that is, SGA parameters within a pre-configured in the database starts to carry on according to the internal distribution of these configurations, oracle10g introduction of dynamic memory management, that is, the database is running, the memory size can be modified online and automatic configuration.
oracle database memory can be divided into: the system global area, process global area, sorting area, large pool, java pool.
First, the system global area (SGA)
System global area of data by multiple users to share. When the database instance starts, the system global area memory is automatically allocated. SGA according to the different roles, but also divided into: data buffer, the log buffer, shared pool.
1. Data buffer data buffer used to store data files from the disk to read into the data for all users to share. Modify, insert the data stored in the data buffer, modify or DBWR process to complete the implementation, the data is written to the data file.
LRU: least recently used, the system according to the principle of replacing the data buffer.
Dirty: dirty data that do not write the modified data file in the data.
Prior to the 9i, the data buffer size is determined by the DB_BLOCK_BUFFER, after release, is determined by the parameters DB_CACHE_SIZE and DB_nK_CACHE_SIZE. Different table spaces can use different block size, the table space is created by adding parameters to specify the BLOCKSIZE data block size of the tablespace, if the specified is 2k, the corresponding buffer size DB_2K_CACHE_SIZE parameter, if specified is 4k, then the corresponding buffer size DB_4K_CACHE_SIZE parameter, and so on. If you do not specify BLOCKSIZE, the default value for the parameter DB_BLOCK_SIZE, the corresponding buffer size is DB_CACHE_SIZE value.
Set on the performance of the buffer is very prominent. Set a query to read the data blocks for the A, to read the data from the buffer block number C, need to read the data from the disk block number D, then A + C + D, then C / A as data buffer areas hit rate. The following statement is calculated data buffer hit rate:
SQL> SELECT A. VALUE + B. VALUE "LOGICAL_READS", C. VALUE "PHYS_READS",
ROUND (100 * ((A. VALUE + B. VALUE)-C.VALUE) / (A. VALUE + B. VALUE)) "BUFFER HIT RATIO"
FROM V $ SYSSTAT A, V $ SYSSTAT C
WHERE A. STATICSTIC # = 38 AND B. STATISTIC # = 39 AND C. STATICSTIC # = 40
Generally require less than 90% hit rate, if the hit rate is too low, it should adjust the size of the data buffer.
2. Log buffer to buffer transaction log log buffer, in due course by the LGWR process writes the log file. Log buffer size set by the parameter LOG_BUGGER.
Log in into the log buffer, if the log buffer is too small and no free space, we must wait for the LGWR to write the log buffer any original log files to free up free space. If there is enough free space, you do not need to wait for a direct write to the log buffer. Then the waiting times / total number of (wait for + non-wait) is called the failure rate of the log buffer can be calculated by the following statement:
sql> select name, gets, misses, immediate_get, immediate_misses,
decode (gets, 0,0, misses / gets * 100 ratio1,
decode (immediate_gets + immdiate_misses, 0,0, immediate_misses / (immdiate_gets + immediate_misses) * 100) ratio2
from v $ latch where name in ('redo allocation', 'redo copy');
Clearly, the failure rate is also an important factor affecting performance, it is necessary to adjust log_buffer the size of the actual situation.
3. Shared Pool Shared pool is sql, pl / sql procedure parsing, compilation, execution memory. Include: database buffer (library cache), data dictionary buffer (data dictinary cache), the user global area (user global area) in three parts.
Shared pool size set by the parameter shared_pool_size.
Query the data dictionary of the successes and failures:
sql> select sum (gets) "dictionary gets",
sum (getmisses) "dictionary cache getmisses",
from v $ rowcache
Calculate the shared pool to remember the success rate of the data dictionary:
sql> select parameter, gets, getmisses, getmisses / (gets + getmisses) * 100 "miss ratio", (1-sum (getmisses) / sum (gets) + sum (getmisses ))))* 100 "hit ratio" from v $ rowcache where gets + getmisses <> 0 group by parameter, gets, getmisses;
The failure rate calculation cache, the result should be less than 1%:
sql> select sum (pins) "total pins", sum (reloads) "total reloads",
sum (reloads) / sum (pins) * 100 libcahe from v $ librarycache;
The above analysis of the three parts of SGA, SGA to see the total size or free space, use rates, as follows:
sql> select * from v $ sga
sql> select name, sgasize/1024/1024 "allocated (m)", bytes/1024 "free space percentage (%)" from (select sum (bytes) sgasize from sys.v_ $ sgastat) s, sys.v_sgastat f where f.name = 'free mamory';
Second, the program global area
Program global area (PGA) is a single user or server that contains the data of control information memory. Is in the user process to connect to oracle database and create a session, automatically assigned by the oracle, it is non-shared area, mainly used to store variable in the programming and the array. The end of the session, PGA was released.
3, sorting area
Sorting area for the sql of the order by. oracle priority sorting area to sort, if the memory is not enough, oracle automatically sort the temporary table space.
Sort area parameter sort_area_size used to set the size.
Fourth, the large pool
Tai Chi for database backup tool-RMAN.
Large pool size determined by the parameters large_pool_size.
5, java pool
Starting from 8i, oracle increased support for java, so the system provides the java pool size set by the parameter java_pool_size.
6, oracle10g Automatic Shared Memory Management
1. What is Automatic Shared Memory Management?
In previous versions of 10g, SGA various parts, share_pool_size, db_cache_size, that require the administrator to manually set the size, when the database load is too large, if not adjust the size of these parameters will have a "can not allocate memory" error. 10 g, the Qiang Shen Shu statistics_level typecal or all She Zhiwei, Zai Shi Yong set new parameters SGA_TARGET Ou Debutaixiao SGA memory can, Wu Xu Dingyimeige parameters of size, the system will need automatically Fenpeineicun between components in Duoge size.
2. Automatic Shared Memory Management, set SGA memory parameters of the various parts of the minimum limit if the same set SGA_TARGET and memory parameters, such as:
SGA_TARGET = 300M
SHARED_POOL_SIZE = 32M
DB_CACHE_SIZE = 90M
Said the shared pool and data buffers will be allocated at least 32M and 90M memory, the remaining memory can be automatically allocated as follows:
300-32-90 = 172M
3. Manually converted to automatic memory management, management can only set the new parameters SGA_TARGET, such as:
SGA_TARGET = 1000M
Automatic memory management mode, if the database using SPFILE, then shut down the database, the system will shut down when the last database memory allocation state record. Therefore, if automatic memory management, better use of SPFILE instead of PFILE.