Open Encumbrance Year: Slow Performance

At our site this concurrent program was too slow. It did not complete even after 1 week. During that time, DBAs recycled concurrent managers, so this program was restarted. When this program runs, I think it checks for existence of records for current year, if record exists then it deletes using following sql. This sql too was taking too much time.

DELETE FROM gl_balances b WHERE (((b.ledger_id=:b0 AND b.period_year =:b1) AND b.actual_flag ='E') AND rownum <=5000);

I think that sql is executed in a loop and there must be a commit after deleting 5000 rows. i think this is being done to avoid snapshot too old or something.

In my experince with Oracle, i have seen this kind of delete in Oracle Pricing too. It was highly inefficient. It would have been much better if Oracle had used a script like

DECLARE type t_rowids is table of rowid index by binary_integer; l_rowids t_rowids; CURSOR c_gl_bal_recs is SELECT rowid from gl.gl_balances b where ((b.ledger_id=1 and b.period_year=2013) and b.actual_flag='E') FOR UPDATE SKIP LOCKED ; BEGIN OPEN c_gl_bal_recs; LOOP FETCH c_gl_bal_recs BULK COLLECT INTO l_rowids LIMIT 5000; IF l_rowids.COUNT = 0 THEN EXIT; END IF; -- FORALL i in l_rowids.FIRST..l_rowids.LAST DELETE from gl_balances WHERE rowid = l_rowids(i); -- commit; END LOOP; CLOSE c_gl_bal_recs; END;