Oracle query that use to run in less than a second now takes 8 minutes
Justin Cave, I think you are right. It's using something other than the explain plan.

I added the hint /*+ LEADING (table_alias, table_alias) */ referencing the first two tables in the explain plan and boom, it's running like lightning again.