Oracle-memory structure

2010-03-01  来源:本站原创  分类:Database  人气:222 

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.

New Concepts:
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.

Reproduced: http://blog.csdn.net/hxf0759/archive/2009/04/09/4060493.aspx

相关文章
  • Oracle memory structure and processes 2010-03-17

    1, an Oracle instance from the one known as the System Global Area SGA for shared memory and a number of process composition; is formed by the storage structure and processes. SGA has three essential elements: the shared pool, database cache, and log

  • oracle memory structure based 2010-12-07

    1. Storage structure Oracle database storage structure is divided into logical storage structure and the physical storage structure. Figure 3 2. Logical structure Logical Oracle database storage structure describes the internal storage structures, fr

  • Oracle memory parameter tuning technology Xiangjie 2010-04-03

    Of Hopes to organize this document, so that we right You oracle memory structure of a comprehensive understanding of the practical work and flexibility in the application, so that oracle's memory Xingneng Dadaozuiyou allocation and improving applicat

  • Oracle memory structure-SGA articles 2011-02-19

    In the " Oracle memory structure "mentioned in the article, PGA is a server process dedicated private memory area, and SGA is the shared memory area. SGA is comprised of several components: 1, the fixed SGA (Fixed SGA) 2, block buffer (Db cache)

  • Oracle architecture: the memory structure and process structure 2010-06-28

    (A) the structure of memory structures and processes Oracle database, the overall structure of the following diagram: 1: Oracle instance (Instance) In a server , each running an Oracle database, database instance are associated with the instance is a

  • Oracle System Structure - logical structure 2010-10-29

    One, Oracle Introduction to logical structure oracle logical structure, including the table space (tablespace), section (segment), the data block (data block) and the model object (schema). oracle database is logically composed of several tables, tab

  • 1.Oracle database structure 2011-04-09

    First, the basic terminology 1. Distinguish between the database instance (instance) and database (database) Database instance: ORACLE instance = process + process used by the memory (SGA), said the state of the database at the time! System Global Ar

  • oracle memory tuning parameters explain 2011-06-12

    Transfer: http://www.douban.com/group/topic/16419241/ Instance = instance structure oracle memory architecture + oracle instance startup process structure process, in fact, the value of oracle memory parameters loaded into memory and start the approp

  • ORACLE basic structure of the analysis and entry-learning methods 2011-10-11

    Oracle's system is too large, for beginners, there are some know how to feel, what want to learn what the results are not adopted, so the learning experience to share about the hope that people just started an oracle general understanding, less take

  • oracle memory management 2010-02-22

    Oracle Memory Management - from 8i, 9i, 10g to 11g Oracle to its management of SGA and PGA memory is divided into SGA = ((db_block_buffers * blocksize) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) +1 MB. PGA include Sort_area

  • The Oracle database model to a shared server mode - adjust the ORACLE memory settings - modify the maximum connection Oracle 2010-04-20

    First, the database model to a shared server mode Note: The Oracle database server the machine operation. 1 Click Start, select Programs menu from the "Database Configuration Assistant", as shown below. 2, into the "Welcome" screen, cl

  • Oracle System Structure - Structure of connection configuration 2010-10-29

    Oracle connections can be divided into two types: First, the dedicated server connection structure (Dedicated Server): In this way, for each user, the database application is run by the user process and have a dedicated server process to serve, execu

  • C + + object memory structure and the multi-state implementation 2010-11-11

    Also learn a C + + memory structures, tend to forget, to prevent future everywhere, today to buy the blog. If you are C + + or java object model that do not know, guess you do not know what I'm talking about. At least you have to know the definition

  • Java garbage collection - the memory structure 2010-11-18

    General process: minor collections occurring at intervals long enough to allow many of the objects to die between collections. It is well-tuned in the sense that the young generation is large enough (and thus the period between minor collections long

  • ORACLE - logical structure (a) (b) 2010-12-16

    ORACLE - logical structure (a) http://blog.csdn.net/xieyuooo/archive/2010/07/30/5775322.aspx ORACLE - logical structure (II) http://blog.csdn.net/xieyuooo/archive/2010/08/11/5804019.aspx

  • Excel Export Oracle table structure by 2011-02-17

    Export Oracle table structure by Excel, a template can be customized, a very powerful Excel tools, specific use, see the attached documentation.

  • oracle memory management (rpm) 2011-02-24

    Oracle's 9i/10g memory management has done a lot of gradually simplified, 11g step further, introducing a new concept of automatic memory management (Automatic Memory Management, AMM). If you really want to be lazy DBA, then, only set two parameters

  • Oracle database structure (transfer) 2011-03-24

    the oracle DB structure The overall structure of the oracle

  • Oracle System Structure - memory structure 2010-10-29

    First, the system global area (SGA) The data system global area shared by multiple users. When the database instance is started, the system global area memory is automatically allocated. Different according to the role of SGA, is divided into: data b

  • The Oracle architecture - the memory structure 2010-10-29

    First, the system global area (SGA) System global area of ​​data shared by multiple users. When the database instance starts, the system global area memory is automatically allocated. SGA according to the role of different, divided into: data buffer,