RSS Feed

Method R Discussion Group

Anyone notice Oracle really wanting to do a lot of cartesian merge joins in 10? We upgraded a version 9 app, had some statements behaving badly, and it turned out that they were doing cartesian merge joins. A quick query against the v$sql_plan table showed thousands of statements doing them (most with no major ill effects). Bad cardinality estimates could account for some of these but not all. (i.e. if Oracle thought it would only get one row back, why not do a cartesian) However, this turned out to not always be the case. Several of the statements expected 100s of rows and in some cases 1000's of rows. There is also a new hidden parameter as of R2 (_optimizer_cartesian_enabled) which is set to TRUE by default. This is supposed to allow you to disable the cartesian merge joins if so desired, and it seems to actually work except for cases where there actually is a missing join condition and a few special cases - like operations that are guaranteed to return no rows (i.e. where 1=2). So my thought is that the optimizer got a little cartesian happy in 10 and Oracle added the new hidden parameter so we could turn it off until they got around to making the optimizer smart enough to use it with a little more restraint.

So my questions are as follows:

Has anyone seen similar behavior?
Has anyone used the "_optimizer_cartesian_enabled" hidden parameter in a production system?
Has anyone run into any negative affects from setting that parameter?
Does anyone know of another way to disable this optimization technique?

Yes, I've seen this behavior but I haven't used or seen production use of the "_optimizer_cartesian_enabled" parameter and would be a little wary of using it without alot of testing.  But, typically what is happening with these plans you're seeing is that when a query has a predicate like this: 
WHERE tab1.col1 = 100
AND tab2.col1 = tabl1.col1,
the optimizer actually removes the join predicate and instead uses transitivity to infer a predicate like this:
WHERE tab1.col1 = 100
AND tab2.col1 = 100. 
This happens when the optimizer estimates the rows returned by separating the two predicates can be retrieved with less work than joining the two tables, so it will choose this method.

Setting the parameter to FALSE should curtail this behavior, but as I already mentioned, I do not know the full effects of doing so.  However, I do know there is another hidden parameter in 10g called "_optimizer_transitivity_retain" which is used to tell the optimizer to "retain equi-join predicates upon transitive equality predicate generation" (this is set to TRUE by default).  Or in other words - don't drop the join predicates!  This parameter (at it's default of TRUE) should help offset the "_optimizer_cartesian_enabled=TRUE" setting and cause the optimizer to retain the join predicates and give you a HASH or NESTED LOOP join instead.  If the optimizer is not choosing to retain the join predicates in the application you're working with, it is very likely that the join cardinality calculations are simply higher than the transitively generated predicate selectivities. 

I'd turn my eye towards the statistics that are in place to see if either increasing the estimate_percent or adjusting some other statistics collection option might help curb the problem.  What I've seen is that by adjusting statistics when cartesians are showing up frequently, I have better luck with getting the optimizer to choose the plans I prefer.  I'd check the stats collection METHOD_OPT and ESTIMATE_PERCENT parameters in particular.  If they are using Oracle defaults (SIZE AUTO and AUTO_SAMPLE_SIZE), I'd look into explicitly setting those and running some more tests to see if better plans result.

One other thing that I've seen be effective is when a cartesian plan is chosen for a 3+ table join with a predicate like this:
WHERE tab1.col1 = 100
AND tab1.col1 = tab2.col1
AND tab2.col1 = tab3.col1. 
The plan chosen for this predicate is almost always a cartesian.  However, I've found that by adding "AND tab1.col1 = tab3.col1" (to "close the loop" so to speak) that the optimizer will revert to a HASH or NESTED LOOPS join and not drop predicates due to transitive closure as frequently.  Of course this option requires code changes that may not be possible, but is does seem to be effective in most cases.

Yes, I have seen 10g go little cartesion join happy too.
I have found many of these plans to be resulting from bad optimizer stats (some that I have found need to be locked).
Overall I have found this "improvement" to be problematic.
SAP recommends setting _optimizer_mjc_enabled = false (OSS Note 16754) to work around these "improvements". 
Metalink documents this hidden parm in Note 457058.1

I like this parm ;-)