The best of IOUG 2005
Helping The CBO To Make Better Decisions - The
Extensible Optimizer
Joze Senegacnik, S&T Slovenia
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>
where
operator(...) 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 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). One 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.
|
