Oracle教程 2023-04-05

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

本站中文解释

PGA_AGGREGATE_LIMIT参数用来设置数据库中的总的PGA内存配置，它的范围是大小在几KB到几GB之间的整数，这个参数在11g版本以后出现。

这个参数的默认值根据操作系统不同而不同，Windows操作系统默认值为2GB，Linux等操作系统默认值为4GB。

设置PGA_AGGREGATE_LIMIT参数的步骤如下：

1.确保数据库已经关闭，打开控制文件，在控制文件中添加参数PGA_AGGREGATE_LIMIT，如pga_aggregate_limit=4G；

2.打开parameter文件，添加该参数PGA_AGGREGATE_LIMIT，确保要决定设置一个合理的值，值必须大于100KB。

3.设置参数文件，重新启动数据库；

4.使用以下SQL查看当前设置的值，查看是否设置正确

select name,value from v$parameter where name=’pga_aggregate_limit’;

5.如果设置正确，提交参数文件保存，关闭数据库。

官方英文解释

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.

Property	Description
Parameter type	Big integer
Syntax	`PGA_AGGREGATE_LIMIT =` `integer` `[K \| M \| G]`
Default value	If `MEMORY_TARGET` is set, then `PGA_AGGREGATE_LIMIT` defaults to the `MEMORY_MAX_TARGET` value. If `MEMORY_TARGET` is not set, then `PGA_AGGREGATE_LIMIT` defaults to 200% of `PGA_AGGREGATE_TARGET`. If `MEMORY_TARGET` is not set, and `PGA_AGGREGATE_TARGET` is explicitly set to 0, then the value of `PGA_AGGREGATE_LIMIT` is set to 90% of the physical memory size minus the total SGA size. In all cases, the default `PGA_AGGREGATE_LIMIT` is at least 2GB and at least 3MB times the `PROCESSES` parameter (and at least 5MB times the `PROCESSES` parameter for an Oracle RAC instance). 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	Do not attempt to set `PGA_AGGREGATE_LIMIT` below its default value, even in a parameter file (pfile), or instance startup will fail. However, `PGA_AGGREGATE_LIMIT` can be set to 0 either in a parameter file or dynamically after startup. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
Basic	No

Actions Taken When PGA_AGGREGATE_LIMIT is Exceeded

Parallel queries will be treated as a unit. First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated.

SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

Note:

This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it specifies the maximum 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_LIMIT initialization parameter in a PDB to a value that meets these requirements:
- Less than or equal to the PGA_AGGREGATE_LIMIT value set at the CDB level
- Greater than or equal to twice the value of PGA_AGGREGATE_TARGET set in the PDB
When you set PGA_AGGREGATE_LIMIT 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_LIMIT value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

本站中文解释

官方英文解释

相关推荐