DSXchange

I'm running a set of jobs in a sequence (Datastage 8.1, Sun Solaris, Oracle 10g). There are 4 jobs that run concurrently, where all four jobs have an oracle Enterprise stage as both the source and the target, with only a copy stage in the middle and the target uses truncate/load. Normally, these jobs run quickly, but twice a month, the sequence that executes them has a conditional branch that loads a large (33 million rows) table. What appears to happen is that on days when the large table doesn't load, the other 4 jobs complete in about 15 minutes. However, on days when the large table loads, the 4 jobs take 2 hours plus with the same volume of data! The large load completes before the other 4 tables kick off, so it should not be using any resources in either the ETL server or the database. Any idea what is happening?

First thing that jumps to mind is a stats / histogram issue. The day of the load the statistics may be out of whack and it isn't until later (in the night?) when the tables/indexes get analyzed that things get straightened back out. However, if your other loads don't specifically join to or otherwise involve the 'large table' somehow, then that thought is pretty much out the window.

Would be worth running the whole situation by your DBA if you haven't already, see what they think might be going on.

If you are using the big table in the 4 jobs , after the table is loaded you can do collect stats on the table before starting the jobs

chulett wrote:First thing that jumps to mind is a stats / histogram issue. The day of the load the statistics may be out of whack and it isn't until later (in the night?) when the tables/indexes get analyzed that t ...

I should clarify: the four jobs load four OTHER tables aside from the large one. The jobs really aren't related other than the tables are in the same Oracle database. They aren't even in the same schema...

Something I asked / noted in my reply.

chulett wrote:Something I asked / noted in my reply.

Sorry, I didn't see that part as my Premium membership has expired and I'm not in a position to renew it right now.

Ah. Then let me repeat this last little bit as I doubt this is anything DataStage and will fall squarely on Oracle's shoulders:

Would be worth running the whole situation by your DBA if you haven't already, see what they think might be going on.

chulett wrote:Ah. Then let me repeat this last little bit as I doubt this is anything DataStage and will fall squarely on Oracle's shoulders:

Would be worth running the whole situation by your DBA if you haven't already, see what they think might be going on.

Thank you for repeating. I hope to renew my premium membership soon, as i do find it very useful.

Unfortuantely, I have run this by my DBA's and they claim nothing unusual is going on with the database. I've talked to the people monitoring the server as well. I am truly at a loss to explain why this is happening. This started after we upgraded to 8.1. We had been running 8.0.1 previously and never saw this issue.

OK, with that I would suggest taking it to your official support provider. Perhaps this is a known change in behaviour with a known resolution or patch.

chulett wrote:OK, with that I would suggest taking it to your official support provider. Perhaps this is a known change in behaviour with a known resolution or patch.

Yes, that is my next step. Just thought I'd check here first. Thanks!

Sure, you never know what people have or haven't seen before, so it's always worth a shot.