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>
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>
1) The limit is on work areas - one process can have many of these. For example, a SQL statement with active hash joins and sorts.
2) pga_aggregate_target is a goal, not a fixed upper limit. The actual amount allocated can be greater than this. MOS note 1392549.1 has several examples of how this works with the other memory target parameters.