After a migration from 12.10.FC10 to 12.10.FC13, a stored
procedure that simply reads data from an external source, does
some evaluation and performs an insert like this:
INSERT INTO (a_acc_sid, a_agr_sid,
a_cust_sid, a_acc_nm, a_acc_opn_dt, a_acc_opn_rsn, a_acc_cls_dt,
a_acc_cls_rsn, a_crst_cd, a_bil_ctry_cd, a_allw_arch_flg,
a_accno, a_own_ctry_cd, a_del_flg, a_own_srv_area_cd,
a_acc_sgm_ty, a_skp_wh_prcs, a_prt_own_labl, a_ib_pod_flg,
a_ob_pod_flg, a_acc_st, a_acc_act_st, a_acc_crdt_st,
a_crdt_stp_dt, a_crdt_stp_rsn, a_prnt_chld_cd,
a_on_wtch_lst_flg, a_cr_usr, a_cr_dtm, a_upd_usr, a_upd_dtm,
a_version, a_source, a_frg_ctry_cd, a_frg_dat_st,a_srva_fc_cd)
VALUES (p_a_acc_sid, p_a_agr_sid, p_a_cust_sid,
v_acc_nm, p_acc_accnt_opn_dt, p_acc_accnt_opn_rsn,
p_acc_accnt_cls_dt, (case when p_acc_accnt_cls_dt is null then
NULL ELSE p_acc_accnt_cls_rsn END), p_a_crst_cd, p_cntry_cd,
p_acc_allow_archv_yn, p_acc_accnt_no, p_acc_ownr_cntry_cd,
p_acc_flg_arc_yn, p_acc_ownr_srv_area_cd,
(CASE WHEN (p_acc_ownr_cntry_cd IN ('ID','MY')
AND p_acc_sgmnt_ty = 'C' AND p_a_acc_purpose_cd = 'CSH' AND
p_a_accty_cd = 'GEN') THEN 'O' ELSE p_acc_sgmnt_ty END),
'N', 'Y', p_ref_inbd_pod_ind,
p_ref_otbd_pod_ind, p_a_acc_st, p_active_status,
v_acc_crdt_st,
(CASE WHEN v_acc_crdt_st = 'S' THEN
(CASE WHEN p_acc_accnt_cls_dt IS NOT NULL
THEN p_acc_accnt_cls_dt ELSE DATE(p_acc_last_mdfy_ts) END)
ELSE NULL END),
v_crdt_stp_rsn,
p_acc_parent_child_cd, 'N',
p_acc_last_mdfy_user,
(CASE WHEN p_acc_creation_dt IS NOT NULL THEN
p_acc_creation_dt ELSE (CASE WHEN p_acc_accnt_opn_dt IS NOT NULL
THEN p_acc_accnt_opn_dt ELSE DATE(p_acc_last_mdfy_ts) END) END),
p_acc_last_mdfy_user, p_acc_last_mdfy_ts, p_version,
p_source, p_acc_ownr_cntry_cd, p_a_acc_st, p_acc_srva_fc_cd);
... which is executed thousands of times in the same SPL call,
results in continuous session ralloc memory pool growth:
ralloc 0 9511808 gentcb 0
4104
ralloc 0 9912120 gentcb 0
4104
ralloc 0 10172288 gentcb 0
4104
ralloc 0 10631608 gentcb 0
4104
ralloc 0 10957144 gentcb 0
4168
ralloc 0 11301208 gentcb 0
4176
ralloc 0 11747760 gentcb 0
4104
ralloc 0 12069760 gentcb 0
4176
ralloc 0 12331904 gentcb 0
4176
ralloc 0 12725120 gentcb 0
4032
ralloc 0 13075024 gentcb 0
4104
ralloc 0 13447512 gentcb 0
4176
ralloc 0 13775192 gentcb 0
4176
ralloc 0 14165808 gentcb 0
4104
ralloc 0 14478992 gentcb 0
4104
ralloc 0 14871504 gentcb 0
4104
ralloc 0 15220056 gentcb 0
4104
ralloc 0 15641320 gentcb 0
4104
onstat -g afr output indicates the memory allocation comes from:
26fb00000 16384 ralloc 2986 sqalloc.c:1949
d0dd9000 16384 ralloc 2986 sqalloc.c:1949
d0cb1000 16384 ralloc 2986 sqalloc.c:1949
c0b04000 16384 ralloc 2986 sqalloc.c:1949
bc2a1000 16384 ralloc 2986 sqalloc.c:1949
4d71f5000 16384 ralloc 2986 sqalloc.c:1949
d2673000 16384 ralloc 2986 sqalloc.c:1949
367fae000 16384 ralloc 2986 sqalloc.c:1949
306bd7000 16384 ralloc 2986 sqalloc.c:1949
33acfe000 16384 ralloc 2986 sqalloc.c:1949
27d0fb000 16384 ralloc 2986 sqalloc.c:1949
bf8d7000 16384 ralloc 2986 sqalloc.c:1949
bc1ed000 16384 ralloc 2986 sqalloc.c:1949 |
