Shulou(Shulou.com)06/01 Report--

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

A new feature has been introduced in version 12.1.0.1: the use of 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 PGA every three seconds. If the limit is exceeded, the session is terminated to reduce the amount of PGA memory used. For SYS user processes and background processes, excluding job queues will not be terminated. With this limit, it will not cause PGA memory to soar and lead to memory exhaustion.

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

By default, the PGA_AGGREGATE_LIMIT parameter is 2G or 200% PGA_AGGREGATE_ target value or 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 0CORE 12.1.0.2.0 Production 0TNS for Linux: Version 12.1.0.2.0-Production 0NLSRTL Version 12.1.0.2.0-Production 0

Check that the PGA_AGGREGATE_LIMIT parameter value 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/zx@pdb_orclConnected.

Create a package for the demo to occupy PGA

SQL > create or replace package demo_pkg 2 as 3 type array is table of char (2000) index by binary_integer; 4 g_data array; 5 end; 6 / Package created.

View current session sid and PGA memory usage

SQL > select userenv ('sid') from dual USERENV ('SID')-22 murals-the current session sid is 22SQL > select a.name, to_char (b.value,' 999999999') bytes, 2 to_char (round (b.valuehand 1024 from v$statname 1), '99999.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 2301312 2.2session uga memory max 2424824 2.3session pga memory 3715176 3.5session pga memory max 3715176 3.5 mi-current session using PGA memory for 3.5MB

Execute the package created earlier to view PGA memory usage

-- cycle through 200000 times to check PGA memory usage SQL > begin 2 for i in 1. 200000 3 loop 4 demo_pkg.g_data (I): ='x end loop;; 5 end loop; 6 end 7 / PL/SQL procedure successfully completed.SQL > select a.name, to_char (b.value, '999999999') bytes, 2 to_char (round (b. ValueGreat 1024), '99999.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 470213072 448.4session uga memory max 470213072 448.4session pga memory 471773288 449.9session pga memory max 471773288 449.9 Murray-sharing 449MB memory It can be calculated that the PGA consumed by the cycle execution 200000 minutes 5 times will exceed the set 2GSQL > begin 2 for i in 1.. 1000000 3 loop 4 demo_pkg.g_data (I): ='x end;; 5 end loop; 6 end; 7 / begin*ERROR at line 1:ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT-- error ORA-4036 exceeds 2G set by PGA_AGGREGATE_LIMIT

After adjusting the PGA_AGGREGATE_LIMIT to 4G, execute the error reporting process again, and there will be no problem.

SQL > conn / as sysdbaConnected.SQL > alter system set PGA_AGGREGATE_LIMIT=4G;System altered.SQL > conn zx/zx@pdb_orclConnected.SQL > begin 2 for i in 1. 1000000 3 loop 4 demo_pkg.g_data (I): ='x end loop;; 5 end loop; 6 end 7 / PL/SQL procedure successfully completed.SQL > show parameter pgaNAME TYPE VALUE---pga_aggregate_limit Big integer 4Gpga_aggregate_target big integer 250M

Cancel the restriction on PGA and set up pga_aggregate_limit=0.

Alter system set PGA_AGGREGATE_LIMIT=0

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.