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 








Oracle Extensible Optimizer Tips

Joze Senegacnik, S&T Slovenia

Also see these related notes on cardinality estimation:

The central problem with cardinality estimation is the in cases of complex WHERE clauses the optimizer does not have enough information about inter-join result set sizes to determine the optimal table join order. 

To wit, examine the where clause in the below query.  Einstein himself would have trouble figuring out the result cardinality (the number of rows returned).

natural join
natural join
natural join
   credit_rating * extended_credit > .07
   (qty_in_stock * velocity) /.075 < 30

   (sku_price / 47) * (qty_in_stock / velocity) > 47;

In one of the best presentations at IOUG, Mr. Senegacnik dove into the internals of the CBO and discovered some stunning conclusions.  He was right-on about the issue of the CBO estimating inter-join cardinality as a major problem and discussed the extensible CBO optimizer feature.

Senegacnik described the correlation between histograms and column selectivity (dynamic sampling) and showed the importance of how Oracle costing works inside the CBO for cardinality estimation.

Best of all, Senegacnik showed the "extensible optimizer" feature whereby you can manually set costs for internal CBO operations when you define your own built-in functions.

According to Senegacnik the extensible optimizer is a very cool feature for writing custom built-in functions:

With the new functionality of the extensible optimizer one can:

Associate cost functions and default costs with domain indexes, indextypes, packages, and standalone functions.

Associate selectivity functions and default selectivity with methods of object types, package functions, and standalone functions.

Associate statistics collection functions with domain indexes and columns of tables. The optimizer can collect user-defined statistics at both the partition level and the object level for a domain index or a table

Order predicates with functions based on cost or use a hint to preserve the order of evaluation for function predicates.

He also explores User-Defined Selectivity a feature where you can assign your own values:

To improve the optimizer's selectivity estimation capabilities, the extensible optimizer feature allows definition of user-defined selectivity functions for predicates containing user-defined operators, standalone functions, package functions, or type methods.

The optimizer calls the user-defined selectivity function whenever it encounters a predicate with one of the following forms: operator(...) relational_operator <constant><constant> relational_operator operator(...)operator(...) LIKE <constant>whereoperator(...) is a user-defined operator, standalone function, package function, or type method,relational_operator is one of {<, <=, =, >=, >}, and<constant> is a constant value expression or a bind variable.Users can specify their user-defined selectivity functions associated with operator(...).

When the optimizer encounters such a predicate while preparing the execution plan, it calls the user-defined selectivity function and passes the entire predicate as an argument (including the relational operator, function, or type method and its arguments, the relational operator relational_operator, and the constant expression or bind variable).

The return value (selectivity) is expressed as a percentage between 0 and 100 inclusive; values out of this range are ignored. If the selectivity is not known then CBO uses heuristics to estimate the selectivity.

Lastly, he covers User-Defined Costs where you can "hint" the CBO directly of costs for your user-defined PL/SQL function:

The cost model has also been extended to let the developer define costs for domain indexes, index partitions, and user-defined standalone functions, package functions, and type methods.

The costs can be defined as costs that the CBO uses by default or they can be a result of special cost functions that the CBO calls to compute the cost.

Whenever the cost information is missing, the CBO uses heuristics to compute an estimate, but this may lead to sub-optimal execution plans.

As for selectivity functions, user-defined cost functions are only called when a predicate has one of the forms already mentioned.

The cost for a single execution is defined by three cost values: CPU, I/O and NETWORK - already known from the new cost model in starting in 9i. 

  • CPU cost value is represented with the number of machine cycles executed by the function or domain index implementation. (The overhead of invoking the function is not included). You can estimate the number of machine cycles with the package function DBMS_ODCI. ESTIMATE_CPU_UNITS.

  • I/O cost value is the number of data blocks read by the function or domain index implementation.

  • NETWORK this value is currently not used but may be used later so developers are encouraged to define it. It represents the number of data blocks transmitted to the network.

  • The optimizer mode determines which cost to minimize. If the goal is FIRST_ROWS, the resource cost of returning a single row is minimized, otherwise the resource cost of returning all rows is minimized.

Overall, this was a highlight of IOUG Live! 2005, an outstanding presentation from a great presenter.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



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