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.
No comments:
Post a Comment