PGA_AGGREGATE_LIMIT of 12c new features
11g
[root@roidb1] # su-oracle$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 13 21:40:51 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL > show parameter pga NAME TYPE VALUE-- pga_aggregate_target big integer 0SQL >
12c
[root@rac01] # su-oracleLast login: Fri Dec 8 16:09:27 CST 2017 on pts/0$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 13 21:33:48 2017Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Application Testing optionsSYS@orcl1 > show parameter pga NAME TYPE VALUE-- pga_aggregate_limit big integer 2Gpgawatcher _ Target big integer 390MSYS@orcl1 >-- official interpretation Property DescriptionParameter typeBig integerSyntaxPGA_AGGREGATE_LIMIT = integer [K | M | G] Default valueBy 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.ModifiableALTER SYSTEMModifiable in a PDBNoRange of valuesOracle recommends that you do not set PGA_AGGREGATE_LIMIT below its default value. PGA_AGGREGATE_LIMIT cannot be set below its default value except ina 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.BasicNoPGA_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 2Gpgawatcher _ Target big integer 390MSYS@orcl1 > alter system set pga_aggregate_limit=3G-- dynamically modify System altered.SYS@orcl1 > show parameter pga NAME TYPE VALUE-- pga_aggregate_limit big integer 3Gpgawatcher _ Target big integer 390MSYS@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 invalidORA-00093: pga_aggregate_limit must be between 2048M and 100000G and 100000GSYS@orcl1-size cannot be less than 2GSYS@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 invalidORA-00093: pga_aggregate_limit must be between 2048m and 100000GSYS@orcl1 >
Prior to Oracle 12c R1, there was no option to limit and control the size of PGA. Although you set a size to the initial parameter of PGA_AGGREGATE_TARGET, Oracle will dynamically increase or decrease the size of PGA according to workload and demand. In 12c, you can set a hard limit on PGA by turning on automatic PGA management, which requires setting the PGA_AGGREGATE_LIMIT parameter. Therefore, you can now set hard limits on PGA by setting new parameters to avoid overusing PGA.
