Oracle Performance and Engineering

As mentioned in this Oracle optimizer white paper - Oracle can 'transform a query' (rewrite it before execution to give same logical results and better performance) in two ways -
(a) heuristic SQL transformation - Oracle follows a set of rules to do so.
(b) cost-based SQL transformation - Oracle costs the query and data structures to decide whether it will transform or not.

Before 10g (the paper was written in 9i times) - the following transformations were part of the heuristic ones -

• simple view merging
• complex view merging
• subquery 'flattening'
• transitive predicate generation
• common subexpression elimination
• predicate pushdown and pullup
• group pruning for "cube" queries
• outer-join inner join conversion

For things like 'subquery flattening' (i.e., replacing the 'FILTER' operation in EXISTS with a 'SEMI JOIN'; or 'unnest' it) -- following a simple set of rules can just not be adequate enough for arriving at the best possible plan. Thus, Oracle 10g has started 'costing' the 'complex view merging'; predicate push and subquery flattening (or, unnesting). This is turned on by the undocumented parameter "_OPTIMIZER_COST_BASED_TRANSFORMATION" (value = linear, by default).

I have noticed after upgrading from 9i to 10g, the biggest impacts for query performance were -

(1) subquery unnesting (mostly, unnesting EXISTS to HASH JOIN SEMI), and
(2)right outer hash join (smaller table in an outer join *is* used as the hash table, unlike what was happening in pre-10g versions).

In some cases (till 10.1.0.4) we would have to hint the sub-query with NO_UNNEST to avoid the hash join semi.

Of course, I did not want to turn the parameter off systemwide, because - as usual - some other queries were benefitted from costed unnesting. I am yet to do sufficient analysis but all the queries that performed much worse after costed unnesting had combination of complex views in the sub-query.

I also discovered a unique bug related to view merging that definitely is reproducible across all 9i versions, but may be suppressed on 10g because of costed query transformation.

DROP TABLE testmerge;

CREATE TABLE testmerge (a NUMBER, b NUMBER);

INSERT INTO testmerge VALUES (2,3);

SELECT a,b FROM testmerge GROUP BY b;

ora-979 not a group by expression

SELECT COUNT(*) FROM
(SELECT a, b FROM testmerge
GROUP BY b);

COUNT(*)
----------
1

The onlyways to get rid of this problem (ora-979 is ommited if it is raised in inline view) is to use the hint NO_MERGE in 9i, and depending on 10g version and testmerge table columns - setting the '_OPTIMIZER_COST_BASED_TRANSFORMATION' to off on 10g.

Queries that correctly return the ORA-979 -

SELECT COUNT(*) FROM
(SELECT /*+ no_merge */ a, b FROM testmerge
GROUP BY b);

SELECT COUNT(*) FROM
(SELECT a, b FROM testmerge WHERE ROWNUM > 0
GROUP BY b);


In the first case, I used NO_MERGE hint in the inline view so it does not get merged, and in second query I added a ROWNUM > 0" trick so Oracle does not merge the view (very useful technique when you do not want your view to be merged, rather evaluated before the rest of the query).