ORA-04030，ora0403032位

ORA-04030，ora0403032位
--報錯：無法分配20m
Dump continued from file: /u01/app/oracle/diag/rdbms/szaa/szaa2/trace/szaa2_dw01_122659.trc
ORA-04030: out of process memory when trying to allocate 24520 bytes (koh-kghu sessi,kpmalloc)


--匯出操作用掉4G 記憶體
pid 112: 1860 KB used of 2229 KB allocated 
pid 113: 4102 MB used of 4105 MB allocated    --------查看關鍵點...
------------------------------------
Begin session detail for pid 113
  sid: 1625 ser: 56665 audsid: 0 user: 0/SYS
    flags: (0x8000051) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0xc0409) -/-/INC
  pid: 113 O/S info: user: oracle, term: UNKNOWN, ospid: 122659
    image: oracle@szaadb02.shenzhenair.com (DW01)
  current SQL:
   BEGIN
   SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_02', 'BACKUP', 0);
 END;
End session detail for pid 113
------------------------------------
pid 114: 2200 KB used of 3557 KB allocated (1024 KB freeable)             






--總共記憶體配置...
(percentage is of 5140 MB total allocated memory)
80% pid 113: 4102 MB used of 4105 MB allocated  <= CURRENT PROC  -------------匯出進程....占Pga 80%
 3% pid 106: 37 MB used of 168 MB allocated (129 MB freeable)
 2% pid 36: 73 MB used of 78 MB allocated 
 2% pid 37: 73 MB used of 78 MB allocated 
 2% pid 40: 73 MB used of 78 MB allocated 
 1% pid 91: 55 MB used of 56 MB allocated (704 KB freeable)
 1% pid 38: 34 MB used of 36 MB allocated 
 1% pid 21: 13 MB used of 27 MB allocated (10 MB freeable)
 1% pid 20: 13 MB used of 27 MB allocated (10 MB freeable)
 0% pid 19: 13 MB used of 25 MB allocated (8256 KB freeable)  
 
 
--總共記憶體應該是這個... 隱含參數控制...
18:10:09 sys@szaa2(szaadb02.shenzhenair.com)> show parameter pga


NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
pga_aggregate_target                 big integer                    5G
18:10:12 sys@szaa2(szaadb02.shenzhenair.com)>  



              Begin  End
Host Mem (MB): 96,531.496,531.4
SGA use (MB):   20,480.020,480.0
PGA use (MB):   5,075.9828.4              ----------------匯出進程結束，進程迅速降低。
% Host Mem used for SGA+PGA: 26.4722.07

正常匯出記憶體使用量

Host Mem (MB):96,531.496,531.4

SGA use (MB):20,480.020,480.0

PGA use (MB):1,413.91,179.1% 

Host Mem used for SGA+PGA:22.6822.44

--總共記憶體應該是這個... 隱含參數控制...
[oracle@szaa2 /home/oracle/shsnc]$ ora  _param|grep pga
__pga_aggregate_target                   5368709120           N N I N N Current target size for the aggregate PGA memory consumed
_kdli_sio_pga                            FALSE                Y Y N N N use PGA allocations for direct IO
_kdli_sio_pga_top                        FALSE                Y Y N N N PGA allocations come from toplevel PGA heap
_ldr_pga_lim                             0                    Y Y I N N pga limit, beyond which new partition loads are delayed
_pga_large_extent_size                   1048576              Y N N N N PGA large extent size
_pga_max_size                            1073725440           Y N I N N Maximum size of the PGA memory for one process
_pgactx_cap_stacks                       FALSE                Y Y I N N capture stacks for setting pgactx
_simulate_mem_transfer                   FALSE                Y N I N N simulate auto memory sga/pga transfers
_smm_advice_enabled                      TRUE                 Y N I N N if TRUE, enable v$pga_advice
_use_ism_for_pga                         TRUE                 Y N N N N Use ISM for allocating large extents