Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








_optimizer_cost_based_transformation tips

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 cost-based transformations


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 transformation?


Oracle author and SQL tuning expert 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 merging.

  • OR expansion?converts a query with ORs in the WHERE-clause into a UNION ALL of several queries without ORs

  • Materialized view rewrite

  • Star transformation

  • Conversion of set operator intersect into join and minus into anti-join


Issues in 10g cost based transformations


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 = false.


Oracle Freelists has this great note where we see a bug:

"Even since our upgrade from, we have started to hit bug#4686006." . . . An alternate workaround mentioned in the MOSC doc#392551.1 is to unset the _optimizer_cost_based_transformation parameter.


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 to off.


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:

after logon on USER.schema
execute immediate
'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."

This 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 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."





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.