Oracle Memory Architecture - Oracle 体系结构篇 7

前言：UGA&PGA&Software Code Areas

Oracle Memory Architecture - Oracle 体系结构篇 6  http://blog.csdn.net/u010993297/article/details/9700319

 

1. User Global Area

               图 1  User global area

参考图1，来理解 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.

UGA是一个会话内存，这个内存分配给会话变量，比如：登陆信息，以及其他一些数据库的会话信息，从本质上讲，UGA存储就是会话的状态。

If a session loads a PL/SQL package into memory, then the UGA contains the package state, which is the set of values stored in all thepackage variables at a specific time. The package state changes when a package subprogram changes the variables. By default, the package variables are unique to and persist for the life of the session.

如果会话加载了一个PL/SQL程序包到内存，那么UGA就包含了这个程序包的状态。在特定的时间，UGA存储着所有程序包变量的值信息。当程序包子程序改变了变量的时候，程序包的状态也随着改变，在默认情况下，这个程序包的变量值是唯一的，并且值值保留在此会话之中。

The OLAP page poolis also stored in the UGA. This pool manages OLAP data pages, which are equivalent to data blocks. The page pool is allocated at the start of an OLAP session and released at the end of the session. An OLAP session opens automatically whenever a user queries a dimensional object such as a cube.

OLAP页面池也存储在UGA里面。

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 adedicated server connection, the UGA is stored in the PGA.

UGA的生命周期是基于用户会话的，会话断了，UGA就释放了。

在共享服务模式下，UGA分离到SGA，挂在SGA下。

在专有服务模式下，UGA挂在PGA下。

2. Program Global Area

 

                                图 2 Instance  PGA

 

图2shows an instance PGA (collection of all PGAs) for an instance that is not configured for shared servers. You can use an initialization parameter to set a target maximum size of the instance PGA. Individual PGAs can grow as needed up to this target size.

--PGA不能被共享服务器配置

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA.

PGA 是内存指定的操作进程或线程，是不能被系统上其他进程或线程共享的。因为PGA是一个独立的进程，它不在SGA中分配。

The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA.

 

An analogy for a PGA is atemporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

打个比喻，顾客小Q发出请求，要买一本书，服务台（监听器）审核合法后，分配一个售后人员来为他服务。这个售后人员就是一个服务器进程，小Q和售后人员建立的就是session。基于利益的目的，其他的售后人员是不允许知道服务于小Q的这个售后人员的信息及小Q这次买书的信息。所以这个服务器进程对应的信息是私有的，小Q的用户信息也是私有的，服务于小Q的售后人员知道的信息叫PGA。也就是进程全局区。当然售后人员也知道小Q获取的信息。

售后人员是服务器进程，小Q是用户进程，用户进程使用的内存叫UGA。

 

--小结：

UGA的生命周期是基于用户会话的。

PGA的生命周期是基于服务器进程的。

SGA的生命周期是基于实例的。

2.1 Contents of  the PGA

--PGA 的组成，见下图：

                                                                   图 3 PGA contents

2.1.1 Private SQL Area 

 

A private SQL areaholds information about a parsedSQL statementand other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to storebind variable values,query execution state information, andquery execution work areas.  –PGA 保存着被解析的sql语句，其他具体的会话处理信息

Do not confuse a privateSQL area, which is in the UGA, with thesharedSQL area, which stores execution plans in theSGA. 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 employees in 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. –不要混淆另一处的PGA（in the UGA）

A cursor is a name or handle to a specific private SQL area. As shown in 图 4, 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.

 

                                             图 4  Cursor

 

--PGA 的被分成下面两个区域

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 areatracks the number of rows retrieved so far in a full table scan.

Oracle Database creates the run-time areaas the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.

>>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.

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.

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.

2.1.2 SQL Work  Area 

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 ahash area to build a hash table from its left input, whereas a bitmap mearge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes. –频繁访问内存操作者

 

见下面的表连接的查询计划 

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 |

---therun-time area tracks the progress of thefull table scans. The session performs ahash join in thehash area to match rows from the two tables. Theorder by sort occurs in thesort 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 totemporary disk storage for processing later.

The database automatically tuneswork area sizes whenautomatic PGA memory management is enabled. You can also manually control and tune the size of a work area.  –自动手动管理PGA memory menagement

--三种情况下work areas size的描述

Generally,larger work areascan significantly improveperformance of an operator at the cost of higher memory consumption.

Optimally, the size of a work area issufficient to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. Ifnot, 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 toosmall compared to input data size, then the database must perform multiple passes over the data pieces, dramatically increasing response time.

2.2 PGA Usage in Dedicated and Shared Server Modes

PGA memory allocation depends on whether the database usesdedicated orshared server connections.

如下表：

Memory Area	Dedicated Server	Shared Server
Nature of session memory	Private	Shared
Location of the persistent area	PGA	SGA
Location of the run-time area for DML/DDL statements	PGA	PGA

表1 differences in memory allocation

3 Software Code Areas

--软件代码区是被内存分配的一部分用来存储在运行或能运行的代码的区域。

Software code areas are portions of memory that store code that is being run or can be run.Oracle Database code is stored in a software area that is typically more exclusive and protected than the location of user programs.

--软件代码区的大小通常是静态的，只有软件升级或是重新安装时，才能被改变。

Software areas are usuallystatic in size, changing only when software is updated or reinstalled. The required size of these areas varies by operating system.

--软件代码区是只读的，能被安装共享或非共享模式。

Software areas are read-only and can be installedshared or nonshared. Some database tools and utilities, such asOracle Forms and SQL*Plus, can be installedshared, but some cannot. When possible, database code is shared so that all users can access it without having multiple copies in memory, resulting in reduced main memory and overall improvement in performance. Multipleinstances of a database can use thesame database code area with different databases if running on the same computer.

 

At this point,  the oracle memory architecture is end.