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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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 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 _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:

SQL> CREATE OR REPLACE TRIGGER logon_trigger
after logon on USER.schema
begin
execute immediate
'alter session set "_optimizer_cost_based_transformation"=off';
end;
/


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

 

 


 

 

��  
 
 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational