Re: Imedia query tuning

Hello,

I'm not a guru : I just try to help you. :->

Hint for Query 2 :
Have you try to hint so you run on the editor_choice index. If it works. Can you remove the Order command. Because the index is already ordered.

Regards
Henrik E.

On Wed, 16 May 2001, Ranganath K wrote:

> Dear DBA Gurus,
>
> I have the following two queries along with execution plan and statistics.
> The first one is taking a long time to execute. The second query is taking
> a long time to execute when I use the order by clause. Is there any way I
> can reduce the execution time as these queries will be used by a search
> engine? Any help in this regard will be greatly appreciated.
>
> SQL> select depth, count(*) a from category, site
> 2 where (site.fk_category in (select pk_category_id from category
> 3 where category.status = 0)) and site.status = 0
> 4 and ((contains (title,'box') > 0) or
> 5 (contains (description, 'box') > 0))
> 6 and pk_category_id = fk_category group by depth order by a desc;
>
> 467 rows selected.
>
> Elapsed: 00:00:16.43
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=214936 Card=22470 By
> tes=136572660)
>
> 1 0 SORT (ORDER BY) (Cost=214936 Card=22470 Bytes=136572660)
> 2 1 SORT (GROUP BY) (Cost=214936 Card=22470 Bytes=136572660)
> 3 2 NESTED LOOPS (Cost=19 Card=22470 Bytes=136572660)
> 4 3 NESTED LOOPS (Cost=4 Card=15 Bytes=60945)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Ca
> rd=1 Bytes=4037)
>
> 6 5 BITMAP CONVERSION (TO ROWIDS)
> 7 6 BITMAP OR
> 8 7 BITMAP CONVERSION (FROM ROWIDS)
> 9 8 SORT (ORDER BY)
> 10 9 DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1
> )
>
> 11 7 BITMAP CONVERSION (FROM ROWIDS)
> 12 11 SORT (ORDER BY)
> 13 12 DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1)
> 14 4 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=
> 1 Card=1499 Bytes=38974)
>
> 15 14 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
> 16 3 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1
> Card=149802 Bytes=301851030)
>
> 17 16 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 786 recursive calls
> 40 db block gets
> 5919 consistent gets
> 1389 physical reads
> 0 redo size
> 1829532 bytes sent via SQL*Net to client
> 69737 bytes received via SQL*Net from client
> 920 SQL*Net roundtrips to/from client
> 13 sorts (memory)
> 0 sorts (disk)
> 467 rows processed
>
> SQL> select depth, title, description, url, editor_choice from category,site
> 2 where (site.fk_category in (select pk_category_id from category
> 3 where category.status = 0)) and site.status = 0
> 4 and site.fk_category = category.pk_category_id
> 5 and ((contains (title, 'box') > 0) or
> 6 (contains (description, 'box' ) > 0)) order by editor_choice desc;
>
> 552 rows selected.
>
> Elapsed: 00:00:16.94
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=142941 Card=22470 By
> tes=181714890)
>
> 1 0 SORT (ORDER BY) (Cost=142941 Card=22470 Bytes=181714890)
> 2 1 NESTED LOOPS (Cost=19 Card=22470 Bytes=181714890)
> 3 2 NESTED LOOPS (Cost=4 Card=15 Bytes=91080)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Card
> =1 Bytes=6046)
>
> 5 4 BITMAP CONVERSION (TO ROWIDS)
> 6 5 BITMAP OR
> 7 6 BITMAP CONVERSION (FROM ROWIDS)
> 8 7 SORT (ORDER BY)
> 9 8 DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1)
> 10 6 BITMAP CONVERSION (FROM ROWIDS)
> 11 10 SORT (ORDER BY)
> 12 11 DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1)
> 13 3 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1
> Card=1499 Bytes=38974)
>
> 14 13 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
> 15 2 TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1 Ca
> rd=149802 Bytes=301851030)
>
> 16 15 INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 781 recursive calls
> 66 db block gets
> 6930 consistent gets
> 1708 physical reads
> 0 redo size
> 2244834 bytes sent via SQL*Net to client
> 252240 bytes received via SQL*Net from client
> 2265 SQL*Net roundtrips to/from client
> 11 sorts (memory)
> 1 sorts (disk)
> 552 rows processed
>
> TIA and Regards,
>
> Ranganath
>
>
>

-- 
---------------------------------------------------------------------------
Henrik Ekenberg                                 Anoto AB
Direct    +46 (0)8 410 78 577                   Vasagatan 7
Mobile    +46 (0)733  478 577                   111 20 Stockholm, Sweden
www.anoto.com                                   Switchb. +46 (0)8 406 79 00
E-mail :henrik.ekenberg_at_anoto.com               Fax      +46 (0)8 406 79 50


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henrik Ekenberg
  INET: henrik.ekenberg_at_anoto.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).