Re: Tuning Oracle interMedia query - minimizing I/O?

Mike,

Another option is OMNIDEX keyword indexing from DISC to layer on top of your Oracle database. (See http://www.disc.com/keyword.html.) We just performed benchmarks comparing OMNIDEX with InterMedia Text for another company with difficulties similar to your, and reduced their average query time to .5 seconds.

If you would like more information, please contact me.

Cheryl Grandy
cgrandy_at_disc.com
303 444-4000
www.disc.com/home

In article <395512cf_at_news.advancenet.net>,   "Mike Ita" <mitagaki_nspam_at_yahoo.com> wrote:
> 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>
   >
   >
   

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.