Re: Fetch cursor very slow

Ludovic Bostral wrote:
> I use a Oracle 9.2.0.1.0 server
>
> I have a view which is a join of few table :
> CREATE VIEW SE_SEARCH AS
> SELECT ... FROM ITEMS
> INNER JOIN ...
> INNER JOIN ...
> INNER JOIN ...
> INNER JOIN ...
> INNER JOIN ...
> INNER JOIN ...
>
> One of this table has a column which is a full text index, and i perform som
> search on this
>
> SELECT ITEM_SE_KEY FROM DEV_GMI2.SE_SEARCH WHERE ACCOUNT_FK = 'cfrt' and
> contains(FIELD_VALUE,'{church}') >0;
>
> I received 108000 answers for this request. But the performance is very bad
>
> When i execute this on Sql*plus
> Exec plan
>
> Plan d'exécution
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=318 Card=55284 Bytes
> =11001516)
>
> 1 0 NESTED LOOPS (Cost=318 Card=55284 Bytes=11001516)
> 2 1 HASH JOIN (Cost=318 Card=55284 Bytes=10835664)
> 3 2 TABLE ACCESS (FULL) OF 'CAT_PROJECT_RIGHT' (Cost=2 Car
> d=514 Bytes=10794)
>
> 4 2 HASH JOIN (Cost=315 Card=7043 Bytes=1232525)
> 5 4 TABLE ACCESS (FULL) OF 'CAT_ITEM_TREE' (Cost=13 Card
> =8905 Bytes=480870)
>
> 6 4 NESTED LOOPS (Cost=288 Card=7043 Bytes=852203)
> 7 6 HASH JOIN (Cost=288 Card=7043 Bytes=654999)
> 8 7 TABLE ACCESS (FULL) OF 'SE_ITEM_REF' (Cost=8 Car
> d=8874 Bytes=337212)
>
> 9 7 HASH JOIN (Cost=272 Card=7043 Bytes=387365)
> 10 9 TABLE ACCESS (BY INDEX ROWID) OF 'SE_VALUES' (
> Cost=243 Card=1605 Bytes=73830)
>
> 11 10 DOMAIN INDEX OF 'SE_VAL_FTIDX' (Cost=29)
> 12 9 TABLE ACCESS (FULL) OF 'SE_ITEM_VALUE_LINKS' (
> Cost=22 Card=193820 Bytes=1744380)
>
> 13 6 INDEX (UNIQUE SCAN) OF 'SE_ITEMS_PK' (UNIQUE)
> 14 1 INDEX (UNIQUE SCAN) OF 'COM_GRP_PK' (UNIQUE)
>
> 108480 ligne(s) sélectionnée(s).
>
> Elapsed : 00 :24 :11.00
>
> 24 minutes !!!
>
> SE_VAL_FTIDX is my full text index .
>
> I have not much experience of oracle, and i don't know why performance is so
> bad,

You have some full table accesses. Are there indexes that are not being used or are there indexes that are missing? Are statistics current with DBMS_STATS?

Patch up to 9.2.0.6 at a minimum.

What hardware and CPUs? What O/S? How much RAM?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)