An update on this -
Noticed that the query was running very fast on the test system which is a clone of the production. The only difference between the test system and the production system is, the tables in the production system were analyzed last weekend. So, as a first try, I deleted the statistics on the tables referred to in the query and boom! the sql ran very fast in production too! The report which was running for the past 2 days finished in a matter of minutes! Now.. how can this be explained ?? I then tried it several times on test too. Everytime I analyze the tables, the query runs slow (uses cartesian joins). I delete statistics and the query runs fast. Do you think we might be having some unnecessary indexes on the tables?