April 15, 2010

Predicate (again)

I often make a fuss about making sure that people include the predicate section when looking at execution plans. Here’s another example demonstrating why it can be so helpful. We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):

You will notice from the names of the bind variables (SYS_B_n)that this is a system running with the cursor_sharing parameter set to force or similar. This suggests one obvious check – when cursor_sharing is set to similar, the optimizer will re-optimize statements where the bind variables reference columns that are: used in range-based predicates (not here), or partition key columns (not in this system) or have histograms on them. So I checked for a histogram on the tmfg.tcf_mpf_file_group_id column – and there wasn’t one.

Next step of investigation – pull the plan from memory and have a quick look at it, paying special attention to the predicate section:

Notice the appearance of an extra predicate on fgc.tcp_mpf_file_group_id. This has appeared through transitive closure. (You might also notice that the join condition between tmfg and fgc has disappeared, and in some cases this would result in a cartesian merge join but in this case the first table is known to return exactly one row through a unique index).

So transitive closure has cloned a predicate from a column that didn’t have a histogram to a column that did have a histogram which means the rule for cursor_sharing= similar comes into play and Oracle has been re-optimising this statement every time a new value appears for that bind variable. But you can’t see the threat when you look at the statement, you can only see it in the predicate section of the execution plan.

Related

“We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):”

you said there is many child cursors and they have same plan_hash_value who could be this possible ?

> you said there is many child cursors and they have same plan_hash_value who could be this possible

Timur,

as I understand the question I think it is more about the relation between multiple child cursors and the same execution plan – which is something quite usual, since e.g. CURSOR_SHARING=SIMILAR creates a new child cursor under certain circumstances (as outlined by Jonathan) that gets then optimized again. It may very well happen that this repeated optimization leads to exactly the same execution plan / PLAN_HASH_VALUE.

I guess Henish is under the impression that different child cursors would only be created if the optimization lead to different plans, but this is not the case with CURSOR_SHARING=SIMILAR. It is the other way round – a new child cursor is created (for different input values) and gets then optimized. Whether the resulting plan will be different or not doesn’t matter.

Adaptive Cursor Sharing is different in this regard – it tries to minimize the number of different child cursors, something that didn’t work very well in the initial 11.1.0.6 release, but has been improved in 11.1.0.7. May be this is more what Henish is thinking of.

[…] Cartesian joins are not automatically a sign of problems. Previous blog articles have demonstrated that a Cartesian join may be more efficient than other methods for certain situations (see Improving Performance by Using a Cartesian Join and Improving Performance by Using a Cartesian Join 2), the execution plan may simply display a Cartesian join rather than a nested loops join when one of the row sources is expected to return a single row, or the optimizer may introduce a Cartesian join as a transformation due of transitive closure (see this article). […]