ORACLE12C new features: Limiting the size of the PGA's use of memory

As we all know, before 12c, the management of PGA memory is controlled using the Pga_aggregate_target parameter, but this parameter is only a reference value, Oracle instance just try to ensure that the total PGA usage is within this value range, When the PGA memory used by the session exceeds this limit, Oracle cannot impose any enforcement measures to limit the size of the memory used.

New features were introduced in the 12.1.0.1 version: Use the Pga_aggregate_limit parameter to limit the upper limit of memory used by the Oracle instance PGA. The background process CKPT checks the total amount of memory used by the PGA every three seconds and, if the limit is exceeded, terminates the usage of the PGA memory, and does not include the job queue for the SYS user process and background process. This limitation does not cause the PGA memory to go crazy, resulting in memory exhaustion.

Official Document: http://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA95344

The default pga_aggregate_limit parameter is 2G or 200% of the Pga_aggregate_target value or the processes parameter value *3m

Test Database version 12.1.0.2

Sql> select * from V$version; BANNER con_id------------------------------------------------------------------------------------------Oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit Production 0pl/sql release 12.1.0.2.0-production 0core12.1. 0.2.0Production 0TNS for linux:version 12.1.0.2.0-production 0NLSRTL Version 12.1.0.2.0-production 0

View Pga_aggregate_limit parameter value size is 2G

Sql> Show parameter Pganame TYPE VALUE-------------------------------------------------------------------------- ---pga_aggregate_limit big integer 2gpga_aggregate_target big integer 250M

Create a test user

Sql> alter session set CONTAINER=PDB_ORCL; Session altered. Sql> create user ZX identified by ZX; User created. Sql> Grant DBA to ZX; Grant succeeded. Sql> Conn Zx/[email protected]_orclconnected.

Create a package to demonstrate the use of the PGA

Sql> Create or Replace package Demo_pkg 2 as 3 type array is table of char (a) index by Binary_integer;  4 G_data Array;  5 end; 6/package created.

Viewing the current session SID and using the PGA memory condition

Sql> select userenv (' Sid ')  from dual; USERENV (' SID ')--------------            22-- The current session SID is 22sql> select a.name, to_char (b.value,  ' 999,999,999 ')  bytes,  2          to_char (Round (b.value/1024/1024,1),  ' 99,999.9 '  )  mbytes  3    from v$statname a, v$mystat b   4   where a.statistic# = b.statistic#  5      and a.name like  '%ga memory% ';name                                                                bytes         MBYTES---------------------------------------------------------------- ------------ ---------session  uga memory                                                     2,301,312       2.2session uga memory max                                                 2,424,824       2.3session  pga memory                                                    3,715,176       3.5session  pga memory max                                                3,715,176        3.5--current session uses PGA memory to 3.5MB

Execute the previously created package to see the PGA memory usage

--loop execution 200,000 times view PGA memory usage sql> begin  2           FOR I IN 1&NBSP, ....  200000  3          loop  4                   demo_ Pkg.g_data (i)  :=  ' x ';   5          end  loop;  6  end;  7  /pl/sql procedure successfully  completed. Sql> select a.name, to_char (b.value,  ' 999,999,999 ')  bytes,  2          to_char (Round (b.value/1024/1024,1),  ' 99,999.9 '  )  mbytes  3    from v$statname a, v$mystat b   4   where a.statistic# = b.statistic#  5     and a.name like  '%ga memory% ';name                                                                 BYTES         MBYTES---------------------------------------------------------------- ------------ ---------session  uga memory                                                  470,213,072      448.4session uga memory max                                              470,213,072     448.4session pga  memory                                                  471,773,288      449.9session pga memory max                                               471,773,288     449.9--a total of 449MB of memory, you can calculate the loop to perform 200000*5 times occupy the PGA will exceed the set 2gsql> begin  2           FOR I IN 1&NBSP, ....  1000000  3          loop  4                   demo_ Pkg.g_data (i)  :=  ' x ';   5          end  loop;  6  end;  7  /begin*error at line 1:o Ra-04036: pga memory used by the instance exceeds pga_aggregate_ limit--error ORA-4036 exceeds the Pga_aggregate_limit setting of 2G

Adjust the pga_aggregate_limit to 4G after the error process again, there is no problem.

Sql> conn / as sysdbaconnected.sql> alter system set pga_ aggregate_limit=4g; System altered. sql> conn zx/[email protected]_orclconnected.sql> begin  2           FOR I IN 1&NBSP, ....  1000000  3          loop  4                   demo_ Pkg.g_data (i)  :=  ' x ';   5          end  loop;  6  end;  7  /pl/sql procedure successfully  completed. sql> show parameter pganame                                &nBsp;  type        value------------------------------------  ----------- ------------------------------pga_aggregate_limit                   big integer 4gpga_aggregate_ target                  big integer 250m

To cancel the PGA limit, set the pga_aggregate_limit=0.

alter system set pga_aggregate_limit=0;

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1885869

ORACLE12C new features: Limiting the size of the PGA's use of memory