Performance text index
Date: Tue, 28 Oct 2008 01:20:40 -0700 (PDT)
Message-ID: <c6d40666-62b2-44b9-a018-e321f5805220@h60g2000hsg.googlegroups.com>
Hi,
We have a a materialized view containing about 7 million records at
the moment with song artist and album information. We hava a column in
the materialized wiev which contains the song name concateneted with
the artistname concateneted with the album name. We have a textindex
on this column. Searching on 'Elvis' or 'Elvis Presley' and similiar
is very fast. However doing a search on 'in the ghettto' or 'on in
the'. will produce a slow result (more than 10 seconds). I understand
that there are a lot of hits with words like 'in' and 'the' but we
need to be able to search on these words as well. I have provided the
autotrace information as well as the tkprof. Does anybody have any
suggestions to speed this up.
Thanks,
Sql:
SELECT sub2.*
FROM (
SELECT ROWNUM rad, sub1.*
FROM (
SELECT songId, title, songArtist, albumId,
popularity, genreId, genre, explicit, offerId, priceCategoryId,
offerStartDate, offerEndDate, mountPointId, drmProtected, releaseDate
FROM MV_Song_batman
WHERE CATSEARCH(searchText, :vSearchString, :vCriterion) > 0)
sub1
WHERE ROWNUM <= :vEnd) sub2
WHERE sub2.rad >= :vStart;
Autotrace:
SQL> @catsearch
PL/SQL procedure successfully completed.
30 rows selected.
Execution Plan
Plan hash value: 2829723679
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 150K| 84M| 25900 (1)| 00:05:11 |
|* 1 | VIEW | | 150K| 84M|
25900 (1)| 00:05:11 |
|* 2 | COUNT STOPKEY | | | |
| |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID| MV_SONG_BATMAN | 150K|
27M|
25900 (1)| 00:05:11 |
|* 4 | DOMAIN INDEX | MV_SONG_BATMAN_IND | | |
| |
Predicate Information (identified by operation id):
1 - filter("SUB2"."RAD">=TO_NUMBER(:VSTART))
2 - filter(ROWNUM<=TO_NUMBER(:VEND))
4 -
access("CTXSYS"."CATSEARCH"("SEARCHTEXT",:VSEARCHSTRING,:VCRITERION)>0)
Statistics
872 recursive calls
0 db block gets
86385 consistent gets
12 physical reads
0 redo size
3839 bytes sent via SQL*Net to client 392 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 158 sorts (memory)
0 sorts (disk)
30 rows processed
TKProf:
TKPROF: Release 10.2.0.1.0 - Production on Tue Oct 28 08:30:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: orcl_ora_4668.trc
Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
alter session set events '10046 trace name context forever, level 12'
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00 ********************************************************************************
begin
:vend := 30; :vStart := 1; :vSearchstring := 'in the ghetto'; :vCriterion := 'countrycode = ''SE'' order by sortorder';end;
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.39 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ----------
total 2 0.00 0.39 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00 ********************************************************************************
SELECT sub2.*
FROM (
SELECT ROWNUM rad, sub1.*
FROM (
SELECT songId, title, songArtist, albumId,
popularity, genreId, genre, explicit, offerId, priceCategoryId,
offerStartDate, offerEndDate, mountPointId, drmProtected, releaseDate
FROM MV_Song_batman
WHERE CATSEARCH(searchText, :vSearchString, :vCriterion) > 0)
sub1
WHERE ROWNUM <= :vEnd) sub2
WHERE sub2.rad >= :vStart
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.25 0 18 0 0 Fetch 3 0.09 0.16 0 18 0 30------- ------ -------- ---------- ---------- ---------- ----------
total 5 0.10 0.41 0 36 0 30
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244
Rows Row Source Operation
------- ---------------------------------------------------30 VIEW (cr=86072 pr=0 pw=0 time=14336154 us) 30 COUNT STOPKEY (cr=86072 pr=0 pw=0 time=14336124 us) 30 MAT_VIEW ACCESS BY INDEX ROWID MV_SONG_BATMAN (cr=86072 pr=0 pw=0 time=14336103 us)
30 DOMAIN INDEX MV_SONG_BATMAN_IND (cr=86054 pr=0 pw=0 time=14336280 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 0.37 0.67 ********************************************************************************
select metadata
from
kopm$ where name='DB_FDO'
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1------- ------ -------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=47 us) 1 INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=20 us)(object id 365)
select /*+ NO_EXPAND INDEX_ASC(i "DR$MV_SONG_BATMAN_IND03") */ dr
$rowid ,
SORTORDER
from
"MEDIA_CORE"."DR$MV_SONG_BATMAN_IND$I" i where dr$token = :token and
dr$token_type = :ttype and COUNTRYCODE = 'SE' order by dr$token ASC,
dr$token_type ASC, SORTORDER ASC, dr$rowid ASC
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.04 0 0 0 0 Fetch 4 0.35 0.25 0 2485 0 396------- ------ -------- ---------- ---------- ---------- ----------
total 6 0.35 0.30 0 2485 0 396
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------396 SORT ORDER BY (cr=2485 pr=0 pw=0 time=257922 us) 65277 TABLE ACCESS BY INDEX ROWID DR$MV_SONG_BATMAN_IND$I (cr=2485 pr=0 pw=0 time=486792 us)
261491 INDEX RANGE SCAN DR$MV_SONG_BATMAN_IND03 (cr=978 pr=0 pw=0 time=1054100 us)(object id 63590)
select /*+ NO_EXPAND INDEX_ASC(i "DR$MV_SONG_BATMAN_IND03") */ dr
$rowid ,
SORTORDER
from
"MEDIA_CORE"."DR$MV_SONG_BATMAN_IND$I" i where dr$token = :token and
dr$token_type = :ttype and COUNTRYCODE = 'SE' and SORTORDER >= :r01
and
(SORTORDER > :r01 or DR$ROWID >= :r02) order by dr$token ASC,
dr$token_type ASC, SORTORDER ASC, dr$rowid ASC
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 157 0.00 0.00 0 0 0 0 Execute 157 0.01 0.21 0 0 0 0 Fetch 439 16.48 13.60 0 83549 0 43460------- ------ -------- ---------- ---------- ---------- ----------
total 753 16.50 13.82 0 83549 0 43460
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 244 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------198 SORT ORDER BY (cr=2058 pr=0 pw=0 time=331363 us) 210971 TABLE ACCESS BY INDEX ROWID DR$MV_SONG_BATMAN_IND$I (cr=2058 pr=0 pw=0 time=3375647 us)
210971 INDEX RANGE SCAN DR$MV_SONG_BATMAN_IND03 (cr=799 pr=0 pw=0 time=850588 us)(object id 63590)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 3 0.01 0.65 0 18 0 1 Fetch 3 0.09 0.16 0 18 0 30------- ------ -------- ---------- ---------- ---------- ----------
total 8 0.10 0.81 0 36 0 31
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 8 0.00 0.00 SQL*Net message from client 8 17.24 25.54
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------
Parse 159 0.00 0.00 0 0 0 0 Execute 159 0.01 0.26 0 0 0 0 Fetch 444 16.84 13.86 0 86036 0 43857------- ------ -------- ---------- ---------- ---------- ----------
total 762 16.85 14.13 0 86036 0 43857
Misses in library cache during parse: 0
161 user SQL statements in session.
1 internal SQL statements in session.
162 SQL statements in session.
Trace file: orcl_ora_4668.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
161 user SQL statements in trace file.
1 internal SQL statements in trace file.
162 SQL statements in trace file.
6 unique SQL statements in trace file.
5777 lines in trace file.
23 elapsed seconds in trace file.
Received on Tue Oct 28 2008 - 03:20:40 CDT