Hi,
Using oracle 11.2.0.3
We have seen a sql which usually takes under 1 minute and runs every day for years suddenly take 11 hrs to run
data volumes similar to usual and database workload likewise - other sqls o.k.
When try run explain plan can see merge join cartesian and also looks to be plan used when database was running.
Even if put below in a window
alter session set "_optimizer_cartesian_enabled"=FALSE;
alter session set "_optimizer_mjc_enabled" = FALSE;
and then run explain plan on sql still looks to use merge join cartesian.
What would be recommendations to avoid merge join cartesian being used?
Thanks