Re: Need help with improving performance of a query.

As Ryan suggested, AskTom has some pretty good discussion about "variable inlists" which is what you're working on.

My 2c:
- The cardinality hint is only 9ir1 and up so you're version ain't covered. Tough luck! (On a side note, even if you could use that, your hint should include the table/table alias first and it should be a bit deeper inside the THE()).
- THE() is way too old. Use TABLE(). It provides a more direct cast which for all we know translates to fewer cpu cycles. - I think your main goal here is to force the CBO to "materialize" the IN part of the subquery (the one with the string parser function). That way, you cut down on function executions. The rownum condition serves this intent.

Try this. Cross your fingers. ;o)

select transaction_id
from int_buyer_seller
where company_id in
(select *
from TABLE(cast(manda_search.prs_str_ret_num('2624,2860,83540,104346,104348,2954')as id_list_type))
where rownum >= 0
)
group By transaction_id having Count(Distinct company_id) = 6

Cheers.

ronnie_yours_at_yahoo.com (Ronnie) wrote in message news:<ea603f8d.0406250544.314288b9_at_posting.google.com>...
> HI,
>
> I tried the cardinality approach as you suggested but still its the same
>
> see the explain plan below
>
> 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')
> 5 as id_list_type) from dual where rownum >=0)
> 6 ) group By transaction_id having Count(Distinct company_id) = 6
> 7 /
>
> no rows selected
>
> Elapsed: 00:00:20.42
>
> 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 COUNT
> 7 6 FILTER
> 8 7 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1)
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 3 recursive calls
> 235256 db block gets
> 58913 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
>
>
> Thanks
> Ron
>
> rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0406240756.69453840_at_posting.google.com>...
> > 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 Sat Jun 26 2004 - 10:20:46 CDT