performance - oracle - query execution very slow -

performance - oracle - query execution very slow -

i have big problem query in oracle 11g. query this:

insert ldp_t_impacto_pc (my_ctrl, tipo_my_ctrl, my_exec_date, my_load_identificator, type_mp, value_u, value_type, potencial) select a.my_ctrl, 'number' tipo_my_ctrl, a.my_exec_date, a.my_load_identificator, b.type_mp, c.value_u, d.value_type, decode(d.value_type,'saldo sdp', decode(max(e.my_coins),min(e.my_coins),max(e.my_coins),'error'), sum(a.counter * d.valor)) my_impact ldp_t_my_numbrer_warnings_group a, ldp_r_impact_pnt b, ldp_r_pc_value_u c, ldp_r_value_u d, ( select a.my_ctrl, round(sum(d.account_balance)) my_coins ldp_t_my_numbrer_warnings a, ldp_r_pc_value_u b, ldp_r_impact_pnt c, ldp_t_sdp_all d 1=1 , a.my_ctrl = b.my_ctrl , a.my_ctrl = c.my_ctrl , a.number = d.number , b.value_u = 'money sdp' , a.my_exec_date = (select max(load_processed_date) software_r_load_processed_date) , to_number(a.warnings_recurrence) >= to_number(c.recorrence) grouping a.my_ctrl ) e a.my_ctrl = b.my_ctrl , b.my_ctrl = c.my_ctrl , c.value_u = d.value_u , a.my_ctrl= e.my_ctrl (+) , a.my_exec_date = (select max(load_processed_date) software_r_load_processed_date) , a.warnings_recurrence >= b.recorrence grouping a.my_ctrl, a.my_exec_date, a.my_load_identificator, b.type_mp, c.value_u, d.value_type

we execute query time week, , work , execute in 10 minutes. week spend 36 hours , not finished yet.

i utilize sql developer , seek study explication plan:

query plan rows rowsource time(s) _____________________________________________________________ insert statement cost = 65159 0 load table conventional 0 hash grouping 47 0 nested loops outer 47 0 hash bring together 47 0 hash bring together 60 0 index fast total scan pkc_r_impact_pnt_1pc2rec3ti 214 0 merge bring together cartesian 808k 0 table access total ldp_t_my_numbrer_warnings_group 3k 0 sort aggregate 1 0 index total scan (min/max) pkc_load_processed_date_1lpc 1 0 buffer sort 212 0 index fast total scan pkc_r_pc_value_u_1pc2vu 212 0 table access total ldp_r_value_u 93 0 view pushed predicate 1 0 sort grouping 1 0 nested loops 0 nested loops 1 0 hash bring together 1 9 nested loops 34 0 index range scan pkc_r_impact_pnt_1pc2rec3ti 1 0 partition range single 34 0 partition list 34 0 table access local index rowid ldp_t_my_numbrer_warnings 34 9031 index skip scan pkc_my_numbrer_warnings_pt_1vd2pc3m 86 324 sort aggregate 1 0 index total scan (min/max) pkc_load_processed_date_1lpc 1 0 view vw_gbf_14 1 0 sort grouping 1 0 index unique scan pkc_r_pc_value_u_1pc2vu 1 0 index range scan nui_sdp_number 1 0 table access index rowid ldp_t_sdp_all 1 0

in explication part alter tree lines:

hash bring together 1 6 table access local index rowid es_t_msisdn_alarms 34 14513 index skip scan pkc_msisdn_alarms_pt_1vd2pc3m 86 656

some 1 can help me understand this? give thanks you.

performance oracle oracle11g oracle-sqldeveloper