Donald Burleson Click here for more Oracle News Headlines

1.  

2. Inside Oracle SQL optimization

3. Oracle SQL tuning with column histograms

4. Oracle SQL Join Cardinality

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

select
   stuff
from
   customer
natural join
   orders
natural join
   item
natural join
   product
where
   credit_rating * extended_credit > .07
and
   (qty_in_stock * velocity) /.075 < 30
or
   (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.