Disabling Oracle Cartesian Merge Joins

Question:  :  I have heard that changing the hidden parameters _optimizer_mjc_enabled and _optimizer_cartesian_enabled will disallow Cartesian merge joins, beneficial to performance?   

Is it safe to change _optimizer_mjc_enabled=false and _optimizer_cartesian_enabled =false?

Answer:  It's better to find out why the optimizer is choosing a cartesian join and fix the root cause of the problem, but there are reports of unnecessary Cartesian merge joins in 10g and 11g, caused by optimizer glitches in estimating cardinality.

Disabling Cartesian joins in Oracle 10g r1 only

Oracle 10g saw some shops suffering from unnecessary Cartesian joins.  The hidden parameter _optimizer_mjc_enabled stands for "merge join cartesian" and _optimizer_mjc_enabled was first introduced in Oracle 10g to disable cartesian join operations within SQL execution plans, with an alter system or alter session statement, like this:

alter session set "_optimizer_mjc_enabled"=false ;

Disabling Cartesian joins in Oracle 10g r2 and beyond

Oracle 10g release 2 also saw the introduction of the _optimizer_cartesian_enabled parameter. Internally, setting _optimizer_cartesian_enabled=false will cause the optimizer to disallow cartesian joins and produce a nested loops or hash join in lieu of a merge join cartesian (MJC) execution plan.

alter session set "_optimizer_mjc_enabled"=false ;
alter session set "_optimizer_cartesian_enabled "=false ;

However, these parameters are undocumented and hidden for a good reason.

Changing any hidden parameter can invalidate your support agreement unless it is done with the express consent of Oracle technical support.

MOSC Note ( 549895.1) gives details on setting _optimizer_cartesian_enabled=false. 

Also see more details on disabling cartesian in 10g and beyond in MOSC note 457058.1.  

Note:  For SAP systems, OSS recommends SAP setting _optimizer_mjc_enabled = false (See OSS Note: 16754)

More information on the cause of Cartesian Merge Joins is available HERE.

Tip:  For testing for unnecessary Cartesian Merge Joins, try adding the RULE hint to your SQL.  If you have a problem with an unnecessary Cartesian join, the RULE hint will cause the explain plan to replace the cartesian merge join with a nested loops or hash join.