Oracle教程

Oracle 参数 PGA_AGGREGATE_TARGET 官方解释，作用，如何配置最优化建议

本站中文解释

PGA_AGGREGATE_TARGET参数指定服务器可分配给每个Oracle会话的最大共享池（PGA）内存大小。PGA共享池用于存储用户会话的活动状态数据，特别是排序和聚合缓冲区，以及其他大量操作，如游标，快速批处理等。

PGA_AGGREGATE_TARGET参数可以通过设置ALTER SYSTEM或ALTER SESSION命令设置。有关设置它的格式，应用如下：

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = Size [K | M | G] SCOPE = {MEMORY | SPFILE};

在内存中设置参数后，它将从SPFILE加载到SGA中。另外，默认情况下，此参数为0，此时系统将计算一个PGA空间，但是对于吞吐量的大量增长，它可能会失去效率。为确保更快的响应，建议调整参数。

官方英文解释

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Property Description

Parameter type

Big integer

Syntax

PGA_AGGREGATE_TARGET = integer [K | M | G]

Default value

10 MB or 20% of the size of the SGA, whichever is greater

For a PDB, the default value is the same as the CDB’s default value.

Modifiable

ALTER SYSTEM

Modifiable in a PDB

Yes

Range of values

Minimum: 10 MB

Maximum: 4096 GB – 1

Basic

Yes

To set a hard limit for aggregate PGA memory, use the PGA_AGGREGATE_LIMIT parameter.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. With this setting, SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. With this setting, SQL working areas are sized using the *_AREA_SIZE parameters.

Oracle attempts to keep the amount of private memory below the target specified by this parameter by adapting the size of the working areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to working areas. Consequently, more memory-intensive operations are able to run fully in memory and fewer will work their way over to disk.

If Automatic Memory Management is enabled (MEMORY_TARGET is set to a positive value) and PGA_AGGREGATE_TARGET is also set to a positive value, the PGA_AGGREGATE_TARGET value acts as the minimum value for the size of the instance PGA.

Note:

This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it specifies the target aggregate PGA size for the PDB.

To be able to use Resource Manager in a CDB to control the amount of memory each PDB can use:

  • The NONCDB_COMPATIBLE initialization parameter must be set to FALSE at the CDB level (in the root of the CDB).

  • The MEMORY_TARGET initialization parameter must not be set at the CDB level.

  • You must set the PGA_AGGREGATE_TARGET initialization parameter in a PDB to a value that meets these requirements:

    • Less than or equal to the PGA_AGGREGATE_TARGET value set at the CDB level

    • Less than or equal to 50% of the PGA_AGGREGATE_LIMIT initialization parameter value set at the CDB level

    • Less than or equal to 50% of the PGA_AGGREGATE_LIMIT value set in the PDB

    When you set PGA_AGGREGATE_TARGET in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the PGA_AGGREGATE_TARGET value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

See Also:

  • “WORKAREA_SIZE_POLICY”

  • Oracle Database
    Performance Tuning Guide     for more information about configuring memory

  • “PGA_AGGREGATE_LIMIT”


数据运维技术 » Oracle 参数 PGA_AGGREGATE_TARGET 官方解释，作用，如何配置最优化建议
分享到：

相关推荐