Hi gents,
I've problem. Look at this:
Table TEXT_TAB is simple table with inline BLOB column:
create table text_tab (doc_id number, text blob);
create or replace function hit_result (p_id in numeric, p_query in varchar2,
p_idx in varchar2) return varchar2 is
v_clob_selected CLOB;
v_read_amount integer;
v_read_offset integer;
v_buffer varchar2(32767);
v_quantity constant integer := 256; -- How mach bytes returns
restab CTX_DOC.HIGHLIGHT_TAB;
begin
dbms_lob.createtemporary(v_clob_selected, true, dbms_lob.session);
ctx_doc.markup (p_idx, p_id, p_query, v_clob_selected, true,
'TEXT_DEFAULT', '<b><font color=red>', '</font></b>');
ctx_doc.highlight(p_idx, p_id, p_query, restab => restab, plaintext => true);
v_read_amount := restab(1).length + v_quantity;
v_read_offset := restab(1).offset;
begin
dbms_lob.read(v_clob_selected, v_read_amount, v_read_offset, v_buffer);
exception
when no_data_found then null;
end;
dbms_lob.freetemporary(v_clob_selected);
return v_buffer; -- Show result highlighted portion of data
exception
when others then
dbms_lob.freetemporary(v_clob_selected);
return null;
end hr_hit_result;
/
SELECT /*+ FIRST_ROWS(15) DOMAIN_INDEX_NO_SORT */
SCORE(1), doc_id, hit_result(cv_id, '&P29_SEARCH.', 'TXT_CTX_I') as "Text"
FROM text_tab
WHERE CONTAINS(text, '&P29_SEARCH.', 1) > 0;
SQL> SELECT /*+ FIRST_ROWS(15) DOMAIN_INDEX_NO_SORT */
2 SCORE(1)||'%' as "Hit%",
3 doc_id,
4 '<img src="#IMAGE_PREFIX#arrow.gif" border="0">
5 <img src="#IMAGE_PREFIX#arrow.gif" border="0"> '||
6 hit_result(doc_id, '&P29_SEARCH.', 'TXT_CTX_I') as "Text"
7 FROM text_tab
8 WHERE CONTAINS(text, '&P29_SEARCH.', 1) > 0;
Enter value for p29_search: oracle
old 8: WHERE CONTAINS(text, '&P29_SEARCH.', 1) > 0
new 8: WHERE CONTAINS(text, 'oracle', 1) > 0
Elapsed: 00:00:07.90
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=4 B
ytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEXT_TAB' (Cost=2 Card=
4 Bytes=120)
2 1 DOMAIN INDEX OF 'TXT_CTX_I' (Cost=0)
Statistics
----------------------------------------------------------
1749 recursive calls
2912 db block gets
2049 consistent gets
32 physical reads
0 redo size
726 bytes sent via SQL*Net to client
249 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> ed
Wrote file afiedt.buf
1 SELECT /*+ FIRST_ROWS(15) DOMAIN_INDEX_NO_SORT */
2 SCORE(1)||'%' as "Hit%",
3 doc_id,
4 FROM text_tab
5* WHERE CONTAINS(text, '&P29_SEARCH.', 1) > 0
SQL> /
Enter value for p29_search: oracle
new 5: WHERE CONTAINS(text, 'oracle', 1) > 0
Hit% DOC_ID
----------------------------------------- ----------
100% 66
100% 77
100% 57
100% 79
Elapsed: 00:00:02.80
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=4 B
ytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEXT_TAB' (Cost=2 Card=
4 Bytes=120)
2 1 DOMAIN INDEX OF 'TXT_CTX_I' (Cost=0)
Statistics
----------------------------------------------------------
194 recursive calls
0 db block gets
430 consistent gets
0 physical reads
0 redo size
338 bytes sent via SQL*Net to client
248 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
As you can see, CTX_DOC.MARKUP presence in function is a performance issue. This example is not the top - another query (table contains only 15 documents by 400-500 Kbytes every) runs 10 minutes on box with SPARC-II 500 MHz CPU and 1,5 Gb RAM.
report.txt showing absolutely good DB, also OS and database already optimized.
Whats happens with MARKUP?