I’ve offered up “first child first, recursive descent” as a basic strategy for interpreting execution plans – but it’s not a perfect rule, and it can be easy to lose track of what’s going on even when the basic “first child first” is being obeyed. In this article we’ll be looking at a couple of examples where we will still be using “first child first” some of the time (though slightly camouflaged), an example of a bug which makes the rule look wrong, and one example where “first child first” doesn’t apply. There are several different cases, in fact, where the rule doesn’t apply, but we’ll have to wait for part 6 to see more of them.

Subquery Update

The examples I’ll cover in this article are: updates, scalar subqueries in the select list, and subquery factoring. To cover as many examples as possible I’ll supply just a sample statement with plan and make a few comments; I won’t be supplying the full code to reproduce the tables and data.

The first example is an update with subquery; partly because DML plans rarely appear in the literature of execution plans and partly because it will be useful to contrast it with my second example. Here’s my sample statement:

Looking at the query there are three “intuitively obvious” steps. First we find the rows to update then, for each row, we run the t2 subquery followed by the t3 subquery: and that’s exactly what we see in the plan.

As you can see, this plan follows “first child first” exactly. The update operation at line 1 has three children: lines 2, 3 and 6. The first child is the index range scan that lets us find the rowids of the rows to be updated – the second child produces the subplan (lines 3 – 5) that represents the subquery updating column n1, the third child produces the subplan (lines 6 – 8) that represents the subquery updating column n2.

There is a little oddity with the cost of this plan (see also: http://jonathanlewis.wordpress.com/2014/05/02/costing-bug/) – the total cost of 610 seems to come from summing the cost of executing the two subqueries 101 times (which is reasonable) then adding the cost of visiting the table 101 times for each subquery (which is not reasonable).

Scalar Subqueries

For my second example I’m going to take this statement and turn it into a query that shows how the data would be changed by the update. All I have to do is take each of the subqueries from the update and write it as an inline scalar subquery in the select list. In this example we see the “first child first” rule coming close to being turned upside down:

The plan tells use that the select statement at line 0 has three child rows (lines 1, 4, and 7); but when we compare our intuitive understanding of what must happen with the order of the child operations we see that it’s the last child that represents the starting point of the driving query. When you have scalar subqueries in the select list then the last child in the plan is the first child to be called, and the other children, which represent the scalar subqueries, are called subsequently (in order from first to last but one).

Again there’s an odd detail in the costing of the query – at no point does the optimizer attempt to calculate a cost for the entire query; all it’s given you is the cost of running the three main parts of the query once each even though, in principle, it has information that tells it that the two scalar subqueries might be run 101 times each.

If you run this test on 12c you might see the optimizer using a new “scalar subquery” optimization that transforms the two scalar subqueries into outer joins – and if this happens the costing will be correct.

Presentation bug

Inevitably it is always possible to write increasingly complex SQL, and it takes just a tiny extra effort to hit a display error relating to scalar subqueries – what if we wanted to use data from table t2 to update some rows in t1, and data from table t3 to update others? We might use a decode() statement to switch between scalar subqueries. Here’s a query (rather than an update) demonstrating the principle and the associated bug:

As you can see in the query, if n1 is a multiple of 4 we return a value from t2, if not we return a result from t3. Looking at the query it seems quite reasonable to believe that the two subqueries are in some way “on a par” with each other but when we look at the execution plan this doesn’t seem to be the case.

Of course, we start by noting that the driving query appears as the last child of the select statement, but then we notice that there is only one other child of the select statement and, if we apply “first child first” we find that, apparently, the subquery against t3 seems to be a child of the subquery against t2. It seems as if “first child first” is telling use that we have to operate lines 4 – 6 to produce a rowsource that we can then pass up through lines 3,2, and 1.

It would actually be possible to write a query that produces a plan that looks just like the one above and requires exactly that order of work (the predicate section would look different, though) but what I’ve described is not what’s going on here; the plan is wrong. (12c uses the same plan, and makes the same error.)

I explained earlier on in this series that Oracle calculates a depth value for each line of a plan, and we can select that column from the plan table (or equivalent dynamic performance views) to produce the indentation for execution plans, and I pointed out that sometimes the optimizer calculates the wrong value for this depth. This example is one of those cases, and we might need to write our own code (using a connect by query on the parent_id and id columns of the plan table) to get the right shape to the plan.

Rather than writing that query to show you what the plan should look like, though, I’ve taken an easier route. I’ve simply executed the query with sql_trace enabled and used tkprof on the resulting trace file. Here’s the result (with a little cosmetic editing – I’ve only run the query once so I’ve cut out the Rows(avg) and Rows(max) columns that were added to the tkprof output in 11g, and I’ve also removed references to the object_id from the ends of lines):

As you can see from this output the two subqueries are equivalent children of the select statement, just as they were in the earlier select statement. The trace file doesn’t hold the depth information; its STAT lines only hold the id and parent_id (labeled id and pid respectively in the raw trace file), so tkprof has to derive the depth, which gives us the right shape for the plan.

Another nice feature of the tkprof output, of course, is that we can look at the “Rows (1st)” column and see that the subquery against t2 returned a total of 25 rows while the subquery against t3 returned a total of 75. Looking back at the original query we were expecting (or hoping) that one query would run 25 times and the other 75 times, so in this case we have some corroborating evidence.

Note: we don’t actually have enough information in the output to know that this really is the case – we are jumping to a conclusion based on our understanding of the data and the query: in principle both queries could have run 100 times each, returning data 25% and 75% of the time respectively – what we really need is the “starts” statistics from the internal v$sql_plan_statistics view but that view is going to produce the wrong shape plan if we use dbms_xplan to query it – and we shouldn’t use a connect by query on the view for performance reasons – so we have to look in two places to get the right shape plan from one and the correct statistics from another.

Subquery Factoring

I pointed out that 12c was able to use a new transformation to turn scalar subqueries into joins. Let’s go back to an earlier query – the one with the two simple inline scalar subqueries – and emulate that plan in 11g. Here’s one way we might do it:

(To reduce the width of the plan output I’ve trimmed the name of the temporary table references down to sys_temp from the original sys_temp_0fd9d6611_770b8e1.)

I’ve used subquery factoring with a /*+ materialize */ hint to force Oracle into creating a pair of internal global temporary tables (GTTs) that hold the results that we will need from t2 and t3, then I’ve written the rest of the code to do an outer join from t1 to these two result sets. In fact I could have left out the hints and Oracle would have copied the “factored subqueries” inline which would have produced a similar set of outer hash joins with the two aggregate result sets being held in a workarea in the session memory. I chose the materialize option simply to show the appearance of a plan with materialized subqueries. If we cut it back to the bare minimum (remember how we can click on “minus” icons the OEM/Grid/Cloud control screens) we would see the following:

I have to say that it’s not exactly what I would like to see – I’d prefer to see line 8 (the hash join) as a child to line 0 (the select statement) – nevertheless we can see that there are three major stages the plan, all children of the temp table transformation: it’s “first child first” again – we create two temporary tables and then do a hash join. If we expand line 2 we see we’re aggregating table t2, if we expand line 5 we see we’re aggregating table t3, and if we expand line 8 we see we’re doing a pair of (outer) hash joins between t1 and (views of) two temporary tables.

Apart from the fact that the plan for the main query is pushed slightly to the right, and that there’s potentially a large number of lines in the plan before we get to the main query –as we saw in the plan for the query with scalar subqueries in the select list – plans that use materialization in their subquery factoring still follow the basic rule, and can be dissected simply by collapsing down the excess volume until you can see the starting line of each child of the first (proper) operation in the plan.

Conclusion

In part 5 we’ve looked at a few examples – basically about subqueries lurking somewhere within a larger query – to see how “first child first” plays out in more complex queries. In the cases we’ve examined we’ve found one example of a bug where blindly following the rule without cross-reference to the query and to alternative ways of generating the plan could lead us to an incorrect interpretation. We’ve also seen the special case for scalar subqueries, where the driving activity of the whole plan is the last child of the select operation.

In the next instalment we’ll see cases of queries where the optimizer’s placement of subqueries really does break “first child first” and can fool you into misinterpreting the plan completely.