Greetings!
This is on Exadata X9M system (DB node has 2TB memory total and 1TB free memory available, 600GB of PGA set. Database is on 19.18.
We are getting ORA-04030 error when the private memory hits 32GB. Appreciate if you can shed some insights on this.
1) Just wondering, if there is PL/SQL memory allocation limit with bulk collection ?
2) Even though we have free memory available, just trying to figure out why Oracle is not able to grow more than 32GB limit ?
Can you please suggest if we need to adjust any of the OS/DB settings below or bulk collect with LIMIT in the code has to be changed ?
Setting in the DB:
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 20G
pga_aggregate_limit big integer 0
pga_aggregate_target big integer 600G
Parameter Default Value Session Value Instance Value IS_SESSION_MODIFIABL IS_SYSTEM_MODIFIABLE
------------------------------ -------------------- ------------------------------ ------------------------------ -------------------- --------------------
_realfree_heap_pagesize TRUE 65536 65536 FALSE FALSE
_use_realfree_heap TRUE TRUE TRUE FALSE IMMEDIATE
-- more details follow from the trace --
PRIVATE HEAP SUMMARY DUMP
32 GB total: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 32 GB limit reached
32 GB commented, 1910 KB permanent
1539 KB free (0 KB in empty extents),
32 GB, 4 heaps: "koh-kghu call " 1241 KB free held
ORA-04030: out of process memory when trying to allocate 169040 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 16336 bytes (koh-kghu call ,pmuccst: adt/record)
========= Dump for incident 1292153 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
100% 32 GB, 2088675 chunks: "pmuccst: adt/record " PL/SQL
koh-kghu call ds=0x7f794779e250 dsprt=0x7f794ee10220
0% 93 MB, 32795 chunks: "free memory " PL/SQL
koh-kghu call ds=0x7f794779e250 dsprt=0x7f794ee10220
0% 1312 KB, 1169 chunks: "free memory "
pga heap ds=0x7f794ee10220 dsprt=(nil)
-------------------------
Top 10 processes:
-------------------------
(percentage is of 58 GB total allocated memory)
55% ospid 258173 ( ): 32 GB used, 32 GB alloc <= CURRENT PROC
1% ospid 59134 (ARC1): 97 MB used, 703 MB alloc, 601 MB freeable
1% ospid 258396_258418 (LMSH): 592 MB used, 600 MB alloc
1% ospid 258396_258417 (LMSF): 567 MB used, 568 MB alloc
ospid 258173 ( ): 32 GB used, 32 GB alloc
------------------------------------
current SQL:
select AMT_OUTSTANDG_HIST_BI_OBJ(INSTR_ID,CHANGE_DATE,CHANGE_REASON,CHILD_INSTR_ID,TRANCHE_INSTR_ID,AMOUNT_OUTSTANDING,AMOUNT_OUTSTANDING_DECIMAL,AMOUNT_OUTSTANDING_RATIO_DATE,CHANGE_DIFFERENCE,CHANGE_DIFFERENCE_DECIMAL,CHANGE_SOURCE,MARKERS_QUALIFIERS,TRANCHE_AMOUNT,TRANCHE_DATE,TRANCHE_ISSUE_PRICE,recorded_date_start,recorded_date_end,EFFECTIVE_DATE_START,effective_date_end,LOADED_BY)
bulk collect into msg_intraday_table from (
select level, CONNECT_BY_ISLEAF AS LEAF,rn,INSTR_ID,CHANGE_DATE,CHANGE_REASON,CHILD_INSTR_ID,TRANCHE_INSTR_ID,AMOUNT_OUTSTANDING,AMOUNT_OUTSTANDING_DECIMAL,AMOUNT_OUTSTANDING_RATIO_DATE,CHANGE_DIFFERENCE,CHANGE_DIFFERENCE_DECIMAL,CHANGE_SOURCE,MARKERS_QUALIFIERS,TRANCHE_AMOUNT,TRANCHE_DATE,TRANCHE_ISSUE_PRICE,LOADED_BY,
prev_amount_outstanding, recorded_date_start, recorded_date_end,
--CHANGE_DATE + interval '1' minute * (level-1) as effective_date_start, CHANGE_DATE + interval '1' minute * level as effective_date_end
case when rn = 1 then CHANGE_DATE else CHANGE_DATE + interval '1' minute * (rn-1) end as effective_date_start,
case when rn = 1 then CHANGE_DATE else CHANGE_DATE + interval '1' minute * rn end as effective_date_end
from (
select
ROW_NUMBER() OVER(PARTITION BY INSTR_ID, change_date order by change_date, amount_outstanding desc) as rn,
INSTR_ID, CHANGE_DATE,CHANGE_REASON,CHILD_INSTR_ID,TRANCHE_INSTR_ID,AMOUNT_OUTSTANDING,AMOUNT_OUTSTANDING_DECIMAL,AMOUNT_OUTSTANDING_RATIO_DATE,CHANGE_DIFFERENCE,CHANGE_DIFFERENCE_DECIMAL,CHANGE_SOURCE,MARKERS_QUALIFIERS,TRANCHE_AMOUNT,TRANCHE_DATE,TRANCHE_ISSUE_PRICE,LOADED_BY,
case when change_difference is null then amount_outstanding else amount_outstanding-change_difference end as prev_amount_outstanding,
sys_extract_utc(SYSTIMESTAMP) as recorded_date_start, v_end as recorded_date_end
from table (object_collection)
)
where level != CONNECT_BY_ISLEAF
CONNECT BY NOCYCLE PRIOR amount_outstanding = prev_amount_outstanding and prior INSTR_ID = INSTR_ID --and prior CHANGE_DATE = CHANGE_DATE
START WITH prev_amount_outstanding = 0);