Memory Architecture-PGA

1、Contents of the PGA

1.1Private SQL Area

A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

不要把PGA中的private SQL Area和SGA中的shared SQL area混淆，shared SQL area是用来存储执行计划的。同一个或者不同的session的private SQL area可以指向SGA中同一个执行计划。

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.

A private SQL area is divided into the following areas:

a、The run-time area

The run-time area包含查询执行状态信息。Oracle数据库在执行请求的第一步创建the run-time area，对于DML语句，当SQL语句close的时候the run-time area被释放。

b、The persistent area

The persistent area包含bind variable值。当语句执行的时候A bind variable被运用到SQL statement。The persistent area is freed only when the cursor is closed.

---------------

客户端程序负责管理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.

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

如果要处理的数据比work area大，数据库会将数据分成smaller pieces。数据库在内存空间处理some data pieces，而把剩下的data pieces存储在temporary disk稍后进行处理。

当automatic PGA memory management is enabled，数据库可以自动调整work area sizes。也可以人工控制和调整work area的大小。

2、PGA Usage in Dedicated and Shared Server Modes