最近同事遇到一个问题，某个报表程序报错ora-4030的错误：
|
1 |
错误信息：[Error] **** Dbupdate.sqlUpdateSubs Function:begin ? := trt_subs_1_i_update_target (?, ?, ?, ?, ?); end;, Execute GD:1li run finished with error, errcode:-4030,errmsg:ORA-04030: out of process memory when trying to allocate 16408 bytes (koh-kghu call ,pmucalm coll),errlog:GD_1li TableA_S.(SCS or ADS) Update TableP_S, circle= 2. |
通过trace，发现是以下语句的问题：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
declare type subs_mobile_no_arr is table of trt_subs_GD_1li_wap_S.subs_mobile_no%type index by pls_integer; type icp_id_arr is table of trt_subs_GD_1li_wap_S.icp_id%type index by pls_integer; type icp_code_arr is table of trt_subs_GD_1li_wap_S.icp_code%type index by pls_integer; type channel_id_arr is table of trt_subs_GD_1li_wap_S.channel_id%type index by pls_integer; type serv_id_arr is table of trt_subs_GD_1li_wap_S.serv_id%type index by pls_integer; type brand_id_arr is table of trt_subs_GD_1li_wap_S.brand_id%type index by pls_integer; type fee_mobile_no_arr is table of trt_subs_GD_1li_wap_S.fee_mobile_no%type index by pls_integer; type action_id_arr is table of trt_subs_GD_1li_wap_S.action_id%type index by pls_integer; type crt_his_date_arr is table of trt_subs_GD_1li_wap_S.crt_his_date%type index by pls_integer; type city_id_arr is table of trt_subs_GD_1li_wap_S.city_id%type index by pls_integer; type attr_arr is table of trt_subs_GD_1li_wap_S.attr%type index by pls_integer; type srh_channel_id_arr is table of trt_subs_GD_1li_wap_S.srh_channel_id%type index by pls_integer; arr_subs_mobile_no subs_mobile_no_arr; arr_icp_id icp_id_arr; arr_icp_code icp_code_arr; arr_channel_id channel_id_arr; arr_serv_id serv_id_arr; arr_brand_id brand_id_arr; arr_fee_mobile_no fee_mobile_no_arr; arr_action_id action_id_arr; arr_crt_his_date crt_his_date_arr; arr_city_id city_id_arr; arr_attr attr_arr; arr_srh_channel_id srh_channel_id_arr; begin select subs_mobile_no, icp_id, icp_code, channel_id, serv_id, brand_id, fee_mobile_no, decode(action_id, 'SCS', 'C', 'ADS', 'D') action_id, crt_his_date, city_id, attr, srh_channel_id bulk collect into arr_subs_mobile_no, arr_icp_id, arr_icp_code, arr_channel_id, arr_serv_id, arr_brand_id, arr_fee_mobile_no, arr_action_id, arr_crt_his_date, arr_city_id, arr_attr, arr_srh_channel_id from trt_subs_GD_1li_wap_S where action_id = 'SCS' or action_id = 'ADS'; if arr_subs_mobile_no.last > 0 then arr_srh_channel_id srh_channel_id_arr; begin select subs_mobile_no, icp_id, icp_code, channel_id, serv_id, brand_id, fee_mobile_no, decode(action_id, 'SCS', 'C', 'ADS', 'D') action_id, crt_his_date, city_id, attr, srh_channel_id bulk collect into arr_subs_mobile_no, arr_icp_id, arr_icp_code, arr_channel_id, arr_serv_id, arr_brand_id, arr_fee_mobile_no, arr_action_id, arr_crt_his_date, arr_city_id, arr_attr, arr_srh_channel_id from trt_subs_GD_1li_wap_S where action_id = 'SCS' or action_id = 'ADS'; if arr_subs_mobile_no.last > 0 then forall i in arr_subs_mobile_no.first .. arr_subs_mobile_no.last update f_wap_subs_GD_p_s c set c.city_id = arr_city_id(i), c.icp_id = arr_icp_id(i), c.icp_code = arr_icp_code(i), c.subs_cancel_channel_id = arr_channel_id(i), c.brand_id = arr_brand_id(i), c.subs_status_id = arr_action_id(i), c.fee_mobile_no = arr_fee_mobile_no(i), c.usubs_date = arr_crt_his_date(i), c.usubs_day = to_char(arr_crt_his_date(i), 'YYYYMMDD'), c.idx_day = to_char(arr_crt_his_date(i), 'YYYYMMDD'), c.is_subs_valid = 0, c.flow_time = sysdate, c.srh_channel_id = arr_srh_channel_id(i) where subs_mobile_no = arr_subs_mobile_no(i) and serv_id = arr_serv_id(i) and is_subs_valid = 1; end if; end; |
这个sql在执行的时候，pga的内存被迅速的消耗，通过v$ses […]