We have obseved this tricky situation yesterday where an INSERT/SELECT submitted by a batch user was stuck on a product join step with PJI reaching 200! The query wasnt going anywhere so it was aborted. When the same query was submitted with my personal user, it finished in 20seconds. We noticed that the explain plan never showed a product join step when run from my user (as well as another user from someone else) however, it was getting stuck on a product join step from that batch user. I checked the session settings and both mine and the batch user was running in Teradata mode. Can someone please explain why is this happening and what exactly can cause this situation?
I was told that this query was submitted from an application, can connection settings from an application cause the optimizer to chose a different plan? if yes, can someone tell what settings can make this influence to the optimizer?
I've seen something like this before. In that situation it wasn't any difference between the two users etc., it was because there were no stats on one/more of the (source) tables involved in the processing and the table was skewed.
With no stats the optimizer will use random amp sample and the optimizer chooses an AMP with (relatively) few rows, therefore the chosen plan involved a product join. Turns out this was really bad :-)
When we ran the query again, because it was a different session (could have been the same user) the optimizer chose a different AMP, which had a more reasonable number of rows, thus the table is assumed to be larger and a different plan (with no product join) is built.
Collecting stats on the relevant table (even simple summary stats) solved our problem.
Look at the two plans and see the estimated row counts from the source tables. For one/more of those tables you may find a big difference between the two plans.
When you see an unexpected product join it is most likely caused by a missing predicate. If you can see the actual SQL that was submitted, verify that all the tables being joined have a corresponding A.X=B.X [and A.Y=B.Y and ...] in the where-clause or on-clause, and that there are enough such qualifiers to match the logical key.
If the two queries are truly "the same sql" then it isn't a case of a missing join.
Your comment did prompt the thought that if this is coming from two different users are the default databases the same? if no then there might be a difference in the views that are being used - that might generate a different plan.