Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

pga_aggregate_limit parameter and CDB/PDB

Mohamed HouriFeb 8 2024

I don't know if this is documented or is an abnormal functioning of the pga aggregate limit. According to my tests (see below) done both in 19c and 23c, it turns out that when logged into a root container, with a user (different from SYS) we are not concerned by the limit imposed via the pga_aggregate_limit. This is also the case even when we alter session to change the container from the root cdb to the pbd. The pga limit seems to be imposed only if we are connected directly to the pdb.

-- First test done in the root container

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show user
USER is "C##MHOURI"

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 600M

SQL> @mysid
      SID
----------
      237
       
       
DECLARE
   TYPE v1_type IS
       TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
   v1 v1_type;
BEGIN
   FOR i IN 1..130000 LOOP
      v1(i) := rpad('x', 32767);
   END LOOP;
END;
/
PL/SQL procedure successfully completed. --> no error despite the 5GB of pga used

SQL> @getpgaSID 237
Display session 237 memory usage from v$process_memory....

 SID  PID    SERIAL# CATEGORY  PGA_ALLOC_GB PGA_USED_GB PGA_MAX_ALLOC_GB     CON_ID
---- ---- ---------- --------- ------------ ----------- ---------------- ----------
 237   35          4 Other              .01                          .01          1
 237   35          4 PL/SQL            5.08        4.95             5.08          1
 237   35          4 SQL                  0           0                0          1

Now, I am going to repeat the same thing but I will switch to the pdb container

SQL> alter session set container=ORCLPDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1
SQL> show user
USER is "C##MHOURI"
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 600M
SQL> @mysid
      SID
----------
      237
       
DECLARE
   TYPE v1_type IS
       TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
   v1 v1_type;
BEGIN
   FOR i IN 1..130000 LOOP
      v1(i) := rpad('x', 32767);
   END LOOP;
END;
/
PL/SQL procedure successfully completed. -> no error despite the 5GB of pga used

SQL> @getpgaSID 237
Display session 237 memory usage from v$process_memory....
SID  PID    SERIAL# CATEGORY PGA_ALLOC_GB PGA_USED_GB PGA_MAX_ALLOC_GB     CON_ID
---- ---- ---------- -------- ------------ ----------- ---------------- ----------
237   35          6 Other             .04                          .04          3
237   35          6 PL/SQL           4.78        4.65             4.78          3
237   35          6 SQL                 0           0                0          3
 

Finally, I'm going to do the same thing again, but this time I will firt connect directly to the pdb.

[oracle@localhost xyz]$ sqlplus c##mhouri/mhouri@localhost:1521/ORCLPDB1

SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1

SQL> show user
USER is "C##MHOURI"

SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
pga_aggregate_limit                  big integer 2G
pga_aggregate_target                 big integer 600M

SQL> @mysid
      SID
----------
      237
      
DECLARE
   TYPE v1_type IS
       TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
   v1 v1_type;
BEGIN
   FOR i IN 1..130000 LOOP
      v1(i) := rpad('x', 32767);
   END LOOP;
END;
/
       
DECLARE
*
ERROR at line 1:
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

SQL> @getpgaSID 237
Display session 237 memory usage from v$process_memory....

 SID  PID    SERIAL# CATEGORY  PGA_ALLOC_GB PGA_USED_GB PGA_MAX_ALLOC_GB     CON_ID
---- ---- ---------- --------- ------------ ----------- ---------------- ----------
 237   35          7 Other              .03                          .03          3
 237   35          7 PL/SQL               0           0             1.68          3
 237   35          7 SQL                  0           0                0          3
Show Active workarea memory usage for where sid=237...

In short, it's understandable not to perform heavy processing from the root container, but it would have been acceptable if this limit had been imposed boot in root and pdb containers.

Best regards

Mohamed Houri

Comments
Post Details
Added on Feb 8 2024
0 comments
51 views