OCP-043 automatic PGA

最新推荐文章于 2013-07-08 22:57:06 发布

xiadingling

最新推荐文章于 2013-07-08 22:57:06 发布

阅读量422

点赞数

分类专栏： OCP 文章标签： initialization database oracle performance input parameters

本文链接：https://blog.csdn.net/xiadingling/article/details/7955564

版权

OCP 专栏收录该内容

72 篇文章 0 订阅

订阅专栏

81. You are working in a dedicated server environment. Your database is running in the automatic
Program Global Area (PGA) memory management mode. Which two statements are correct in this
scenario? (Choose two.)
A.The WORK_AREASIZE_POLICY initialization parameter cannot be set to AUTO.
B.The Oracle database automatically controls the amount of PGA memory allotted to SQL work areas.
C.Setting the value of the SGA_TARGET initialization parameter to 0 will disable the automatic PGA
memory management.
D.The SORT_AREA_SIZE parameter is ignored by all the sessions running in the automatic PGA
memory management mode.
Answer: BD

7.5 PGA Memory Management

The Program Global Area (PGA) is a private memory region containing data and control information for a server process. Access to it is exclusive to that server process and is read and written only by the Oracle code acting on behalf of it. An example of such information is the runtime area of a cursor. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.

Note:

Part of the runtime area can be located in the SGA when using shared servers.

For complex queries (for example, decision support queries), abig portion of the runtime area is dedicated to work areas allocated by memory intensive operators, such as the following:

Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions
Hash-join
Bitmap merge
Bitmap create
Write buffers used by bulk load operations

A sort operator uses a work area (the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.

The size of a work area can be controlled and tuned. Generally, bigger work areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Ideally, the size of a work area is big enough that it can accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. This is known as the optimal size of a work area. When the size of the work area is smaller than optimal, the response time increases, because an extra pass is performed over part of the input data. This is known as the one-pass size of the work area. Under the one-pass threshold, when the size of a work area is far too small compared to the input data size, multiple passes over the input data are needed. This could dramatically increase the response time of the operator. This is known as the multi-pass size of the work area. For example, a serial sort operation that needs to sort 10GB of data needs a little more than 10GB to run optimal and at least 40MB to run one-pass. If this sort gets less that 40MB, then it must perform several passes over the input data.

The goal is to have most work areas running with an optimal size (for example, more than 90% or even 100% for pure OLTP systems), while a smaller fraction of them are running with a one-pass size (for example, less than 10%). Multi-pass execution should be avoided. Even for DSS systems running large sorts and hash-joins, the memory requirement for the one-pass executions is relatively small. A system configured with a reasonable amount of PGA memory should not need to perform multiple passes over the input data.

Automatic PGA memory management simplifies and improves the way PGA memory is allocated. By default, PGA memory management is enabled. In this mode, Oracle dynamically adjusts the size of the portion of the PGA memory dedicated to work areas, based on 20% of the SGA memory size. The minimum value is 10MB.

Note:

For backward compatibility, automatic PGA memory management can be disabled by setting the value of the PGA_AGGREGATE_TARGET initialization parameter to 0. When automatic PGA memory management is disabled, the maximum size of a work area can be sized with the associated _AREA_SIZE parameter, such as the SORT_AREA_SIZE initialization parameter.

WORKAREA_SIZE_POLICY

Property	Description
Parameter type	String
Syntax	`WORKAREA_SIZE_POLICY = { AUTO \| MANUAL }`
Default value	`AUTO`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.

Values:

AUTO

Work areas used by memory-intensive operators are sized automatically, based on the PGA memory used by the system, the target PGA memory set inPGA_AGGREGATE_TARGET, and the requirement of each individual operator.
MANUAL

The sizing of work areas is manual and based on the values of the *_AREA_SIZE parameter corresponding to the operation (for example, a sort usesSORT_AREA_SIZE). Specifying MANUAL may result in sub-optimal performance and poor PGA memory utilization.

PGA Memory Management for Dedicated Mode

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.

Note:

In earlier releases, the database administrator controlled the maximum size of SQL work areas by setting the following parameters: SORT_AREA_SIZE,HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE . Setting these parameters is difficult, because the maximum work area size is ideally selected from the data input size and the total number of work areas active in the system. These two factors vary a lot from one work area to another and from one time to another. Thus, the various *_AREA_SIZE parameters are hard to tune under the best of circumstances.

With PGA_AGGREGATE_TARGET, sizing of work areas for all dedicated sessions is automatic and all *_AREA_SIZE parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirement.

Note:

The initialization parameter WORKAREA_SIZE_POLICY is a session- and system-level parameter that can take only two values: MANUAL or AUTO. The default is AUTO. The database administrator can set PGA_AGGREGATE_TARGET, and then switch back and forth from auto to manual memory management mode.

There are fixed views and columns that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET is set.