Slow-running Data Load from one day to next

We are running 8.0.4 in a Sun environment. We have a very large datamart that has
data loaded on it from another database warehouse every night. The data is loading
using PL/SQL packages and procedures. The loads had been taking around six hours and then suddenly they started not finishing after 24 hours seemingly overnight.
This was just before I started working here.

Supposedly, nothing has changed in the code and the developer is reluctant to change the
code as it is in production now. We have tried adding some indexes and refreshing the
statistics and removing the statistics with a little improvement but not enough.

Two nights ago we switched from optimization mode of CHOOSE to ALL_ROWS in order to maximize throughput for this batch job. We had marked improvement going from more
than a 24-hour load to 4 hours. We bounced the database in the morning to switch back
to choose during the day. We then bounced the database again in the evening and ran
the same load overnight but it had not finished in nine hours. Theoretically, nothing has
changed on the system except the bouncing of the database to change optimizer mode
and a different set of data (no massive change in amount of data).

Any ideas of why this load is so erratic in the length of time it takes? We can't seem to
get our arms around it. What should we be checking

Thanks in advance, Received on Wed Jul 12 2000 - 07:14:37 CDT