Archives of the TeradataForum

Message Posted: Thu, 05 Jan 2006 @ 15:53:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

OK - Now this is driving me absolutely nuts. Can anyone give me a pointer of where to look next?

Simple scenario - 4 tables, a typical SELECT, a handful of columns in the joins between them, and it's a really poorly performing query anyway. However, I run it as DBC, I run it as myself(personal readonly user), I run it as the actual production user, and it runs in 4.5-6 minutes every time, via Queryman.

However, when this query is run by the same production user, but executed by DataStage, using an API stage (not 100% sure of the access method here), we see this query run in 4-6 minutes maybe 3 times out of 5, but the other times, it runs in SEVERAL (5-6) HOURS.

I have seen two differences in the explain/plan as shown in PMON.

My queryman explain, while running, goes after table A, then to table B, then table C, then table D. It also shows an END TRANSACTION to all amps.

The production user, while having a "long run", goes after table A, then to table B, then to table D, then to table C. It does not clearly show an END TRANSACTION to all amps. This different order in table joins causes a 2nd redistribution, whereas the other explain only has one redistribution. Now, when the query runs "fine", and returns in the same time as my Queryman execution, it goes after the same tables, in the same order as the Queryman execution, but still does not include the END TRANSACTION.

I would like to rework the query, and look at potentially a join index, but RIGHT NOW, that's not a viable option, and I can live with the 5 minute query, as long as it stays at 5 minutes.

Does anyone have any ideas on why two different users, with different access methods would receive differing explains - and sporadically, on the exact same statement, on the exact same dataset, with no changes to anything else?

Thanks!

Alistair MacGowan
Senior Database Administrator

	<Prev	Next>		<<First	<Prev	Next>	Last>>