11g
[root@roidb1 ~]# su - oracle
<roidb1:orcl1:/home/oracle>$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 13 21:40:51 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> 12c
[root@rac01 ~]# su - oracle
Last login: Fri Dec 8 16:09:27 CST 2017 on pts/0
<rac01:orcl1:/home/oracle>$sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 21:33:48 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SYS@orcl1> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 390M
SYS@orcl1> --官方解释
Property Description
Parameter type
Big integer
Syntax
PGA_AGGREGATE_LIMIT = integer [K | M | G]
Default value
By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.
Modifiable
ALTER SYSTEM
Modifiable in a PDB
No
Range of values
Oracle recommends that you do not set PGA_AGGREGATE_LIMIT below its default value. PGA_AGGREGATE_LIMIT cannot be set below its default value except in a text initialization parameter file (pfile) or binary server parameter file (spfile). If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
Basic
No
PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.
There is no difference in behavior between PGA_AGGREGATE_LIMIT being explicitly set or being set to the default.SYS@orcl1> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 390M
SYS@orcl1> alter system set pga_aggregate_limit=3G --动态修改
System altered.
SYS@orcl1> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 3G
pga_aggregate_target big integer 390M
SYS@orcl1> alter system set pga_aggregate_limit=350m;
alter system set pga_aggregate_limit=350m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
--大小不能小于2G
SYS@orcl1> alter system set pga_aggregate_limit=2048m;
System altered.
SYS@orcl1> alter system set pga_aggregate_limit=2047m;
alter system set pga_aggregate_limit=2047m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00093: pga_aggregate_limit must be between 2048M and 100000G
SYS@orcl1> 在 Oracle 12c R1 之前，没有选项可以用来限制和控制 PGA 的大小。虽然你设置某个大小为 PGA_AGGREGATE_TARGET 的初始参数，Oracle 会根据工作负载和需求来动态地增大或减小 PGA 的大小。而在 12c 中，你可以通过开启自动 PGA 管理来对 PGA 设定硬性限制，这需要对 PGA_AGGREGATE_LIMIT 参数进行设置。因此，你现在可以通过设置新的参数来对 PGA 设定硬性限制以避免过度使用 PGA。
