Jobs Running Unusually long time after large load
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
Jobs Running Unusually long time after large load
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.
Would be worth running the whole situation by your DBA if you haven't already, see what they think might be going on.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Jobs Running Unusually long time after large load
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
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
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...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 ...
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
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.
Would be worth running the whole situation by your DBA if you haven't already, see what they think might be going on.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am
Thank you for repeating. I hope to renew my premium membership soon, as i do find it very useful.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.
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.
-
- Premium Member
- Posts: 129
- Joined: Thu Mar 02, 2006 8:28 am