Re: Need help with improving performance of a query.

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 Fri Jun 25 2004 - 08:44:27 CDT