oracle的内存管理真是很乱，之前反复看了几次资料，以为理解了几种内存管理方式，一到用起来的时候感觉又是大脑一片空白，很崩溃。以下好好总结一下我对oracle内存管理的理解：
1、内存管理方式分类
（注意这个问题还是比较重要的，在一些负载较高、还有一些联机批量业务混合的数据库中，大多需要禁用自动内存管理，使用assm或者msmm）
典型管理方式（typical）：选择此种方式，默认选择automatic memorymanagement。从参数上来看，即是确定了memory_target，memory_max_target。
-
Automatic memory management
You specify the target size for instance memory. The database instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.
定制管理方式（custom）：选择此种方式有两种选择，一是选择automatic shared memory management，二是选择manual shared momory management。
-
Automatic shared memory management
This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.
-
Manual memory management
Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.
根据dbca安装安装过程中的提供的图
- 选择automatic shared memory management（assm）如下，指定sga、pga即可（也就是说如果我们要启用assm，设定sga、pga大小即可）
- 选择manual shared momory management（asmm），需要指定share pool、buffer cache、java pool、large pool、pga大小。（也就是说，我们要启用asmm，需要启用以上组件。）
-
2、关于内存管理这里，还有几个概念如Memory Advisor、SGA Advisor、Shared Pool Advisor、Buffer Cache Advisor、PGA Advisor。（相对来说这个很少使用，至少知道怎么回事就行了）
- If Automatic Memory Management is enabled, then only the Memory Advisor is available. This advisor provides advice for the total memory target for the instance.（如果启用了amm，那么只有Memory Advisor是运行的。）
- If Automatic Shared Memory Management is enabled, then the SGA Advisor and PGA Advisor are available.（如果启用了asmm，那么可以使用sga advisor、pga advisor。）
- If Manual Shared Memory Management is enabled, then the Shared Pool Advisor, Buffer Cache Advisor, and PGA Advisor are available.（如果启用了msmm，那么可以使用Shared Pool Advisor, Buffer Cache Advisor, and PGA Advisor）
3、重点说一下Oracle内存中重要的几个组件：
UGA:
-
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state. The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.
PGA:
-
The PGA is subdivided into different areas, each with a different purpose. Figure 14-4 shows the possible contents of the PGA for a dedicated server session. Not all of the PGA areas will exist in every case.
Private SQL Area
A private SQL area holds information about a parsed SQL statement and other session-specific information for processing.
Do not confuse a private SQL area, which is in the UGA（private SQL area在UGA pool中）, with the shared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions of
SELECT * FROM employeesin one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.A cursor is a name or handle to a specific private SQL area. As shown in Figure 14-5, you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
{私有SQL区域保存有关已解析的SQL语句的信息和其他特定于会话的信息，以便进行处理。不要将UGA中的私有SQL区域与SGA中存储执行计划的共享SQL区域混淆。相同或不同会话中的多个私有SQL区域可以指向SGA中的单个执行计划。例如，一个会话中有20个select*的执行来自员工，而在另一个会话中有10个相同查询的执行可以共享同一计划。每个执行的私有SQL区域不共享，可能包含不同的值和数据。cursor是private SQL area的名称或句柄。如图14-5所示，可以将光标视为客户端的指针和服务器端的状态。由于游标与私有SQL区域密切相关，因此术语有时可以互换使用。}
A private SQL area is divided into the following areas:
-
The run-time area
This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.（此区域包含查询执行状态信息。例如，运行时区域跟踪到目前为止在全表扫描中检索到的行数。Oracle数据库创建运行时区域作为执行请求的第一步。对于DML语句，当SQL语句关闭时，将释放运行时区域。）
-
The persistent area
This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.（此区域包含绑定变量值。在执行SQL语句时，会在运行时向该语句提供绑定变量值。只有当光标关闭时，持久区域才被释放。）
The client process is responsible for managing private SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameter
OPEN_CURSORS.（客户端进程负责管理私有SQL区域。私有SQL区域的分配和释放很大程度上取决于应用程序，尽管客户端进程可以分配的私有SQL区域的数量受到初始化参数open-cursors的限制。）Although most users rely on the automatic cursor handling of database utilities, the Oracle Database programmatic interfaces offer developers more control over cursors. In general, applications should close all open cursors that will not be used again to free the persistent area and to minimize the memory required for application users.（尽管大多数用户依赖于数据库实用程序的自动光标处理，但Oracle数据库编程接口为开发人员提供了对光标的更多控制。一般来说，应用程序应该关闭所有不会再次使用的打开的游标，以释放持久区域并最小化应用程序用户所需的内存。）
SQL Work Areas
A work area is a private allocation of PGA memory used for memory-intensive operations. For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.（工作区是用于内存密集型操作的专用PGA内存分配。例如，排序运算符使用排序区域对一组行进行排序。类似地，哈希联接运算符使用哈希区域从其左侧输入构建哈希表，而位图合并则使用位图合并区域合并从多个位图索引扫描中检索到的数据。）
Example 14-1 shows a join of
employeesanddepartmentswith its query plan.Example 14-1 Query Plan for Table Join
SQL> SELECT * 2 FROM employees e JOIN departments d 3 ON e.department_id=d.department_id 4 ORDER BY last_name; . . . ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 106 | 9328 | 7 (29)| 00:00:01 | | 1 | SORT ORDER BY | | 106 | 9328 | 7 (29)| 00:00:01 | |* 2 | HASH JOIN | | 106 | 9328 | 6 (17)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 540 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------
In Example 14-1, the run-time area tracks the progress of the full table scans. The session performs a hash join in the hash area to match rows from the two tables. The
ORDER BYsort occurs in the sort area.If the amount of data to be processed by the operators does not fit into a work area, then Oracle Database divides the input data into smaller pieces. In this way, the database processes some data pieces in memory while writing the rest to temporary disk storage for processing later.
The database automatically tunes work area sizes when automatic PGA memory management is enabled. You can also manually control and tune the size of a work area. Generally, larger work areas can significantly improve performance of an operator at the cost of higher memory consumption. Optimally, the size of a work area is sufficient to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time increases because part of the input data must be cached on disk. In the extreme case, if the size of a work area is too small compared to input data size, then the database must perform multiple passes over the data pieces, dramatically increasing response time.
（运行时区域跟踪整个表扫描的进度。会话在哈希区域执行哈希联接，以匹配两个表中的行。排序顺序出现在排序区域中。如果操作员要处理的数据量不适合工作区，那么Oracle数据库会将输入数据分成更小的部分。通过这种方式，数据库处理内存中的一些数据块，同时将其余的数据写入临时磁盘存储器，以便以后处理。启用自动PGA内存管理时，数据库自动调整工作区大小。您还可以手动控制和调整工作区的大小。一般来说，较大的工作区域可以显著提高操作员的性能，但代价是更高的内存消耗。最佳情况下，工作区的大小足以容纳输入数据和由其关联的SQL运算符分配的辅助内存结构。否则，响应时间会增加，因为部分输入数据必须缓存在磁盘上。在极端情况下，如果与输入数据大小相比，工作区的大小太小，那么数据库必须对数据块执行多次传递，从而显著增加响应时间。）
PGA Usage in Dedicated and Shared Server Modes
PGA memory allocation depends on whether the database uses dedicated or shared server connections. Table 14-1 shows the differences.
-
[ocm:oracle]:/home/oracle>oerr ora 845 00845, 00000, "MEMORY_TARGET not supported on this system" // *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux. // *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system. [ocm:oracle]:/home/oracle>df -TH df: "/root/.gvfs": 权限不够 Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/vg_ocm-lv_root ext4 30G 18G 10G 65% / tmpfs tmpfs 977M 283k 977M 1% /dev/shm /dev/sda1 ext4 199M 43M 146M 23% /boot [ocm:oracle]:/home/oracle>df -Th df: "/root/.gvfs": 权限不够 Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/vg_ocm-lv_root ext4 28G 17G 9.3G 65% / tmpfs tmpfs 932M 276K 932M 1% /dev/shm /dev/sda1 ext4 190M 41M 139M 23% /boot
1806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
