Re: Need help with improving performance of a query.

you need to use the cardinality hint. When Oracle does a 'pickler fetch' it assumes there will be about 8192 rows returned. It doesn't have stats on your package. You need to tell oracle how many rows to expect back. This works 99% of the time to get a good plan. I ran into one case, where I still got a bad plan when using a proper cardinality hint. I put it on asktom and he told me to use a global temp table instead of the pickler fetch...

Also you need to change your code in the following way... (note addition of where rownum >= 0)

if you do not do this your package will be called once for every row it hits in the query. IF you add that it will be called just once.

cardinality hints can be estimated. You can use a few sets. Say a value of 20 for anything that returns less than 50 values and higher ones for others. This works almost all the time(though there are occassional exceptions).

this is all on asktom. Look up 'variable inlist'

> SQL> Select transaction_id From int_buyer_seller
> 2 Where company_id in
> 3 (select /*+ cardinality (6) */ * from THE(select cast
> 4 (manda_search.prs_str_ret_num('2624,2860,83540,104346,104348,2954')
> as id_list_type) from dual where rownum >= 0)
> )
> 5 group By transaction_id having Count(Distinct company_id) = 6
> 6 /

ronnie_yours_at_yahoo.com (Ronnie) wrote in message news:<ea603f8d.0406231457.1dc8bd20_at_posting.google.com>...
> Hi,
>
> Oracle Database Version 8.1.7.0.0
>
> I have a table with 30,00 records and the structure is
>
> SQL> desc int_buyer_seller
> Name Type Nullable Default Comments
> -------------- ------------- -------- ------- --------
> TRANSACTION_ID NUMBER
> COMPANY_ID NUMBER
> TRANS_ROLE_ID NUMBER
> ADVISOR_ID NUMBER Y
> RATIONALE_DESC VARCHAR2(100) Y
>
> The table has a composite primary primary key on the
> Transaction_Id, Company_Id and the Trans_Role_Id columns. Additionaly
> I created an Index on the Transaction_Id and Company_Id column.
>
> I have a query which takes about 20 seconds to run
>
> SQL> Select transaction_id From int_buyer_seller
> 2 Where company_id in
> 3 (select * from THE(select cast
> 4 (manda_search.prs_str_ret_num('2624,2860,83540,104346,104348,2954')
> as id_list_type) from dual)
> )
> 5 group By transaction_id having Count(Distinct company_id) = 6
> 6 /
>
> no rows selected
>
> Elapsed: 00:00:19.57
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=294168 Card=148 Byte
> s=1480)
>
> 1 0 FILTER
> 2 1 SORT (GROUP BY) (Cost=294168 Card=148 Bytes=1480)
> 3 2 NESTED LOOPS (Cost=294168 Card=240742 Bytes=2407420)
> 4 3 INDEX (FULL SCAN) OF 'PK_BUYER_SELLER' (UNIQUE) (Cos
> t=98 Card=29407 Bytes=235256)
>
> 5 3 COLLECTION ITERATOR (PICKLER FETCH)
> 6 5 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1)
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 9 recursive calls
> 235256 db block gets
> 58919 consistent gets
> 0 physical reads
> 0 redo size
> 222 bytes sent via SQL*Net to client
> 314 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 5 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> This query calls a function which parses the string and returns the
> values in a pl/sql table type.
>
> The same query executes in less than 1 sec when I modify it to
>
>
> 1 Select transaction_id From int_buyer_seller
> 2 Where company_id in (2624,2860,83540,104346,104348,2954)
> 3* group By transaction_id having Count(Distinct company_id) = 6
> 4 /
>
> no rows selected
>
> Elapsed: 00:00:00.53
>
> Any suggestions as to how to improve this.
>
> I have wasted the whole day looking for solutions and trying various
> methods without any success.
>
> Hope somebody will help me out.
>
> Thanks
> Ron
Received on Thu Jun 24 2004 - 10:56:43 CDT