2014-03-07

Variable in-list cardinality


Collect the statistics in my earlier varying in-list example inlist_test table and you might hit the following problem.


select * from table(SYS.odcivarchar2list('a','b'));

select * from table(SYS.odcinumberlist(1,2,3));

----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

The default 8168 cardinality is the root cause for some performance problems. Adrian Billington to the rescue.  
"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."

Putting the different lists to a collection wrapper package.


CREATE OR REPLACE package coll
 as 
  FUNCTION wrapper(
                  p_collection IN SYS.odcinumberlist
                  ) RETURN SYS.odcinumberlist;
  FUNCTION wrapper(
                  p_collection IN SYS.odcivarchar2list
                  ) RETURN SYS.odcivarchar2list;
  FUNCTION wrapper(
                  p_collection IN SYS.odcidatelist
                  ) RETURN SYS.odcidatelist;
END coll;
/

CREATE OR REPLACE package body coll

 as 
  FUNCTION wrapper(
                  p_collection IN SYS.odcinumberlist
                  ) RETURN SYS.odcinumberlist as
  BEGIN
     RETURN p_collection;
  END wrapper;
  FUNCTION wrapper(
                  p_collection IN SYS.odcivarchar2list
                  ) RETURN SYS.odcivarchar2list as
  BEGIN
     RETURN p_collection;
  END wrapper;
  FUNCTION wrapper(
                  p_collection IN SYS.odcidatelist
                  ) RETURN SYS.odcidatelist as
  BEGIN
     RETURN p_collection;
  END wrapper;
END;
/

CREATE OR REPLACE TYPE coll_wrapper_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_collection IN  SYS.odcinumberlist
                     ) RETURN NUMBER,
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcivarchar2list
                     ) RETURN NUMBER,
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcidatelist
                     ) RETURN NUMBER
  );
/


CREATE OR REPLACE TYPE BODY coll_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  SYS.odcinumberlist
                     ) RETURN NUMBER IS
     BEGIN
        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
        RETURN ODCIConst.success;
     END ODCIStatsTableFunction;
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcivarchar2list
                     ) RETURN NUMBER IS
     BEGIN
        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
        RETURN ODCIConst.success;
     END ODCIStatsTableFunction;
     STATIC FUNCTION ODCIStatsTableFunction (
                     p_function   IN  SYS.ODCIFuncInfo,
                     p_stats      OUT SYS.ODCITabFuncStats,
                     p_args       IN  SYS.ODCIArgDescList,
                     p_collection IN  SYS.odcidatelist
                     ) RETURN NUMBER IS
     BEGIN
        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
        RETURN ODCIConst.success;
     END ODCIStatsTableFunction;
  END;
/

--DISASSOCIATE STATISTICS FROM PACKAGES coll;


ASSOCIATE STATISTICS WITH PACKAGES coll USING coll_wrapper_ot;



select * from table(coll.wrapper(SYS.odcivarchar2list('a','b')));

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     2 |     4 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| WRAPPER |     2 |     4 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

select * from table(coll.wrapper(SYS.odcinumberlist(1,2,3)));

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     3 |     6 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| WRAPPER |     3 |     6 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

select * from table(coll.wrapper(SYS.odcidatelist(date'2014-03-07',date'2014-03-08',date'2014-03-09')));

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     3 |     6 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| WRAPPER |     3 |     6 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


Seems like just the thing desired, but how about binds?


declare
 b sys.odcinumberlist := sys.odcinumberlist(1,2,3,4,5,6,7,8,9,1,2,4,5,7,8,9,0);
 ret number;
begin
 select /*+monitor*/ count(*) into ret from table(coll.wrapper(b));
 dbms_output.put_line(ret);
end;
/

=============================================================================================================================================
| Id |              Operation               |  Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                      |         | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=============================================================================================================================================
|  0 | SELECT STATEMENT                     |         |         |      |         1 |     +0 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE                     |         |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
|  2 |    COLLECTION ITERATOR PICKLER FETCH | WRAPPER |       1 |   29 |         1 |     +0 |     1 |       17 |          |                 |
=============================================================================================================================================


Got unlucky. Esitmated is not 8168 but one. The second documented alternative was to use DYNAMIC_SAMPLING hint. 

declare
 b sys.odcinumberlist := sys.odcinumberlist(1,2,3,4,5,6,7,8,9,1,2,4,5,7,8,9,0);
 ret number;
begin
 select /*+ monitor DYNAMIC_SAMPLING(2)*/ count(*) into ret from table(b);
 dbms_output.put_line(ret);
end;
/

SQL Plan Monitoring Details (Plan Hash Value=3309076612)
==========================================================================================================================================
| Id |              Operation               | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|    |                                      |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
==========================================================================================================================================
|  0 | SELECT STATEMENT                     |      |         |      |         1 |     +0 |     1 |        1 |          |                 |
|  1 |   SORT AGGREGATE                     |      |       1 |      |         1 |     +0 |     1 |        1 |          |                 |
|  2 |    COLLECTION ITERATOR PICKLER FETCH |      |      17 |   11 |         1 |     +0 |     1 |       17 |          |                 |
==========================================================================================================================================

That seems to work also with the binds. My newly created inlist_test table did not have statistics gathered and so the DYNAMIC_SAMPLING is used to the statement in varying in list. And so the different plans are generated for different collection sizes.

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.