Transformation and Optimisation

One of the biggest changes in the optimizer in 10g is the arrival of cost-based query transformation.

In earlier versions of Oracle, it was possible to distinguish very clearly between “transformation” and “optimisation”. The transformation engine would accept your SQL, and follow a set of rules to produce a restuctured statement that was logically equivalent but easier for the optimisation engine to handle.

For a vivid demonstration of this distinction, consider the query:

select
	t1.id, max_small
from
	t1,
	(
		select
			id_p, max(small_num_c) max_small
		from	t2
		group by
			id_p
	)	v2
where
	v2.id_p = t1.id
and	t1.small_num_p between 10 and 20
;

If I run this query (with my particular data set, indexes, and constraints) under the rule based optimizer in 8i, I see the following plan reported from autotrace:

Execution Plan (8.1.7.4)
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   NESTED LOOPS
   2    1     VIEW
   3    2       SORT (GROUP BY)
   4    3         TABLE ACCESS (FULL) OF 'T2'
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   6    5       INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)

Note how the plan shows Oracle performing a sort group by on table t2, using the result to do a nested loop into t1. But here’s the execution plan from 9i:

Execution Plan (9.2.0. 
———————————————————-
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF ‘T2′
   4    2       TABLE ACCESS (BY INDEX ROWID) OF ‘T1′
   5    4         INDEX (UNIQUE SCAN) OF ‘T1_PK’ (UNIQUE)

The plan is different even though the rule-based optimizer has not changed since 7.3.4 !

The transformation engine has used complex view merging to restructure the query before passing it to the (rule-based) optimisation engine. “Transformation” and “optimisation” are two different tasks.

Subquery unnesting and complex view merging are examples of query transformation. If you aren’t aware of this, you may find cases where your attempts to hint the optimizer into a particular execution path seem to go wrong because Oracle has transformed your SQL in ways that make your hints  irrelevant, invalid, or inappropriate.

In 10g, then, things have changed. The optimisation engine now has a ‘feedback loop’ to the transformation engine, which can result in many more strategies being investigated by the optimizer before a final plan is chosen.

Using the cost-based optimizer, the 10053 trace file for Oracle 9i shows just one “General Plan” for my demonstration query - which merged the in-line view, getting to the same plan as the rule-based optimizer.

In 10g, the 10053 trace file shows four general plans as it works through the costs for executing the query with, and without, complex view merging.

In principle, this is a good thing - especially for DSS and data warehouse systems - the benefit of a particular transformation can be highly dependend on the actual data distributions. The penalty, of course, is that 10g may spend more time optimising even fairly simple queries. If you have a badly written OLTP application that generates lots of “literal string” SQL, then the extra work done in optimisation may be enough to  cause performance problems - visible as excess CPU and increased latch contention on the library cache and shared pool latches.