| Create Demo Objects |
-- pipelined table function
CREATE TABLE employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
department_id NUMBER(4))
TABLESPACE uwdata;
INSERT INTO employees VALUES (1, 'Dan', 'Morgan', 100);
INSERT INTO employees VALUES (2, 'Randy', 'Lind', 200);
INSERT INTO employees VALUES (3, 'Sanjay', 'Varma', 100);
COMMIT;
CREATE TABLE departments (
department_id NUMBER(4),
department_name VARCHAR2(20))
TABLESPACE uwdata;
INSERT INTO departments VALUES (100, 'Roller Coast');
INSERT INTO departments VALUES (200, 'West Coast');
INSERT INTO departments VALUES (300, 'East Coast');
COMMIT;
-- pipelined table function
CREATE OR REPLACE TYPE employees_t AUTHID DEFINER 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;
/
-- parameter P_NUM_ROWS doesn't appear in the function body but is used by the CBO
-- interface object
CREATE OR REPLACE TYPE pipelined_stats_ot AUTHID DEFINER 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;
/ |
| This demo is still under development but will be
based on one posted here and updated to version 21c when the update is completed. |
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.
CREATE OR REPLACE TYPE employees_ot AUTHID DEFINER AS OBJECT (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4));
/
CREATE OR REPLACE TYPE employees_ntt AS TABLE OF employees_ot;
/
CREATE OR REPLACE FUNCTION employees_piped(p_num_rows IN NUMBER)
RETURN employees_ntt PIPELINED AS
BEGIN
FOR r IN (SELECT * FROM employees) LOOP
FOR i IN 1 .. 200 LOOP
PIPE ROW (employees_ot(
r.employee_id, r.first_name, r.last_name,
r.email, r.phone_number, r.hire_date,
r.job_id, r.salary, r.commission_pct,
r.manager_id, r.department_id));
END LOOP;
END LOOP;
RETURN;
END employees_piped;
/
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.
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);
/
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.
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;
/
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 optimizer
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.
set autotrace traceonly explain
SELECT *
FROM departments d,
TABLE(employees_piped(21400)) e
WHERE d.department_id = e.department_id;
Execution Plan
----------------------------------------------------------------------
| 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
executes 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.
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/
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.
CREATE FUNCTION collection_wrapper(p_collection IN varchar2_ntt) RETURN varchar2_ntt IS
BEGIN
RETURN p_collection;
END collection_wrapper;
/
Function created.
Third, we will create an interface type specification to be associated with our simple COLLECTION_WRAPPER function, as follows.
CREATE OR REPLACE TYPE collection_wrapper_ot AUTHID DEFINER 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_collection IN varchar2_ntt) RETURN NUMBER);
/
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:
CREATE OR REPLACE TYPE BODY collection_wrapper_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_collection IN varchar2_ntt) RETURN NUMBER IS
BEGIN
p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
RETURN ODCIConst.success;
END ODCIStatsTableFunction;
END;
/
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.
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:
set autotrace traceonly explain
SELECT * 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:
set autotrace traceonly explain
SELECT * FROM TABLE(collection_wrapper(varchar2_ntt('A','B','C')));
Execution Plan
------------------------------------------------------------------------
| 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:
set autotrace traceonly explain
SELECT *
FROM employees
WHERE last_name IN (SELECT column_value
FROM TABLE(varchar2_ntt('Grant','King')));
Execution Plan
------------------------------------------------------------------
| 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:
SELECT *
FROM employees
WHERE last_name IN (SELECT column_value
FROM TABLE(collection_wrapper(varchar2_ntt('Grant','King'))));
Execution Plan
------------------------------------------------------------------------
| 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. |
