| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Help! Intermedia Text Misery...
I really need some help on this
I just installed Oracle's Intermedia text on a Sun 450 (I know. It's kind of
small, but it's a test server) and the performance is terrible.
In fact, it's faster for me to do a 'like' then it is to use the "contains"
clause... The like is 2.5 seconds on a table with 50K rows and the
"contatins" takes 40 seconds! When I did an autotrace, it shows it's doing a
tablescan on the table and not even using the text index. I used the
ctx_query.count and it came back in .5 seconds. I haven't tried any of the
other ctx_ packages.
I'm having a hard time believing people are using this. I can only assume I
have it setup improperly. There are several concerns and questions I have
and I need answers quickly or I'll have to search for other products.
Maybe somebody can clue me in on the secrets.
Main Issues:
Response time is dismal (see above)!
The amount of space for the supporting indexes and tables are 4 times the
size of the table I'm indexing (see below)! This is a big concern for me.
Terrible documentation and explanation of what's going on and the "gotchas"
Lack of tuning information
Lack of Configuration Information
I called Oracle Support and the analyst didn't know anything about it
either. I usually get pretty good help, but they failed here.
I have no choice but to get this working. I hope somebody can help. Are there any special admin parameters I should have set or other configuration options???
Thanks,
Steve
describe on junk1
Name Null? Type ----------------------------------------------------- ---------------------- --- GID NOT NULL VARCHAR2(24) DELETED NOT NULL VARCHAR2(1) MODIFICATIONDATE NOT NULL DATE CREATIONDATE NOT NULL DATE CREATEDBY NOT NULL VARCHAR2(24)index on this column --->BODY
Here's some timings and info from my little test:
--
keep in mind junk1 has 50K rows and note_detail has 5k rows. the stats are up to date on the tables and indexes the optimizer is set to choose
--. 1/2 second
set serveroutput on
declare count1 number;
begin
count1 := ctx_query.count_hits(index_name ='NTD_TEXT_IDX',
text_query='legae', exact =TRUE);
dbms_output.put_line('Number of docs with problem:');
dbms_output.put_line(count1);
end;
/
--6 seconds
SELECT count(*) FROM trash_user.note_detail WHERE CONTAINS(body, 'legae',1)
0;
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=417)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NOTE_DETAIL' (Cost=48 Card=1Bytes=417)
--* --*** Maybe this will not work for us... --*
SQLset timing on
SQLset autotrace on
SQL
SQL--data I know is in note_detail, but unsure in junk1
SQLSELECT count(*) from junk1
2 WHERE CONTAINS(body, 'password', 1) 0
3 /
COUNT(*)
2
real: 43172
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=999 Card=1 8 ) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'JUNK1' (Cost=999 Card=1 Bytes=928)
Statistics
99798 recursive calls
16 db block gets
215764 consistent gets
0 physical reads
0 redo size
199074 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQLSELECT count(*) from junk1
2 WHERE CONTAINS(body, 'about(password)') 0
3 /
COUNT(*)
1
real: 41921
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=999 Card=1
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'JUNK1' (Cost=999 Card=1 Bytes=928)
Statistics
99581 recursive calls
16 db block gets
215712 consistent gets
0 physical reads
0 redo size
199074 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQLSELECT /*+ index(note_detail NTD_TEXT_IDX) */
2 count(*)
3 from note_detail
4 WHERE CONTAINS(body, 'password', 1) 0
5 /
COUNT(*)
194
real: 5658
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=417)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NOTE_DETAIL' (Cost=48 Card=176
Bytes=73392)
Statistics
5886 recursive calls
4 db block gets
12515 consistent gets
0 physical reads
0 redo size
912441 bytes sent via SQL*Net to client
795315 bytes received via SQL*Net from client
11768 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQLSELECT count(*) from note_detail
2 WHERE CONTAINS(body, 'about(password)') 0
3 /
COUNT(*)
150
real: 5588
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=417)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NOTE_DETAIL' (Cost=48 Card=1
Bytes=417)
Statistics
5886 recursive calls
4 db block gets
12515 consistent gets
0 physical reads
0 redo size
902607 bytes sent via SQL*Net to client
795282 bytes received via SQL*Net from client
11768 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQL--data I know is in junk, but unsure in note_Detail
SQLSELECT count(*) from junk1
2 WHERE CONTAINS(body, 'detail', 1) 0
3 /
COUNT(*)
6
real: 35421
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=999 Card=1 Bytes=928 )
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'JUNK1' (Cost=999 Card=1 Bytes=928)
Statistics
49934 recursive calls
16 db block gets
166065 consistent gets
0 physical reads
0 redo size
99780 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQLSELECT count(*) from junk1
2 WHERE CONTAINS(body, 'about(detail)') 0
3 /
COUNT(*)
6
real: 35371
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=999 Card=1
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'JUNK1' (Cost=999 Card=1 Bytes=928)
Statistics
49934 recursive calls
16 db block gets
166065 consistent gets
0 physical reads
0 redo size
99780 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQLSELECT count(*) from note_detail
2 WHERE CONTAINS(body, 'detail', 1) 0
3 /
COUNT(*)
16
real: 5648
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=417)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NOTE_DETAIL' (Cost=48 Card=1
Bytes=417)
Statistics
6383 recursive calls
4 db block gets
13012 consistent gets
0 physical reads
0 redo size
1020925 bytes sent via SQL*Net to client
865349 bytes received via SQL*Net from client
12762 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL
SQLSELECT count(*) from note_detail
2 WHERE CONTAINS(body, 'about(detail)') 0
3 /
COUNT(*)
48
real: 5558
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=417)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NOTE_DETAIL' (Cost=48 Card=1
Bytes=417)
Statistics
5870 recursive calls
4 db block gets
12499 consistent gets
0 physical reads
0 redo size
893457 bytes sent via SQL*Net to client
793021 bytes received via SQL*Net from client
11736 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Received on Thu Dec 30 1999 - 21:57:18 CST
 |
 |