Imedia query tuning

From: Ranganath K <ranganathk_at_subexgroup.com>
Date: Wed, 16 May 2001 04:45:06 -0700
Message-ID: <F001.00304A41.20010516040522@fatcity.com>

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ranganath K
  INET: ranganathk_at_subexgroup.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).

Received on Wed May 16 2001 - 06:45:06 CDT