Tuning Oracle interMedia query

From: Mike Ita <mitagaki_nspam_at_yahoo.com>
Date: 2000/06/24
Message-ID: <395512cf@news.advancenet.net>#1/1

Hello,

I've been grappling with a tuning an intermedia search query and have run into a roadblock. No matter what I do, I can't seem to get the query time below 40-60 seconds for keywords that return a large number of rows. This will be used on the web, so I need fast results, although I don't expect usage to be that heavy. There are 220,000 records in the database. I am running NT 4 with Oracle 8i 8.1.6, single 466 Celeron processor, 384 MB RAM, 2 7200 RPM drives.

I've experimented with join order, made sure statistics are current on all tables, made sure all equijoins are done with indexes and that the indexes are freshly rebuilt, but to no avail. The text index is freshly rebuilt with parameters('optimize full'). The table data and the text index are on different drives. The problem seems to be with the CONTAINS clause itself. All on its own it takes as much as a minute with some keywords. (see below) The FIRST_ROWS hint doesn't seem to help very much. (see below)

My guess is that the problem is being caused by the physical I/Os. For one keyword there are more than 15,000 physical I/Os. This suspicion is supported by Performance Monitor, which goes to 70% disk time on the drive that contains the table data and 80% processor time during a query. I'm getting desperate, and am considering resorting to upgrading hardware as a final resort (having given up on the idea of optimizing SQL queries, DB structure, and the like).

Possible brute force solutions I can think of are RAID
Caching the text index table in RAM (thats a lot of ram!) Parallel Server

Can anyone think of a more elegant solution, or can identify an error in my logic?

Thanks so much,

Mike

//////////////////// Simple call to CONTAINS

SQL> select count(url_id) from urls WHERE contains(text, 'fortran', 1) > 0;

COUNT(URL_ID)

24515

Elapsed: 00:00:46.37

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2009) 1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'URLS' (Cost=1 Card=1 B
          ytes=2009)
   3    2       DOMAIN INDEX OF 'TEXT_INDEX' (Cost=1)

Statistics

       1044  recursive calls
         18  db block gets
      24556  consistent gets
      15500  physical reads
          0  redo size
    3119849  bytes sent via SQL*Net to client
     134068  bytes received via SQL*Net from client
       1920  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


/////////////////////// Example using the FIRST_ROWS hint

SQL> RUN
  1 declare
  2 cursor c is
  3 select /*+ FIRST_ROWS */ url_id, score FROM urls   4 WHERE contains(text, 'fortran', 1) > 0 ORDER BY SCORE;   5 begin
  6 for i in c
  7 loop
  8 insert into query_results (url_id) values(i.url_id);   9 exit when c%rowcount > 10;
10 end loop;
11* end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:45.96
SQL> Received on Sat Jun 24 2000 - 00:00:00 CDT

Tuning Oracle interMedia query - minimizing I/O?