Skip to Main Content
  • Questions
  • The "greater" PGA memory size allocated in rman channel

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, yulin.

Asked: February 19, 2021 - 11:40 am UTC

Last updated: March 02, 2021 - 6:50 pm UTC

Version: 11.2.0.4

Viewed 100+ times

You Asked

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> 

and we said...

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.

Rating

  (1 rating)

Comments

The doubt about the _pga_max_size

A reader, March 02, 2021 - 2:52 pm UTC

Could you explain more things about the _pga_max_size 。。。?
Chris Saxon
March 02, 2021 - 6:50 pm UTC

It's an underscore parameter - leave it alone unless instructed by Oracle support

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database