Oracle Database Tips by Donald Burleson
Oracle has many hidden parameters
which should only be used at the direction of Oracle Technical
Support, but under the right circumstances there can be a "silver
bullet", profoundly effecting system-wide performance.
One of these undocumented
parameters is _optimizer_cost_based_transformation, an
important parameter for influencing the cost-based SQL
optimizer, a parameter that enables
Many shops are experiencing issues
after a 10g upgrade and for full details, see my notes on
10g upgrade performance issues.
What is a cost-based
Oracle author and SQL
Kim Floss notes that some common Oracle cost-based
transformations include the all-important materialized views as
well as select-join, group-by, and distinct-view
OR expansion?converts a query with ORs in the
WHERE-clause into a UNION ALL of several queries without ORs
Materialized view rewrite
Conversion of set operator intersect into join
and minus into anti-join
Issues in 10g cost based
The web is littered with end-user
reports of sporadic performance issues after a 10g upgrade, some
relating to sub-optimal SQL execution plans. For full
details, see my notes on
10g upgrade performance issues.
Oracle bug 4648181, notes two
possible workarounds as per MOSC note 4380992.8 to set
_optimizer_cost_based_transformation = off or set _optimizer_push_pred_cost_based
Oracle Freelists has this great note where we see a bug:
"Even since our upgrade from 10.2.0.2, we have
started to hit bug#4686006." . . . An alternate workaround mentioned in the
MOSC doc#392551.1 is to unset the
I can understand that by
turning it off I would be relying on heuristics for SQL transformations i.e.
view merging, predicate push or un-nesting etc. But, by setting it at the
system level, wouldn't that leave out a lot of good stuff that would have
come to use because of the cost based SQL transformations."
This blog notes an action
on Oracle Bug 3723007 - Predicate push down may not occur in 10g.
"There is a patch for this bug, but we wanted
a quick workaround that we could apply immediately. There is a workaround
that involves changing the parameter _optimizer_cost_based_transformation
Now traditionally you are not supposed to go mucking around with
underscore parameter but as oracle support were giving the go ahead we were
not too worried. So we actually wanted this to just come into effect for the
user that performs the whois queries so we decided to put a logon trigger
for that user:
SQL> CREATE OR REPLACE TRIGGER
after logon on USER.schema
'alter session set "_optimizer_cost_based_transformation"=off';
We now have this running and the whois queries are taking around two orders
of magnitude less database resources."
document notes other 10g upgrade issues with advanced SQL optimization in
subquery un-nesting and outer hash joins:
"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. . .
The only ways to get rid of this problem
(ora-979 is omitted 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."