Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> realistic cardinality for PL/SQL procedures in SELECT statements

realistic cardinality for PL/SQL procedures in SELECT statements

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Wed, 16 Aug 2006 21:51:44 +0200
Message-ID: <lghd0c31db.fsf@chi.biosolveit.loc>


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	

);

--

Volker Apelt Received on Wed Aug 16 2006 - 14:51:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US