oracle 内存管理

1. SEGMENT EXTEND BLOCK
http://cotana/wiki/?action=userform&uid=1242887634.4.5192


2. memory structures
Type
Description

Database buffer cache
Memory area userd to store blocks read from data files. Data is read into the blocks by server processes and written out by DBWn asynchronously.

Log buffer
Memory containing before and after image copies fo changed data to be written to the redo logs

large pool
An optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server and Oracle XA

Shared pool
Stores parsed versions of SQL statements, PL/SQL procedures, and data dictionary information

Java pool
Used in server memory for all session-specific java code and data within the Java virtual machine(JVM)

3. Processes
3.1. Server Processes
3.2. Background processes
Type
Description

Database write(DBWn)
Write dirty buffers from the data buffer cache to the data file. This activity is asynchronous.

Log Write(LGWR)
Write data from the redo log buffer to the redo log files.

System monitor(SMON)
Performs automatic instance recovery.Recovers space in temproary segments when they are no longer in use.Merges contiguous areas of free space depending on parameters that are set.

Process monitor(PMON)
Cleans up the connection/server process dedicate to an abnormally terminated user process. Performs rollback and releases the resources held by the failed process.

Checkpoint(CKPT)
Synchronizes the headers of the data files and control files with current redo log and checkpoint numbers.

Archiver(ARCn)
A process that automatically copies redo logs that have been marked for archiving.


3.3. User Processes
4. SGA（System Global Area）
系统全局区，Oracle中最重要的内存区。

Specifies the total size of all SGA components. If SGA_TARGET is specified,
then the buffer cache (DB_CACHE_SIZE),
Java pool(JAVA_POOL_SIZE),
large pool (LARGE_POOL_SIZE),
and shared pool (SHARED_POOL_SIZE) memory pools are automatically sized.
4.1. shared pool
共享池主要由library cache 和 dictioinary cache 组成。
library cache 主要存储可执行的SQL和PL/SQL代码(解析or编译)
The main components of the shared pool are the library cache and the dictionary
cache. The library cache stores the executable (parsed or compiled) form of recently
referenced SQL and PL/SQL code. The dictionary cache stores data referenced from
the data dictionary. Many of the caches in the shared pool automatically increase or
decrease in size, as needed, including the library cache and the dictionary cache. Old
entries are aged out of these caches to accommodate new entries when the shared pool
does not have free space.

Dictionary Cache Concepts

Information stored in the data dictionary cache includes usernames, segment
information, profile data, tablespace information, and sequence numbers. The
dictionary cache also stores descriptive information, or metadata, about schema
objects. Oracle uses this metadata when parsing SQL cursors or during the
compilation of PL/SQL programs.
Library Cache Concepts

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java
classes. This section focuses on tuning as it relates to cursors, PL/SQL programs, and
Java classes. These are collectively referred to as application code.
4.2. LARGE POOL
The large pool is used to allocate sequential I/O buffers from shared memory.For I/O slaves and Oracle backup and restore, the RDBMS allocates buffers that are a few hundred kilobytes in size.

Recovery Manager(RMAN)uses the large pool for backup and restore when you set the '''DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O.
--Oracle backup and restore operations
--I/O server processes
--Session memory for the shared server
4.3. DB BUFFER CACHE
DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).

The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value). A user-specified value larger than this is rounded up to the nearest granule size. A value of zero is illegal because it is needed for the DEFAULT memory pool of the primary block size, which is the block size for the SYSTEM tablespace.

SQL>show parameter db_cache_advice;
SQL>SELECT size_for_estimate AS "size", buffers_for_estimate AS "buffer",
2>estd_physical_read_factor AS "factor", estd_physical_reads AS "read"
3>FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT'
4>AND block_size = (SELECT value FROM V$PARAMETER
5>WHERE name = 'db_block_size') AND advice_status = 'ON';

size buffer factor read
---------- ---------- ---------- ----------
16 1996 1.4807 10386
32 3992 1.1359 7967
48 5988 1.0223 7170
64 7984 1 7014
80 9980 1 7014
96 11976 1 7014
112 13972 1 7014
128 15968 1 7014
144 17964 1 7014
160 19960 1 7014
176 21956 1 7014

size buffer factor read
---------- ---------- ---------- ----------
188 23453 1 7014
192 23952 1 7014
208 25948 1 7014
224 27944 1 7014
240 29940 1 7014
256 31936 1 7014
272 33932 1 7014
288 35928 1 7014
304 37924 1 7014
320 39920 1 7014

21 rows selected.

SQL> SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
2> 1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
3> FROM V$BUFFER_POOL_STATISTICS;
NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS Hit Ratio
--------------- -------------- ------------- -------------------------
DEFAULT 7649 11045 103311 .933112386

4.4. REDO LOG BUFFER
用户修改数据时，会在日志缓冲区(log buffer) 构建一个redo entry.

LGWR进程将log buffer 里面的数据写入到联机日志(redo log)中

经过一段时间再通过DBWn进程将数据写入磁盘。

CKPT进程触发DBWn进程，将联机日志(redo log)中的数据写入到磁盘中。

DBWn 触发的条件

1.checkpoint occurs

2.dirty buffers reach threshold

3.there are no free buffers

4.timedout occurs

5.RAC ping request is made

6.tablespace offline

7.tablespace read only

8.table drop or truncate

9.tablespace begin backup

LGWR写入的条件如下

1.Whenever a commit is issued by any transaction --执行commit

2.When the redo log buffer is one-third full or contains 1MB of buffered data --redo log buffer 有三分之一内容或有1M的数据

3.Every three seconds --每三秒执行一次

4.When a DBWn process writes modified buffers to disk, if necessary --当DBWn进程写入修改数据到磁盘时

从上面可以看出 DBWn LGWR CKPT他们的关系, CKPT会触发DBWn,DBWn 会触发LGWR ,当发生redo log switch时会触发CKPT.

SQL>show parameter log_buffer;

log_buffer 的值设置：MAX(0.5M, (128K * number of cpus))

On most systems, sizing the log buffer larger than 1M does not provide any
performance benefit. Increasing the log buffer size does not have any negative
implications on performance or recoverability. It merely uses extra memory.

大多数的系统中，log buffer size 的大小超过1M, 并不能提供很好的优化。

5. PGA (Process Global Area)
V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC. Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.


SQL> select * from v$pgastat;

NAME VALUE UNIT
------------------------------ ---------- ------------
aggregate PGA target parameter 94371840 bytes --当前PGA大小
aggregate PGA auto target 69488640 bytes --当前可用于自动分配了的PGA大小，不应该比PGA_AGGREGATE_TARGET小
global memory bound 18874368 bytes
total PGA inuse 17332224 bytes
total PGA allocated 41093120 bytes
maximum PGA allocated 53965824 bytes
total freeable PGA memory 5898240 bytes
process count 19
max processes count 29
PGA memory freed back to OS 12845056 bytes
total PGA used for auto workar 105472 bytes

NAME VALUE UNIT
------------------------------ ---------- ------------
eas

maximum PGA used for auto work 877568 bytes
areas

total PGA used for manual work 0 bytes
areas

maximum PGA used for manual wo 0 bytes
rkareas


NAME VALUE UNIT
------------------------------ ---------- ------------
over allocation count 0
bytes processed 23966720 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 680

19 rows selected.


SQL>SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb,
2>estd_pga_cache_hit_percentage as hit_ratio,
3>estd_overalloc_count
4>FROM V$pga_target_advice
5>ORDER BY target_mb

TARGET_MB HIT_RATIO ESTD_OVERALLOC_COUNT
---------- ---------- --------------------
11 100 1
23 100 1
45 100 0
68 100 0
90 100 0
108 100 0
126 100 0
144 100 0
162 100 0
180 100 0
270 100 0

TARGET_MB HIT_RATIO ESTD_OVERALLOC_COUNT
---------- ---------- --------------------
360 100 0
540 100 0
720 100 0

14 rows selected.