Oracle Extensible Optimizer
Version 11.2.0.3

General Information
This page is based on excellent work by Adrian  Billington.

Query Optimization is the process of choosing the most efficient way to execute a SQL statement. When the cost-based optimizer was offered for the first time with Oracle7, Oracle supported only standard relational data. The introduction of objects extended the supported data types and functions. The Extensible Indexing introduced user-defined access methods.

The extensible optimizer allows authors of user-defined functions and indexes to create statistics collection, selectivity, and cost functions that are used by the optimizer in choosing a query plan. The optimizer cost model is extended to integrate information supplied by the user to assess CPU and the I/O cost, where CPU cost is the number of machine instructions used, and I/O cost is the number of data blocks fetched.

Specifically, you can:
  • Associate cost functions and default costs with domain indexes (partitioned or non-partitioned), indextypes, packages, and standalone functions. The optimizer can obtain the cost of scanning a single partition of a domain index, multiple domain index partitions, or an entire index.
  • Associate selectivity functions and default selectivity with methods of object types, package functions, and standalone functions. The optimizer can estimate user-defined selectivity for a single partition, multiple partitions, or the entire table involved in a query.
  • 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.
  • Select a user-defined access method (domain index) for a table based on access cost.
  • Use the DBMS_STATS package to invoke user-defined statistics collection and deletion functions.
  • Use new data dictionary views to include information about the statistics collection, cost, or selectivity functions associated with columns, domain indexes, indextypes or functions.
  • Add a hint to preserve the order of evaluation for function predicates.
The Extensible Optimizer has a range of well-defined methods for calculating various statistics for functions, but the one we are interested in here is the ODCIStatsTableFunction method. To use the Extensible Optimizer with a table or pipelined function, we require three components:
  • a table or pipelined function
  • an object type
  • an association between the function and the type
Data Dictionary Objects
oracle/ODCI/ODCIObject oracle/ODCI/ODCIObjectRef oracle/ODCI/ODCIObjectList
oracle/ODCI/ODCIObjectList oracle/ODCI/ODCIObject oracle/ODCI/ODCIObjectRef
Create Demo Objects -- pipelined table function
CREATE OR REPLACE TYPE employees_t AS OBJECT (
employee_id   NUMBER(6),
first_name    VARCHAR2(20),
last_name     VARCHAR2(25),
department_id NUMBER(4));
/

CREATE OR REPLACE TYPE employees_tt AS TABLE OF employees_t;
/

CREATE OR REPLACE FUNCTION employees_piped(p_num_rows IN NUMBER)
RETURN employees_tt AUTHID CURRENT_USER PIPELINED IS
BEGIN
  FOR r IN (SELECT * FROM employees) LOOP
    FOR i IN 1 .. 200 LOOP
      PIPE ROW(employees_t(r.employee_id, r.first_name, r.last_name, r.department_id));
    END LOOP;
  END LOOP;
  RETURN;
END employees_piped;
/

-- note that the P_NUM_ROWS parameter doesn't appear in the function body but is used directly by the CBO

-- interface object

CREATE OR REPLACE TYPE pipelined_stats_ot AS OBJECT (dummy_attribute NUMBER,
  STATIC FUNCTION ODCIGetInterfaces (p_interfaces OUT SYS.ODCIObjectList) RETURN NUMBER,

  STATIC FUNCTION ODCIStatsTableFunction (p_function IN SYS.ODCIFuncInfo, p_stats OUT SYS.ODCITabFuncStats, p_args IN SYS.ODCIArgDescList, p_num_rows IN NUMBER) RETURN NUMBER);
/

/* Some important points to note about this are:

* Line 3: object types must have at least one attribute, even if it is not needed in the implementation;
* Lines 5, 9: these method names are prescribed by Oracle and are not optional. There are a number of different methods that can be used for the Extensible Optimiser. This demo only uses the method for table function cardinality (ODCIStatsTableFunction). The ODCIGetInterfaces method is mandatory for all interface types;
* Lines 10-13: the parameter positions and types for ODCIStatsTableFunction are also prescribed by Oracle. There is one exception to this. Note the highlighted line after the P_ARGS parameter on line 12. Here, we must include all of the parameters of our associated table or pipelined function(s). In the case of EMPLOYEES_PIPED, the only parameter is P_NUM_ROWS, which we have included in our method as required, but interface type methods can cater for more than one user-parameter if required.

The interface type body is where we code our cardinality calculation, as follows. */


CREATE OR REPLACE TYPE BODY pipelined_stats_ot AS
 STATIC FUNCTION ODCIGetInterfaces (p_interfaces OUT SYS.ODCIObjectList) RETURN NUMBER IS
 BEGIN
   p_interfaces := SYS.ODCIObjectList(SYS.ODCIObject ('SYS', 'ODCISTATS2'));
   RETURN ODCIConst.success;
 END ODCIGetInterfaces;

 STATIC FUNCTION ODCIStatsTableFunction(p_function IN SYS.ODCIFuncInfo, p_stats OUT SYS.ODCITabFuncStats, p_args IN SYS.ODCIArgDescList, p_num_rows IN NUMBER) RETURN NUMBER IS
 BEGIN
   p_stats := SYS.ODCITabFuncStats(p_num_rows);
   RETURN ODCIConst.success;
 END ODCIStatsTableFunction;
END;
/
Associate Statistics As stated earlier, the ODCIGetInterfaces method is mandatory and so is its implementation, as shown. The ODCIStatsTableFunction is the method where we can be creative, although in fact our implementation is very simple. Remember that we included a P_NUM_ROWS parameter in our pipelined function. We didn't use it in the function itself. Instead, we have simply taken this parameter and passed it straight through to the CBO via the interface type, as highlighted above (on line 20).
(3) association

The interface type is the bridge between the table or pipelined function and the CBO. The ODCIStatsTableFunction method simply picks up the parameter we pass to our pipelined function, optionally uses it to calculate a cardinality value and then passes it on to the CBO. For Oracle to be able to do this, however, we require a third and final component; that is, the association between the pipelined function and the interface type. We do this as follows.
ASSOCIATE STATISTICS WITH FUNCTIONS employees_piped USING pipelined_stats_ot;
Testing the Extensible Optimizer With this command, our pipelined function and interface type are now directly linked. Incidentally, our type could also be associated with other functions (assuming they also had a single parameter named P_NUM_ROWS).

We have now completed our setup for the Extensible Optimiser. To test it, we will repeat our sample query but without any hints, as follows.
EXPLAIN PLAN FOR
SELECT *
FROM departments d, TABLE(employees_piped(21400)) e
WHERE d.department_id = e.department_id;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------------------
| Id | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                 | 21400 |   459K|    25   (8)|
|* 1 |  HASH JOIN                         |                 | 21400 |   459K|    25   (8)|
|  2 |   TABLE ACCESS FULL                | DEPARTMENTS     |    27 |   540 |     3   (0)|
|  3 |   COLLECTION ITERATOR PICKLER FETCH| EMPLOYEES_PIPED |       |       |            |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"=VALUE(KOKBF$))
 
Demo
This demo is still under development The Extensible Optimiser has a range of well-defined methods for calculating various statistics for functions, but the one we are interested in is the ODCIStatsTableFunction method. To use the Extensible Optimiser with a table or pipelined function, we require three components:

* a table or pipelined function;
* an object type;
* an association between the function and the type.

We will create each of these components below.

(1) pipelined function


First, we'll modify our EMPLOYEES_PIPED pipelined function to include a P_NUM_ROWS parameter, as follows.


SQL> CREATE OR REPLACE FUNCTION employees_piped(
2 p_num_rows IN NUMBER
3 ) RETURN employees_ntt PIPELINED AS
4 BEGIN
5 FOR r IN (SELECT * FROM employees) LOOP
6 FOR i IN 1 .. 200 LOOP
7 PIPE ROW (employees_ot(
8 r.employee_id, r.first_name, r.last_name,
9 r.email, r.phone_number, r.hire_date,
10 r.job_id, r.salary, r.commission_pct,
11 r.manager_id, r.department_id));
12 END LOOP;
13 END LOOP;
14 RETURN;
15 END employees_piped;
16 /

Function created.

Note that the P_NUM_ROWS parameter doesn't appear in the function body at all. Instead, it will be used by the CBO as we will see shortly.

(2) interface type

We will now create an interface object type, beginning with the specification, as follows.


SQL> CREATE OR REPLACE TYPE pipelined_stats_ot AS OBJECT (
2
3 dummy_attribute NUMBER,
4
5 STATIC FUNCTION ODCIGetInterfaces (
6 p_interfaces OUT SYS.ODCIObjectList
7 ) RETURN NUMBER,
8
9 STATIC FUNCTION ODCIStatsTableFunction (
10 p_function IN SYS.ODCIFuncInfo,
11 p_stats OUT SYS.ODCITabFuncStats,
12 p_args IN SYS.ODCIArgDescList,
13 p_num_rows IN NUMBER
14 ) RETURN NUMBER
15
16 );
17 /

Type created.

Some important points to note about this are as follows:

* Line 3: object types must have at least one attribute, even if it is not needed in the implementation;
* Lines 5, 9: these method names are prescribed by Oracle and we must use them. There are a number of different methods that we can use for the Extensible Optimiser. In our case, we are only using the method for table function cardinality (ODCIStatsTableFunction). The ODCIGetInterfaces method is mandatory for all interface types;
* Lines 10-13: the parameter positions and types for ODCIStatsTableFunction are also prescribed by Oracle. There is one exception to this. Note the highlighted line after the P_ARGS parameter on line 12. Here, we must include all of the parameters of our associated table or pipelined function(s). In the case of EMPLOYEES_PIPED, the only parameter is P_NUM_ROWS, which we have included in our method as required, but interface type methods can cater for more than one user-parameter if required.

The interface type body is where we code our cardinality calculation, as follows.


SQL> CREATE TYPE BODY pipelined_stats_ot AS
2
3 STATIC FUNCTION ODCIGetInterfaces (
4 p_interfaces OUT SYS.ODCIObjectList
5 ) RETURN NUMBER IS
6 BEGIN
7 p_interfaces := SYS.ODCIObjectList(
8 SYS.ODCIObject ('SYS', 'ODCISTATS2')
9 );
10 RETURN ODCIConst.success;
11 END ODCIGetInterfaces;
12
13 STATIC FUNCTION ODCIStatsTableFunction (
14 p_function IN SYS.ODCIFuncInfo,
15 p_stats OUT SYS.ODCITabFuncStats,
16 p_args IN SYS.ODCIArgDescList,
17 p_num_rows IN NUMBER
18 ) RETURN NUMBER IS
19 BEGIN
20 p_stats := SYS.ODCITabFuncStats(p_num_rows);
21 RETURN ODCIConst.success;
22 END ODCIStatsTableFunction;
23
24 END;
25 /

Type body created.

As stated earlier, the ODCIGetInterfaces method is mandatory and so is its implementation, as shown. The ODCIStatsTableFunction is the method where we can be creative, although in fact our implementation is very simple. Remember that we included a P_NUM_ROWS parameter in our pipelined function. We didn't use it in the function itself. Instead, we have simply taken this parameter and passed it straight through to the CBO via the interface type, as highlighted above (on line 20).

(3) association

The interface type is the bridge between the table or pipelined function and the CBO. The ODCIStatsTableFunction method simply picks up the parameter we pass to our pipelined function, optionally uses it to calculate a cardinality value and then passes it on to the CBO. For Oracle to be able to do this, however, we require a third and final component; that is, the association between the pipelined function and the interface type. We do this as follows.


SQL> ASSOCIATE STATISTICS WITH FUNCTIONS employees_piped USING pipelined_stats_ot;

Statistics associated.

With this command, our pipelined function and interface type are now directly linked. Incidentally, our type could also be associated with other functions (assuming they also had a single parameter named P_NUM_ROWS).

testing the extensible optimiser

We have now completed our setup for the Extensible Optimiser. To test it, we will repeat our sample query but without any hints, as follows.


SQL> set autotrace traceonly explain

SQL> SELECT *
2 FROM departments d
3 , TABLE(employees_piped(21400)) e
4 WHERE d.department_id = e.department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3250313570

---------------------------------------------------------------------- ...
| Id | Operation | Name | Rows | ...
---------------------------------------------------------------------- ...
| 0 | SELECT STATEMENT | | 21400 | ...
|* 1 | HASH JOIN | | 21400 | ...
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | ...
| 3 | COLLECTION ITERATOR PICKLER FETCH| EMPLOYEES_PIPED | | ...
---------------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("D"."DEPARTMENT_ID"=VALUE(KOKBF$))

As we can see, the CBO has picked up the cardinality value that we passed to our pipelined function and used it to optimise our SQL. If we trace the optimisation of our query with a 10053 event, we can see further evidence that our interface type is being used by the CBO, as follows.

Access path analysis for KOKBF$
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for KOKBF$[KOKBF$]
Calling user-defined function card function...
Bind :3 Value 21400
HR.PIPELINED_STATS_OT.ODCIStatsTableFunction returned:
num_rows : 21400
Table: KOKBF$ Alias: KOKBF$
Card: Original: 21400.000000 Rounded: 21400 Computed: 21400.00 Non Adjusted: 21400.00
Access Path: TableScan
Cost: 29.29 Resp: 29.29 Degree: 0
Cost_io: 29.00 Cost_cpu: 6481984
Resp_io: 29.00 Resp_cpu: 6481984
Best:: AccessPath: TableScan
Cost: 29.29 Degree: 1 Resp: 29.29 Card: 21400.00 Bytes: 0

***************************************

This trace file is from an 11.1.0.7 instance. We can see that the optimiser xecutes the ODCIStatsTableFunction method in our interface type and receives the correct cardinality in return. Note that in 10g (and possibly 11.1.0.6), the 10053 trace file includes the full PL/SQL block that the optimiser uses to execute our ODCIStatsTableFunction method.
benefits of the extensible optimiser method

Overall, we can see that the Extensible Optimiser is a useful, accurate and, importantly, a supported method for supplying cardinality to the CBO. Its main benefit over the DYNAMIC_SAMPLING hint (the only other supported method at the time of writing) is that it doesn't execute the pipelined function itself, just the ODCIStatsTableFunction method.

In our example, we have simply exploited the Extensible Optimiser feature to create our own alternative to the CARDINALITY hint. Apart from being a supported method, another benefit over the CARDINALITY hint is that initial value of P_NUM_ROWS could be passed as a variable rather than hard-coded as above (it must be known in advance when used in the CARDINALITY hint).

An alternative implementation

For an alternative implementation of our Extensible Optimiser method, we could remove the P_NUM_ROWS parameter and instead use a lookup table to store representative cardinalities for all of our table or pipelined functions. A single interface type could be associated to all functions, with the ODCIStatsTableFunction method looking up the cardinality based on the executing function name (which is also known to the interface type). With this technique, we could avoid hard-coding cardinalities and modify them over time in the lookup table as needed.
the extensible optimiser, table functions and variable in-lists

So far, our examples have all been based on the EMPLOYEES_PIPED pipelined function. We will complete this article with an example of a table function. Table functions are commonly used as a mechanism to bind variable in-lists passed as collections into SQL queries (for an example, see this article). Using the Extensible Optimiser, we can devise a generic way to determine the cardinalities of all table functions used in variable in-list queries.

First, we will create a simple collection type to support any variable in-list of string values.


SQL> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
2 /

Type created.

Second, we will create a small function that will receive and return a collection of our generic VARCHAR2_NTT type. This function does nothing with the collection itself; it is merely a wrapper over it.

SQL> CREATE FUNCTION collection_wrapper(
2 p_collection IN varchar2_ntt
3 ) RETURN varchar2_ntt IS
4 BEGIN
5 RETURN p_collection;
6 END collection_wrapper;
7 /

Function created.

Third, we will create an interface type specification to be associated with our simple COLLECTION_WRAPPER function, as follows.

SQL> CREATE TYPE collection_wrapper_ot AS OBJECT (
2
3 dummy_attribute NUMBER,
4
5 STATIC FUNCTION ODCIGetInterfaces (
6 p_interfaces OUT SYS.ODCIObjectList
7 ) RETURN NUMBER,
8
9 STATIC FUNCTION ODCIStatsTableFunction (
10 p_function IN SYS.ODCIFuncInfo,
11 p_stats OUT SYS.ODCITabFuncStats,
12 p_args IN SYS.ODCIArgDescList,
13 p_collection IN varchar2_ntt
14 ) RETURN NUMBER
15
16 );
17 /

Type created.

This is very similar to our previous example so doesn' need to be explained in any great detail. Note, however, that our function has a P_COLLECTION parameter, which needs to be replicated in our ODCIStatsTableFunction method signature. We can now add our interface type body, as follows:

SQL> CREATE TYPE BODY collection_wrapper_ot AS
2
3 STATIC FUNCTION ODCIGetInterfaces (
4 p_interfaces OUT SYS.ODCIObjectList
5 ) RETURN NUMBER IS
6 BEGIN
7 p_interfaces := SYS.ODCIObjectList(
8 SYS.ODCIObject ('SYS', 'ODCISTATS2')
9 );
10 RETURN ODCIConst.success;
11 END ODCIGetInterfaces;
12
13 STATIC FUNCTION ODCIStatsTableFunction (
14 p_function IN SYS.ODCIFuncInfo,
15 p_stats OUT SYS.ODCITabFuncStats,
16 p_args IN SYS.ODCIArgDescList,
17 p_collection IN varchar2_ntt
18 ) RETURN NUMBER IS
19 BEGIN
20 p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
21 RETURN ODCIConst.success;
22 END ODCIStatsTableFunction;
23
24 END;
25 /

Type body created.

Our implementation is very similar to our previous example. This time, however, we have a collection parameter rather than a scalar number, so to supply the CBO with the correct cardinality, we simply count the collection's elements (line 20).

Fourth and finally, we must associate the function with the interface type, as follows.


SQL> ASSOCIATE STATISTICS WITH FUNCTIONS collection_wrapper USING collection_wrapper_ot;

Statistics associated.

Before we test the Extensible Optimiser with a variable in-list query, we'll see how it works with a simple table function select with Autotrace. First, we will query a hard-coded collection of three elements without our COLLECTION_WRAPPER function, as follows:

SQL> set autotrace traceonly explain

SQL> SELECT *
2 FROM TABLE(varchar2_ntt('A','B','C'));

Execution Plan
----------------------------------------------------------
Plan hash value: 1748000095

--------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 |
| 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | |
--------------------------------------------------------------

Unsurprisingly, the CBO has to resort to the default cardinality of 8,168 rows. To counter this, we can wrap our collection in a call to the wrapper function and enable the CBO to get the correct cardinality, as follows:

SQL> set autotrace traceonly explain


SQL> SELECT *
2 FROM TABLE(
3 collection_wrapper(
4 varchar2_ntt('A','B','C')));

Execution Plan
----------------------------------------------------------
Plan hash value: 4261576954

------------------------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| COLLECTION_WRAPPER | |
------------------------------------------------------------------------

This time the optimiser has the correct cardinality due to our interface type. The generic COLLECTION_WRAPPER function and COLLECTION_WRAPPER_OT interface type combine to provide statistics to the CBO when using small collections such as this.

As stated earlier, table functions are typically used to support variable in-lists, so we will see an example of how this wrapper method can assist in this scenario. We will filter the EMPLOYEES table by a variable in-list of names. The in-list is represented by a collection and although it is hard-coded for simplicity below, we would usually expect it to be passed as a parameter/bind variable.

First, we will execute the query without the COLLECTION_WRAPPER function, as follows:


SQL> set autotrace traceonly explain

SQL> SELECT *
2 FROM employees
3 WHERE last_name IN (SELECT column_value
4 FROM TABLE(
5 varchar2_ntt('Grant','King')));

Execution Plan
----------------------------------------------------------
Plan hash value: 2363723373

-------------------------------------------------------------------- ...
| Id | Operation | Name | Rows | ...
-------------------------------------------------------------------- ...
| 0 | SELECT STATEMENT | | 1 | ...
|* 1 | HASH JOIN SEMI | | 1 | ...
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | ...
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | ...
-------------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("LAST_NAME"=VALUE(KOKBF$))

Despite the fact that we only want to query two names from the EMPLOYEES table, Oracle has chosen a hash semi-join. This is because the optimiser has used the 8,168 heuristic cardinality for our collection. To see the effect of the Extensible Optimiser in this case, we will repeat the query but with the COLLECTION_WRAPPER function, as follows:

SQL> SELECT *
2 FROM employees
3 WHERE last_name IN (SELECT column_value
4 FROM TABLE(
5 collection_wrapper(
6 varchar2_ntt('Grant','King'))));

Execution Plan
----------------------------------------------------------
Plan hash value: 1407606243

--------------------------------------------------------------------------- ...
| Id | Operation | Name | Rows | ...
--------------------------------------------------------------------------- ...
| 0 | SELECT STATEMENT | | 2 | ...
| 1 | NESTED LOOPS | | | ...
| 2 | NESTED LOOPS | | 2 | ...
| 3 | SORT UNIQUE | | | ...
| 4 | COLLECTION ITERATOR PICKLER FETCH| COLLECTION_WRAPPER | | ...
|* 5 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | ...
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | ...
--------------------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("LAST_NAME"=VALUE(KOKBF$))

This time, the optimiser is aware that there are only two elements in the variable in-list collection and has opted for a nested loops join accordingly. This also demonstrates that the COLLECTION_WRAPPER and COLLECTION_WRAPPER_OT objects can be re-used for all queries that include small collections (as in-lists or otherwise).

Summary

In this article, we have seen four methods for supplying table and pipelined function cardinalities to the optimiser. Two of these methods are unsupported (as of 11g Release 1) and for this reason, their use in production code is discouraged. Of the two supported methods, the DYNAMIC_SAMPLING hint is a new feature of 11.1.0.7 and has some limitations and performance implications. The Extensible Optimiser feature is the most flexible method to use at this stage and is usable in all versions of 10g. Using this, we have devised a good alternative to the CARDINALITY hint for pipelined functions and also created a generic wrapper for small collections that are typically used in variable in-list queries.

Related Topics
Tuning

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved