Re: script taking time to run on DB

Posted by Roshan on Jul 25, 2018; 9:09am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/script-taking-time-to-run-on-DB-tp6492p6493.html

Hi Erman,

I have checked the script running containing the SQL ID which is blocking.

insert into cbs_sum_dun2
(account_code_n,
tot_inv_amt,
tot_tax_amt,
TOT_AMT_CLR,
tot_bills_due
)
select account_code,sum(invoice_amt),sum(inv_tax_amt),sum(inv_amt_clr),sum(amt_due)
from dun_details
--where account_code = '879751'
group by account_code;

commit;

update cbs_sum_dun2 s
set s.cnt =
(select
count(*)
from dun_details c
where s.account_code_n = c.account_code
group by c.account_code)
where s.LAST_BILL_DATE is null;

commit;


update cbs_sum_dun2 s
set s.over_30 =
(select sum(c.amt_due) from dun_details c
 where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
--and trunc(c.INVOICE_DATE)  <= '30-JUN-17'
and substr(trans_num,1,4) = 'FINV'
and (sysdate  - trunc(c.INVOICE_DATE)) > 30 and (sysdate - trunc(c.INVOICE_DATE)) <= 60)
where s.LAST_BILL_DATE is null
/

commit;

update cbs_sum_dun2 s
set s.over_60 =
(select sum(c.amt_due) from dun_details c
 where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
--and ( (trunc(c.INVOICE_DATE)  <= '01-JUN-17')
and substr(trans_num,1,4) = 'FINV'
and (sysdate  - trunc(c.INVOICE_DATE)) > 60 and (sysdate - trunc(c.INVOICE_DATE)) <= 90)
where s.LAST_BILL_DATE is null

As shown above, it is inserting into table 'cbs_sum_dun2'. It is then doing 3 consecutive updates.