pga_aggregate_target,pga_aggregate_limit

os: centos 7.6
db: oracle 19.3

测试人员提示oracle报错信息:

然后调整 pga 的内存参数,却报错如下.

SQL> alter system set pga_aggregate_limit=5g;

System altered.

SQL> alter system set pga_aggregate_target=3g;
alter system set pga_aggregate_target=3g
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00856: PGA_AGGREGATE_TARGET cannot be set higher 50 percent of PGA_AGGREGATE_LIMIT.

$ oerr ora 856
00856, 00000, "PGA_AGGREGATE_TARGET cannot be set higher 50 percent of PGA_AGGREGATE_LIMIT."
// *Cause:  PGA_AGGREGATE_TARGET value was greater than 50 percent the current value of PGA_AGGREGATE_LIMIT.
// *Action: Increase PGA_AGGREGATE_LIMIT or use a lower PGA_AGGREGATE_TARGET.

$ sqlplus / as sysdba;

SQL> alter system set pga_aggregate_limit=6g;
alter system set pga_aggregate_target=3g;

SQL> show parameter pga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 6G
pga_aggregate_target		     big integer 3G

SQL> show parameter workarea;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy		     string	 AUTO

报错提示 pga_aggregate_target 不能超过 pga_aggregate_limit 的 50%.

pga_aggregate_target 的 oracle 官网有如下说明

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.

参考:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PGA_AGGREGATE_TARGET.html#GUID-DEBBD3F7-9F6D-4AC8-952C-0E0B2E62312D
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3

最后

以上就是务实棉花糖为你收集整理的pga_aggregate_target,pga_aggregate_limit的全部内容，希望文章能够帮你解决pga_aggregate_target,pga_aggregate_limit所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。