10 Using Extensible Optimizer

Statistics

Statistics for tables and indexes can be generated by using the DBMS_STATS package. In general, the more accurate the statistics, the better the execution plan generated by the optimizer.

Selectivity

The optimizer uses statistics to calculate the selectivity of predicates. The selectivity is the fraction of rows in a table or partition that is chosen by the predicate. It is a number between 0 and 1. The selectivity of a predicate is used to estimate the cost of a particular access method; it is also used to determine the optimal join order. A poor choice of join order by the optimizer could result in a very expensive execution plan.

Currently, the optimizer uses a standard algorithm to estimate the selectivity of selection and join predicates. However, the algorithm does not always work well in cases in which predicates contain functions or type methods. In addition, predicates can contain user-defined operators about which the optimizer does not have any information. In that case the optimizer cannot compute an accurate selectivity.

operator(...) relational_operator constant
constant relational_operator operator(...)
operator(...) LIKE constant

Cost

The optimizer estimates the cost of various access paths to choose an optimal plan. For example, it computes the CPU and I/O cost of using an index and a full table scan to choose between the two. However the optimizer does not know the internal storage structure of domain indexes, and so it cannot compute a good estimate of the cost of a domain index.

User-Defined Statistics Functions

There are two user-defined statistics collection functions, one for collecting statistics and the other for deleting them.

The first, ODCIStatsCollect(), is used to collect user-defined statistics; its interface depends on whether a column or domain index is being analyzed. It is called when analyzing a column of a table or a domain index and takes two parameters:

• col for the column being analyzed, or ia for the domain index being analyzed;

• options for options specified in the DBMS_STATS package.

As mentioned, the database does not interpret statistics collected by ODCIStatsCollect(). For system-managed domain index statistics, you don't return the statistics collected by ODCIStatsCollect(). You should store these statistics in a user-managed format, as described in section "Generating Statistics for System-Managed Domain Indexes", and illustrated in Figure 10-1, Figure 10-2, and Figure 10-3.

User-collected statistics are deleted by calling the ODCIStatsDelete() function whose interface depends on whether the statistics for a column or domain index are being dropped. It takes a single parameter: col, for the column whose user-defined statistics must be deleted, or ia, for the domain index whose statistics are to be deleted.

If a user-defined ODCIStatsCollect() function is present in a statistics type, the corresponding ODCIStatsDelete() function must also be present.

The return values of the ODCIStatsCollect() and ODCIStatsDelete() functions must be Success, Error, or Warning; these return values are defined in a system package ODCIConst.

User-Defined Selectivity Functions

User-defined selectivity functions are used only for predicate forms listed in Example 10-1.

A user-defined selectivity function ODCIStatsSelectivity() takes five sets of input parameters that describe the predicate:

• The pred parameter describes the function operator and the relational operator relational_operator.

• The args parameter describes the start and stop values (that is, <constant>) of the function and the actual arguments to the function (operator()).

• The start parameter, whose data type is identical to that of the function's return value, describes the start value of the function.

• The stop parameter, whose data type is identical to that of the function's return value, describes the stop value of the function.

• A list of function arguments whose number, position, and type must match the arguments of the function operator.

The computed selectivity is returned in the output parameter sel as a number between 0 and 100 (inclusive) that represents a percentage. The optimizer ignores numbers less than 0 or greater than 100 as invalid values. If the computed selectivity is less than 0.5%, a value of 0 may be returned in the output parameter sel. A selectivity of 0 does not mean that the predicate will be removed.

The return value of the ODCIStatsSelectivity() function must be one of Success, Error, or Warning.

As an example, consider a function myFunction, as defined in Example 10-3.

myFunction (a NUMBER, b VARCHAR2(10)) return NUMBER

A user-defined selectivity function ODCIStatsSelectivity() is detailed in Chapter 21, "Extensible Optimizer Interface".

If myFunction() is called using literal arguments, such as myFunction(2, 'TEST') > 5, then the selectivity function is called as out lined in Example 10-4.

ODCIStatsSelectivity(ODCIPredInfo_constructor, sel,
   ODCIArgDescList_constructor, 5, NULL, 2, 'TEST', ODCIEnv_flag)

If, on the other hand, myFunction() is called with some non-literals arguments, such as myFunction(Test_tab.col_a, 'TEST')> 5, where col_a is a column in table Test_tab, then the selectivity function is called as outlined in Example 10-5.

ODCIStatsSelectivity(ODCIPredInfo_constructor, sel,
   ODCIArgDescList_constructor, 5, NULL, NULL, 'TEST', ODCIEnv_flag)

In summary, the start, stop, and function argument values are passed to the selectivity function only if they are literals; otherwise they are NULL. ODCIArgDescList describes all the arguments that follow it.

User-Defined Cost Functions for Functions

User-defined cost functions are only used for predicate forms listed in Example 10-1.

You can define a function, ODCIStatsFunctionCost(), for computing the cost of standalone functions, package functions, or type methods. This function takes three sets of input parameters describing the predicate:

• The func parameter describes the function operator.

• The args parameter describes the actual arguments to the function operator.

• A list of function arguments whose number, position, and type must match the arguments of the function operator.

The ODCIStatsFunctionCost() function returns its computed cost in the cost parameter. The returned cost can have two components, a CPU cost and an I/O cost, which are combined by the optimizer to compute a composite cost. The costs returned by user-defined cost functions must be positive whole numbers. Invalid values are ignored by the optimizer.

The return value of the ODCIStatsFunctionCost() function must be one of Success, Error, or Warning.

Consider a myFunction(), defined in Example 10-3.

A user-defined cost function ODCIStatsFunctionCost() is detailed in Chapter 21, "Extensible Optimizer Interface".

If myFunction() is called using literal arguments, such as myFunction(2, 'TEST') > 5, where col_a is a column in table Test_tab, then the cost function is called as out lined in Example 10-6.

ODCIStatsFunctionCost(ODCIFuncInfo_constructor, cost,
   ODCIArgDescList_constructor, 2, 'TEST', ODCIEnv_flag)

If, on the other hand, myFunction() is called with non-literal arguments, such as myFunction(Test_tab.col_a, 'TEST') > 5, where col_a is a column in table Test_tab, then the cost function is called as out lined in Example 10-7.

ODCIStatsFunctionCost(ODCIFuncInfo_constructor, cost,
   ODCIArgDescList_constructor, NULL, 'TEST', ODCIEnv_flag)

In summary, function argument values are passed to the cost function only if they are literals; otherwise, they are NULL. ODCIArgDescList describes all the arguments that follow it.

User-Defined Cost Functions for Domain Indexes

User-defined cost functions for domain indexes are used for the same type of predicates mentioned previously, except that operator must be a user-defined operator for which a valid domain index access path exists.

The ODCIStatsIndexCost() function takes these sets of parameters:

• ia describing the domain index

• sel representing the user-computed selectivity of the predicate

• cost giving the computed cost

• qi containing additional information about the query

• pred describing the predicate

• args describing the start and stop values (that is, <constant>) of the operator and the actual arguments to the operator operator

• start, whose data type is identical to that of the operator's return value, describing the start value of the operator

• stop whose data type is identical to that of the operator's return value, describing the stop value of the operator

• a list of operator value arguments whose number, position, and type must match the arguments of the operator operator. The value arguments of an operator are the arguments excluding the first argument.

• env, an environment flag set by the server to indicate which call is being made in cases where multiple calls are made to the same routine. The flag is reserved for future use; currently it is always set to 0.

The computed cost of the domain index is returned in the output parameter, cost.

ODCIStatsIndexCost() returns Success, Error or Warning.

Consider an operator defined in Example 10-8, which returns 1 or 0 depending on whether or not the string b_string is contained in the string a_string. Further, assume that the operator is implemented by a domain index.

Contains(a_string VARCHAR2(2000), b_string VARCHAR2(10))

A user-defined index cost function ODCIStatsIndexCost() is detailed in Chapter 21, "Extensible Optimizer Interface".

If contains() is called using non-literal arguments, such as Contains(Test_tab.col_c,'TEST') <= 1, then the index cost function is called as out lined in Example 10-9.

ODCIStatsIndexCost(ODCIIndexInfo_constructor, sel, cost,
   ODCIQueryInfo_constructor, ODCIPredInfo_constructor, 
   ODCIArgDescList_constructor, NULL, 1, 'TEST', ODCIEnv_flag)

Note that the first argument, a_string, of Contains does not appear as a parameter of ODCIStatsIndexCost(). This is because the first argument to an operator must be a column for the domain index to be used, and this column information is passed in through the ODCIIndexInfo parameter. Only the operator arguments after the first (the value arguments) must appear as parameters to the ODCIStatsIndexCost() function.

In summary, the start, stop, and operator argument values are passed to the index cost function only if they are literals; otherwise they are NULL. ODCIArgDescList describes all the arguments that follow it.

Generating Statistics for System-Managed Domain Indexes

If you choose the system-managed approach to maintain domain indexes and must associate a statistics type with the domain index or the indextype, then the statistics type must also be managed by the system.

Statistics may be collected when issuing an ODCIStatsCollect() call for a system-managed domain index. For a non-partitioned index, the statistics may be stored with the index storage table, as a separate table, or in a data cartridge metadata table with index name qualified rows.

For local partitioned domain indexes, there are three options for storing statistics. All use the ODCIStatsUpdPartStatistics() method during a partition maintenance operation in the following ways. Please note that in all the following examples, no DDLs are executed inside the ODCIStatsUpdPartStatistics() call, and only DML and query instructions are allowed in the implementation of ODCIStatsUpdPartStatistics().

1. The system calls the ODCIStatsUpdPartStatistics() method If the statistics are stored with the indexed data in the index storage (system-partitioned) tables, as illustrated in Figure 10-1. The method can optionally maintain any statistics-related partition metadata, or be a null operation. The server deletes or drops the statistics for the affected partitions along with the index data specific to these partitions.

   Figure 10-1 Storing Index-Specific Statistics with Index Tables

   
   Description of "Figure 10-1 Storing Index-Specific Statistics with Index Tables"
   
   

2. If the statistics are stored in separate system-partitioned tables, as illustrated in Figure 10-2, the server tracks the creation of these system partitioned tables of store statistics during an ODCIStatsCollect() call. These tables are maintained by the server in the same manner as for index storage tables.

   Figure 10-2 Storing Index-Specific Statistics in a Separate Table

   
   Description of "Figure 10-2 Storing Index-Specific Statistics in a Separate Table"
   
   

3. If the statistics are stored in a non-partitioned table as either schema-name, index-name, or partition-name qualified rows, as illustrated in Figure 10-3, then you have to maintain the partition-level statistics with a call to ODCIStatsUpdPartStatistics(). The server does not perform any operation on these tables.

   Figure 10-3 Storing Index-Partition Statistics in a Common Table

   
   Description of "Figure 10-3 Storing Index-Partition Statistics in a Common Table"
   
   

User-Defined Statistics

User-defined statistics functions are relevant for columns that use both standard SQL data types and object types, and for domain indexes. The functions ODCIStatsSelectivity(), ODCIStatsFunctionCost(), and ODCIStatsIndexCost() are not used for user-defined statistics, so statistics types used only to collect user-defined statistics need not implement these functions. The following sections describe how to collect column and index user-defined statistics.

Users could create their own tables. This approach requires that privileges on these tables be administered properly, backup and restoration of these tables be done along with other dictionary tables, and point-in-time recovery considerations be resolved.

CREATE TABLE Test_tab (
   col_a    NUMBER,
   col_b    typ1,
   col_c    VARCHAR2(2000)
)

ASSOCIATE STATISTICS WITH COLUMNS Test_tab.col_b USING stat

ASSOCIATE STATISTICS WITH TYPES typ1 USING stat_typ1

CREATE INDEXTYPE indtype
FOR userOp(NUMBER)
USING imptype WITH SYSTEM MANAGED STORAGE TABLES;

CREATE INDEX Test_indx ON Test_tab(col_a)
INDEXTYPE IS indtype PARAMETERS('example');

CREATE OPERATOR userOp BINDING (NUMBER) RETURN NUMBER
USING userOp_func;

ASSOCIATE STATISTICS WITH INDEXTYPES indtype USING stat_indtype
WITH SYSTEM MANAGED STORAGE TABLES

User-Defined Selectivity

The optimizer uses selectivity functions to compute the selectivity of predicates in a query. The predicates must have one of the appropriate forms and can contain user-defined operators, standalone functions, package functions, or type methods. The following sections describe selectivity computation for each.

ASSOCIATE STATISTICS WITH FUNCTIONS userOp_func USING stat_userOp_func

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_MyFunction

ASSOCIATE STATISTICS WITH PACKAGES Demo_pack USING stat_Demo_pack

ASSOCIATE STATISTICS WITH TYPES Example_typ USING stat_Example_typ

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT SELECTIVITY 20

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_myFunction
   DEFAULT SELECTIVITY 20

ASSOCIATE STATISTICS WITH PACKAGES Demo_pack DEFAULT SELECTIVITY 20
ASSOCIATE STATISTICS WITH TYPES Example_typ DEFAULT SELECTIVITY 20

User-Defined Cost

The optimizer uses user-defined cost functions to compute the cost of predicates in a query. The predicates must have one of the forms listed earlier and can contain user-defined operators, standalone functions, package functions, or type methods. In addition, user-defined cost functions are also used to compute the cost of domain indexes. The following sections describe cost computation for each.

ASSOCIATE STATISTICS WITH INDEXTYPES indtype USING stat_indtype
  WITH SYSTEM MANAGED STORAGE TABLES
ASSOCIATE STATISTICS WITH FUNCTIONS userOp USING stat_userOp_func

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_myFunction;

ASSOCIATE STATISTICS WITH PACKAGES Demo_pack USING stat_Demo_pack;

ASSOCIATE STATISTICS WITH TYPES Example_typ USING stat_Example_typ;

ASSOCIATE STATISTICS WITH INDEXES Test_indx DEFAULT COST (100, 5, 0);

ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx
   DEFAULT COST (100, 5, 0)

ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT COST (100, 5, 0)
ASSOCIATE STATISTICS WITH PACKAGES Demo_pack DEFAULT COST (100, 5, 0)
ASSOCIATE STATISTICS WITH TYPES Example_typ DEFAULT COST (100, 5, 0)
ASSOCIATE STATISTICS WITH INDEXTYPES indtype DEFAULT COST (100, 5, 0)

Declaring a NULL Association for an Index or Column

An association of a statistics type defined for an indextype or object type is inherited by index instances of that indextype and by columns of that object type. An inherited association can be overridden by explicitly defining a different association for an index instance or column, but there may be occasions when you would prefer an index or column not to have any association at all. For example, for a particular query the benefit of a better plan may not outweigh the additional compilation time incurred by invoking the cost or selectivity functions. For cases like this, you can use the ASSOCIATE command to declare a NULL association for a column or index, as in Example 10-25.

ASSOCIATE STATISTICS WITH COLUMNS columns NULL;
ASSOCIATE STATISTICS WITH INDEXES indexes NULL;

If the NULL association is specified, the schema object does not inherit any statistics type from the column type or the indextype. A NULL association also precludes default values.

How Statistics Are Affected by DDL Operations

Partition-level and schema object-level aggregate statistics are affected by DDL operations in the same way as standard statistics. Table 10-2 summarizes the effects.

ADD PARTITION

DROP PARTITION

SPLIT PARTITION

MERGE PARTITION

TRUNCATE PARTITION

EXCHANGE PARTITION

REBUILD PARTITION

MOVE PARTITION

RENAME PARTITION

If an existing partition is exchanged, or dropped with an ALTER TABLE DROP PARTITION statement, and the _minimal_stats_aggregation parameter is set to FALSE, the statistics for that partition are deleted, and the aggregate statistics of the table or index are recalculated.

Distributed Execution

Oracle's distributed implementation does not support adding functions to the remote capabilities list. All functions referencing remote tables are executed as filters. The placement of the filters occurs outside the optimizer. The cost model reflects this implementation and does not attempt to optimize placement of these predicates.

Since predicates are not shipped to the remote site, you cannot use domain indexes on remote tables. Therefore, the DESCRIBE protocol is unchanged, and remote domain indexes are not visible from the local site.

System-Managed Storage Tables and ASSOCIATE STATISTICS

If you are creating an indextype WITH SYSTEM MANAGED STORAGE TABLES, you should also create its associated statistics type WITH SYSTEM MANAGED STORAGE TABLES. If you are collecting statistics on the local indexed column using system partitioned tables, then the Oracle server maintains the system-partitioned statistics tables for them during partition maintenance operations. You can only use the WITH SYSTEM MANAGED STORAGE TABLES option when an indextype is associated with the statistics type; otherwise the system raises an error.

Aggregate Object-Level Statistics

When using local indexes, it may be useful to maintain both partition-level and aggregate object-level statistics. During partition maintenance operations, the partition level statistics are deleted, while the aggregate object-level statistics are either adjusted to reflect the operation or left "as is" for later recomputation.

The decision to adjust or recompute the aggregate statistics is made based on _minimal_stats_aggregation parameter in the server. If the parameter is FALSE, the aggregate statistics are recomputed. If the parameter is TRUE, the statistics are not recomputed.

System-Managed Domain Indexing

The system-managed domain indexing approach supports system-managed statistics that are associated with indextypes; indextype itself should also be system-managed.

Performance

The cost of execution of the queries remains the same with the extensible optimizer if the same plan is chosen. If a different plan is chosen, the execution time should be better assuming that the user-defined cost, selectivity, and statistics collection functions are accurate. In light of this, you are strongly encouraged to provide statistics collection, selectivity, and cost functions for user-defined structures because the optimizer defaults can be inaccurate and lead to an expensive execution plan.