Oracle Scratchpad

March 8, 2007

Transformation and Optimisation

Filed under: CBO,Performance,Tuning — Jonathan Lewis @ 1:33 pm GMT Mar 8,2007

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:

rem     Script:         merge_groupby.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2004

select, max_small 
			id_p, max(small_num_c) max_small 
		from	t2 
		group by 
	)	v2 
	v2.id_p = 
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 ( 
   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 ( 
   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.


  1. Jonathan,

    I was wondering if you have investigated whether query transformation is enabled in CTAS (Create Table As Select). A while back I was reading about the bug 4768040 which states:
    “A nested SQL statement with correlated subquery can unnest the subquery, but when the same SQL is used in a CTAS, it cannot”. Its an enhancement which they say will be there in 11.1

    I did a test in and and believe that in Oracle CBO is doing query transformation even in CTAS, while in it does not. I created a test case very similar to what the Bug mentions. However, I don’t think the bug fixes for mention this ..

    In I see these lines while doing a 10053 trace (for CTAS):
    3844 SU: Checking validity of unnesting subquery SEL$BE5C8E5F (#0)
    3845 SU: Validity checks failed.
    While in, I see the following:
    427 SU: Considering subquery unnesting in query block SEL$1 (#1)
    428 SU: Performing unnesting that does not require costing.
    429 SU: Considering subquery unnest on SEL$1 (#1).
    430 SU: Checking validity of unnesting subquery SEL$BE5C8E5F (#2)
    431 SU: Passed validity checks, but requires costing.

    … Sorry for the long comment. I was wondering if you have done any tests related to this?


    Comment by Anurag Varma — March 8, 2007 @ 3:14 pm GMT Mar 8,2007 | Reply

  2. … adding to the comment above. I also seem to be seeing this behavior (Query transformation disabled) when doing a “INSERT into as select” in So I was suspecting that Query transformation is not only disabled in CTAS, but also in insert into as select…


    Comment by Anurag Varma — March 8, 2007 @ 3:31 pm GMT Mar 8,2007 | Reply

  3. […] Is Jonathan Lewis perpetuating a myth that Oracle 10g will unnest subqueries based on cost?! […]

    Pingback by 10g Subquery Unnesting Anomalies : Ardent Performance Computing — July 18, 2007 @ 10:30 am BST Jul 18,2007 | Reply

  4. Hi Jonathan,

    I have seen a peculiar issue. Infact, I will say that I have not seen this but have heard from one of my fellow dba. One of the query executed on fetches the data from 9206 using db link. The Query executed on when goes to 9206, the query text changes. I understand that the Query is internally rewritten only to check and understand the execution plan (comple view merging etc.) but v$sqlarea will report the original statement executed. In this case, the Query X changes to Y. The query is similar to the pasted below :

    Query Executed from

    select e.empno, e.ename, d.dname, e.sal
    from emp@o9i e, dept@o9i d
    where e.deptno = d.deptno
    and e.sal = (select max(ep.sal) from emp@o9i ep where ep.deptno = d.deptno)

    Query seen on 9206

    select a1.empno, a1.ename, a3.dname, a1.sal
    from (select max(sal) sal, deptno from emp group by deptno) a2,
    emp a1, dept a3
    where a1.deptno = a3.deptno
    and a1.sal = a2.sal
    and a3.deptno = a2.deptno

    Both the query fetch the same output, but since, the emp table is huge, the second query takes time to execute. Can Oracle Optimizer rewrite the query and change the entire text ?

    I tried to simulate this and could not find this to be true. The alias name etc. are changed as a1, a2 etc when the query is executed from 10g and passed on to 9i.


    Comment by viveshar — August 25, 2007 @ 4:50 pm BST Aug 25,2007 | Reply

  5. Viveshar, I haven’t seen an example exactly like this but I am prepared to believe that it might have happened.

    The modified query looks like an option that the 10g optimizer would have investigated as a viable rewrite of the original query; so in principle I see no reason why it shouldn’t end up as the query sent to the 9i instance.

    Comment by Jonathan Lewis — August 27, 2007 @ 9:03 pm BST Aug 27,2007 | Reply

  6. Hello all,

    Those who will to stay in 9.2 for a long time :-( ; anybody has noticed if Oracle will at some point backport some cbqt funcionality to (plead for costing unnesting at least)

    Comment by Alvaro — September 17, 2007 @ 9:05 pm BST Sep 17,2007 | Reply

  7. […] minimise the risk of Oracle doing something “clever” to spoil my plan. 10g introduces cost-based query transformation – and has far more options for unwrapping any clever tricks you introduce to SQL; so if […]

    Pingback by Manual Optimisation « Oracle Scratchpad — May 2, 2008 @ 11:44 am BST May 2,2008 | Reply

  8. […] Filed under: CBO — Jonathan Lewis @ 9:05 pm UTC Dec 21,2008 In an earlier article on Cost Based Query Transformation, I showed an example of “complex view merging” where the optimizer turned an in-line […]

    Pingback by Group By « Oracle Scratchpad — December 21, 2008 @ 9:06 pm GMT Dec 21,2008 | Reply

  9. […] Articles: Complex View Merging Subquery Factoring Pushing Predicates (1) Pushing Predicates […]

    Pingback by Subquery Factoring (4) « Oracle Scratchpad — September 13, 2010 @ 6:40 pm BST Sep 13,2010 | Reply

  10. […] much into these numbers – but one of the side effects of an upgrade to 10g is the impact of “Cost Based Query Transformation” and the clever (sometimes too clever) things it does to execution plans; and three of the top four […]

    Pingback by Upgrades « Oracle Scratchpad — October 25, 2012 @ 10:27 pm BST Oct 25,2012 | Reply

  11. […] included a couple of hints to make sure that the optimizer doesn’t try complex view merging (with subsequent group by placement), and I’ve enabled stats collections. Here’s the […]

    Pingback by Bloom Filter | Oracle Scratchpad — August 5, 2013 @ 9:22 pm BST Aug 5,2013 | Reply

  12. […] a worked example it shouldn’t be too hard to appreciate the idea that the optimizer might use complex view merging to postpone a group by until after a join – so increasing the estimate for a group by might […]

    Pingback by opt_estimate 3 | Oracle Scratchpad — June 28, 2019 @ 1:12 pm BST Jun 28,2019 | Reply

  13. […] affect an execution plan where the optimizer still obeyed the /*+ rule */ hint, and there’s an old post on this blog which points out that transformation and optimisation are (or were, at the time) independent of […]

    Pingback by Distributed Query | Oracle Scratchpad — August 23, 2021 @ 5:24 pm BST Aug 23,2021 | Reply

  14. […] so maybe the performance problem is a result of the optimizer suddenly deciding it can do complex view merging with this inline view, and perhaps all we need to do is add the hint /*+ no_merge */ to the inline […]

    Pingback by Case Study | Oracle Scratchpad — June 17, 2022 @ 3:00 pm BST Jun 17,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: