njliuqi
- 浏览: 10348 次
- 性别:
- 来自: 北京
-
最新评论
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.
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.
分享到：
发表评论
相关推荐
在深入理解Oracle内存管理的过程中，我们可以从以下几个方面进行探讨： 1. **内部存储与外部存储**：内部存储主要指的是Oracle实例内存结构，包括SGA（System Global Area）和PGA（Program Global Area）。SGA是...
总的来说，Oracle内存管理涉及到多个层面，包括进程私有内存（PGA）和全局共享内存（SGA）的配置、使用和优化。深入理解这些概念并掌握相应的监控和调整技巧，是确保Oracle数据库高效稳定运行的基础。通过持续的学习...
Oracle内存管理是数据库系统中的关键组成部分，它直接影响到数据库的性能和稳定性。Oracle数据库使用复杂的内存结构来存储和处理数据，这些内存结构包括共享池、数据缓冲区缓存、PGA（程序全局区）和SGA（系统全局区...
【操作系统与Oracle内存管理比较】 本文探讨了操作系统与Oracle数据库在内存管理上的异同，作者王润中通过对比分析，揭示了两者在内存利用和进程管理上的策略。 1. 操作系统的内存管理 操作系统的内存管理主要...
本文将深入探讨Oracle内存管理的核心概念，包括内存结构、内存分配策略以及如何通过配置来提升数据库效率。 Oracle内存主要分为两大部分：系统全局区(System Global Area, SGA)和程序全局区(Program Global Area, ...
### Oracle内存管理详解 在Oracle数据库管理中，内存管理是一项至关重要的任务，它直接影响到数据库的性能和稳定性。本文将围绕“Oracle内存管理”这一核心主题进行深入探讨，旨在帮助读者理解Oracle内存管理的基本...
ORACLE内存管理和后台进程.pdf
冯春培作为一位经验丰富的Oracle专家，他的这篇文章清晰而深入地介绍了Oracle内存管理的关键方面，特别是系统全局区(SGA)和进程全局区(PGA)的配置与优化。 #### 二、Oracle内存架构概览 ##### 2.1 SGA与PGA的区别 ...
理解Oracle内存管理不仅有助于提高数据库的运行效率，还能确保系统的稳定性和可靠性。通过对SGA和PGA的深入学习，DBA可以更好地诊断和解决性能问题，从而提升整体的业务运行速度。因此，这份"Oracle 内存全面分析"的...
### Oracle内存全面分析 Oracle数据库的性能在很大程度...总之，Oracle内存管理是确保数据库高性能的关键。深入了解SGA、PGA和UGA的内部机制，以及合理配置相关参数，对于避免性能瓶颈、提升数据库响应速度至关重要。
#### Oracle内存管理 Oracle数据库的内存管理对于确保数据库高效运行至关重要。内存配置不当可能导致性能下降甚至系统崩溃。在默认情况下，Oracle会根据系统的总内存自动调整其内存分配。然而，在某些情况下，可能...
ORACLE 自动内存管理和存储调整 ORACLE 自动内存管理和存储调整是 ORACLE 数据库中的一种重要配置，它可以根据系统的实际情况自动调整内存和存储的使用，以提高数据库的性能和效率。本文将详细介绍 ORACLE 自动内存...
系统全局区（SGA）是Oracle内存管理的关键部分，它包括了Shared Pool、Database Buffer Cache、Redo Log Buffer、Java Pool、Streams Pool、Large Pool等子区域。SGA的大小可以通过`ALTER SYSTEM SET SGA_MAX_SIZE=?...
Oracle内存管理的精细度和灵活性对于实现高性能数据库环境至关重要。理解SGA和PGA的结构、功能及其关键参数，可以帮助数据库管理员和开发人员更有效地配置和优化Oracle实例，从而提高系统性能，减少故障和资源浪费。...
Oracle数据库系统是企业级数据管理的重要工具，其性能优化很大程度上取决于内存的合理分配。"wxh oracle内存分配"这个主题主要...通过深入学习Oracle内存管理机制，可以解决各种性能问题，确保数据库系统的高效运行。
总的来说，Oracle内存管理是一个复杂且精细的工作，需要结合实际业务负载、硬件资源以及数据库的特性和行为进行深入分析和优化。正确的内存配置能够显著提升Oracle数据库的性能，降低CPU和I/O资源的消耗，从而提高...
Oracle数据库是全球广泛使用的...学习并掌握Oracle内存管理对于提升数据库性能至关重要，这不仅涉及理论知识，更需要实践经验。通过本教程，你可以深化对Oracle内存机制的理解，为实际工作中的问题解决提供有力支持。