Message-Id: <10556.111785@fatcity.com> From: Cherie_Machler@gelco.com Date: Wed, 12 Jul 2000 07:14:37 -0500 Subject: 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,