The “unexpected greater” PGA size allocated in rman channel

oracle-11g-r2rman

Environment: RHEL 6.9、 Oracle 11.2.0.4

When we running some rman backup/restore jobs, we found the actual PGA size could greatly exceed the predefined limit value about PGA:

  1. Why the following each rman channel process memory allocated were almost all more than the _pga_max_size?
  2. Why the following total rman channel processes' memory allocated could be far more than the global pga_target?
SQL> 
SQL>  
SQL> SELECT a.ksppinm AS parameter, c.ksppstvl AS VALUE, a.ksppdesc AS description, b.ksppstdf AS "Default?"
  2  FROM x$ksppi a,  x$ksppcv b,  x$ksppsv c  WHERE a.indx = b.indx  AND a.indx = c.indx AND a.ksppinm IN 
  3  ('pga_aggregate_target','_pga_max_size','_smm_max_size','_smm_px_max_size') ORDER BY a.ksppinm;

PARAMETER             VALUE              DESCRIPTION                                        Default?
--------------------- ------------------ -------------------------------------------------- ----------
_pga_max_size         209715200          Maximum size of the PGA memory for one process     TRUE
_smm_max_size         102400             maximum work area size in auto mode (serial)       TRUE
_smm_px_max_size      524288             maximum work area size in auto mode (global)       TRUE
pga_aggregate_target  1073741824         Target size for the aggregate PGA memory consumed  FALSE
                                         by the instance


SQL> 
SQL> select component, current_size/1024/1024  "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", max_size/1024/1024 "MAX_SIZE", 
  2  user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE"  from  v$memory_dynamic_components where rownum < 100;

COMPONENT                                                        CURRENT_SIZE   MIN_SIZE   MAX_SIZE USER_SPECIFIED_SIZE TYPE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- -------------
shared pool                                                               656        528        656                   0 GROW
large pool                                                                416        416        416                   0 STATIC
java pool                                                                  64         64         64                   0 STATIC
streams pool                                                                0          0          0                   0 STATIC
SGA Target                                                               3072       3072       3072                   0 STATIC
DEFAULT buffer cache                                                     1888       1888       1904                   0 SHRINK
KEEP buffer cache                                                           0          0          0                   0 STATIC
RECYCLE buffer cache                                                        0          0          0                   0 STATIC
DEFAULT 2K buffer cache                                                     0          0          0                   0 STATIC
DEFAULT 4K buffer cache                                                     0          0          0                   0 STATIC
DEFAULT 8K buffer cache                                                     0          0          0                   0 STATIC
DEFAULT 16K buffer cache                                                    0          0          0                   0 STATIC
DEFAULT 32K buffer cache                                                    0          0          0                   0 STATIC
Shared IO Pool                                                              0          0          0                   0 STATIC
PGA Target                                                               1024       1024       1024                1024 STATIC
ASM Buffer Cache                                                            0          0          0                   0 STATIC

16 rows selected.

SQL> 
SQL> 
SQL> select sid, spid, s.program, event, pga_used_mem,pga_max_mem from v$process p, v$session s 
  2  where addr = s.paddr and s.program like '%rman%'  order by pga_used_mem desc;

    SID SPID                     PROGRAM                                          EVENT                             PGA_USED_MEM PGA_MAX_MEM
------- ------------------------ ------------------------------------------------ --------------------------------- ------------ -----------
   1123 120921                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        542165387   562245814
   1057 120917                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        542127691   562245814
   1024 120915                   rman@JCHXRST (TNS V1-V3)                         Backup: MML read backup piece        541392107   562753046
    892 120907                   rman@JCHXRST (TNS V1-V3)                         SQL*Net message from client          106517430   113455286
   1090 120919                   rman@JCHXRST (TNS V1-V3)                         Backup: MML restore backup piece      21407539    24588470
    958 120914                   rman@JCHXRST (TNS V1-V3)                         SQL*Net message from client             980323     1913014

6 rows selected.

SQL> 
SQL> 

Updated:
It's just for the RMAN restore job !
And we just use the customized RMAN script with the default RMAN configuration:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SGPMDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+SGPMDB_ARC/snapcf_sgpmdb.f';

RMAN>


rman target / <<EOF
run {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
allocate channel ch02 type 'sbt_tape';
allocate channel ch03 type 'sbt_tape';
SEND 'NB_ORA_SERV=jchxbak,NB_ORA_CLIENT=scyxdb3';
set until time "to_date('2020-08-22 15:12:58','YYYY-MM-DD HH24:MI:SS')";
SET NEWNAME FOR DATABASE to '+data/%U';
restore database ;
switch datafile all;
switch tempfile all;
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
}
exit;

Best Answer

== Partial answer as question require more context ==

== As i'm new here, i can't put that in the comments, so if reviewers could right the things ==

As it's Oracle 11, encryption cause can be casted out.

For point 1 & 2 could you precise if you're doing inconsistent backups (archive log, redo log, etc.) or consistent backups (cold backup, etc.) ?

As it concerns the PGA, that could be the result of your RMAN config (RMAN uses PLSQL package for backup and recover), i suggest to tune your buffers sizes and / or multiplexing level.

Update

Seeing that you've the default config, that means that your restore target disks (Tape backups / restores are more prone to target PGA). With disks, PGA load could come from Synchronous I/O with a high DBWR_IO_SLAVES. I suggest that you check these in your database config. If DBWR_IO_SLAVES is zero and you have asynchronous I/O, i suggest to reduce buffers size.