Re: realistic cardinality for PL/SQL procedures in SELECT statements

Volker Apelt wrote:
> Oracle 9.2.0.7 on SuSE Linux (AMD 64)
> db_block_size=<<16 kb>>
>
> Maybe you can help me on this problem with the CBO.
>
>
> If I use a simple query like
>
> select * from TABLE_A a, TABLE_B b
> where a.NAME = b.NAME and a.KEY_IN_A = 'X';
>
> the CBO will look at the table and index statistics for both tables
> to guess which query plan has the lowest costs.
> In addition to the table statistics it will look at the system
> statistics to adjust the costs for IO and CPU to the available hardware
> and some configuration parameters like optimizer_index_cost_adj
> and alike.
>
> The statistics for these tables can be produced with ANALYZE
> or DBMS_STATS.
>
> But, what if the TABLE is created from a PL/SQL procedure
> on the fly?
>
> How can I tell the CBO what number of records the procedure
> returns?
>
> eg:
> -- TEST_MODUL.TEST_DATA is a procedure returning a TABLE, see below
> --
> SELECT * FROM TABLE (
> TEST_MODUL.TEST_DATA(
> CURSOR(SELECT * FROM TEST_SMALL_RESULTSET)
> , batch_size => 100
> )
> ) SG
> ;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=16360 Bytes=6053200)
> 1 0 VIEW (Cost=14 Card=16360 Bytes=6053200)
> 2 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DATA'
>
> Above SELECT returns 6 rows but the CBO assumes it
> returns 16360 records. This seems to depend on the db block size
> as I have seen in other posts to this topic.
> That cost estimation will not change when the size of the
> result set changes.
>
> In our application this procedure returns a variable number of
> records in multiple collections. The number of returnd rows
> ranges from about 10 to some million depending on the input
> parameters to the procedure. The procedure is PIPELINED.
>
> The above query is a very stripped down version of the original
> query where the result set of the procedure is used as a filter
> for other huge tables with up to 10^10 records.
>
> So, the query can change its character from a 'pick one record'
> to a 'filter many records' and the change is driven by the
> size of the result set of the procedure.
>
> We could switch outlines depending on the procedure parameters
> before the query is started or insert HINTS, but we have not
> found a way tell the CBO about the different cardinalities.
>
>
> Hints and pointers are welcome,
>
> Thanks,
>
> Volker
>
>
> ## package body
> CREATE OR REPLACE TYPE coll_number
> IS TABLE OF NUMBER;
> /
> CREATE OR REPLACE TYPE test_data_rowType
> AS OBJECT(
> ID_TABLE_A NUMBER,
> ATTRIB_1 NUMBER,
> ATTRIB_2 NUMBER
> );
> /
> CREATE OR REPLACE TYPE test_data_ids
> AS TABLE OF test_data_rowType;
> /
> CREATE OR REPLACE PACKAGE TEST_MODUL
> IS
> FUNCTION test_data( v_cursor IN sys_refcursor
> ,batch_size IN NUMBER)
> RETURN test_data_ids
> PIPELINED;
> END;
> /
> create table TEST_SMALL_RESULTSET(
> ID_TABLE_A NUMBER,
> ATTRIB_1 NUMBER,
> ATTRIB_2 NUMBER
> );

Consider also rewriting the query as there are many different constructs that, with different plans, will yield the result set.

Nine different versions can be found at: http://www.psoug.org/reference/explain_plan.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org