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

Hallo Mike,

you wrote that your query returns a large number of rows. How many?

How big is your db_block_buffers-parameter?

Leave away the first_rows-hint because you do a count and therefore you force a complete scan of the index.

What does a plain

select --+ all_rows

        count (url_id)
from

        urls
where text like '%fortran%'
/

yield?

Martin

Mike Ita 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>
Received on Sat Jun 24 2000 - 00:00:00 CDT