SQL & PL/SQL

Hi All,

I have a normal insert query that picks up data from one table over a database link and inserts it into a table on another database.

We then picked up the query and put it in a stored procedure and the procedure is executed via scheduler so that whenever data comes it is inserted.
It was working fine initially however since the last 2 months ithas been giving us trouble i.e it is taking to long to execute. It is taking more than 4 hrs to just insert 1.5 lakh records.

The strange thing is that for records below 1 lakh i.e approximately 70000-80000 records it works fine. The query works fine for 1.5 lakh records also if just the query is executed out of the procedure. It is only when the query is executed in the proc via scheduler that it takes so much time.

Please give any suggestions as to how to analyze this behaviour or why this might be happening.

We also have the table analyzed before inserting into it.