Home » Server Options » Text & interMedia » optimizing iFS text query
optimizing iFS text query [message #110252] Fri, 04 March 2005 11:41 Go to next message
swarris
Messages: 3
Registered: March 2005
Junior Member
L.S.

We've been using Oracle 9i for quite some time now. But after a year the java iFS text became very slow. As the database grew the performance fell significantly, up to a point where an avarage query resulted in a database time out...
We started analysing the iFS text query and much to our suprise, the text index is fast enough:
select /*+ INDEX(ODMZ_CONTEXT_ROUTER IFS_TEXT) */
 count(*)
from ODMZ_CONTEXT_ROUTER
where
  CTXSYS.CONTAINS(ODMZ_CONTEXT_ROUTER.CONTENTPROCEDURE,'search text', 1) > 0;

usually returns in less than a second.

Further analyses revealed that the default iFS text query uses many views on many table, although only a few tables where needed for the result. These views where the real cause of the performance degradation. We've managed to remove the views and added optimizer hints:
select /*+ INDEX(ODMZ_CONTEXT_ROUTER IFS_TEXT) USE_HASH(SC1 SC0 p a) */
 distinct SC0.id, CTXSYS.SCORE(1)/(0.3*log(10, SC1.contentsize)) score
from ODMZ_CONTEXT_ROUTER, odm_contentobject SC1, odm_document SC0
 join odm_relationship r on (SC0.id = r.rightobject)
  join
   (select fi.rightid
     from odmz_folderindex fi
     where fi.lev = 0 and fi.leftid in
      (select fi2.rightid
        from odmz_folderindex fi2
        where fi2.lev = 1 and fi2.leftid =36128
      )
    union
    select fi.rightid
     from odmz_folderindex fi
     where fi.lev = 1 and fi.leftid =36128
   ) f
  on (f.rightid = r.leftobject), odm_publicobject p, odmz_acl_discoverer a, odmz_class_hierarchy ch
 where
  CTXSYS.CONTAINS(ODMZ_CONTEXT_ROUTER.CONTENTPROCEDURE,'search text', 1) > 0 AND
  ODMZ_CONTEXT_ROUTER.id = SC1.content AND
  SC0.CONTENTOBJECT = SC1.ID AND
  SC0.id = p.id AND
  p.acl = a.aclid AND
  ((p.owner =1074 and a.userid = 0) OR ( p.owner != 1074 and a.userid = 1) OR ( p.owner != 1074 and a.userid = 1074)) AND
  p.deletor = 0 AND
  p.classid = ch.subclassid and
  ch.classid=182209
ORDER BY score DESC;


Still, in some cases this query takes about 24 seconds to complete (of which 1.2 seconds is spend on the text index).
The text query results in 9000 documents, whereas the overal result contains about 5000 documents...

Can anybody help us with increasing the performance of this query?? Again, the database keeps growing...

Thanx,
Sven

Re: optimizing iFS text query [message #115364 is a reply to message #110252] Sun, 10 April 2005 18:01 Go to previous messageGo to next message
rhardman
Messages: 25
Registered: April 2005
Junior Member
Have you traced this? Login to Metalink and query for SQLT. It is a handy utility. Just save your query as a .txt file, and with sqlt files and the .txt file in the same directory, run SQLTXPLAIN.SQL with your .txt file as an argument. Take a look at the cost of each line of the execution plan, and examine the rest of the file. Once you do this I'd wager the problem will jump out at you.

There are other ways to get the same info as SQLT, but personally, I like this utility. Carlos Sierra (the author of the scripts) did a good job. It also pulls your stats to a table called SQLT$STATTAB. You can export the table, and import to another instance, and reproduce the same plan...sound like a feature of Data Pump?? It works good for 8i and 9i though...
Re: optimizing iFS text query [message #115436 is a reply to message #115364] Mon, 11 April 2005 11:29 Go to previous message
swarris
Messages: 3
Registered: March 2005
Junior Member
Thank you for the suggestion, I will give it a try and hopefully this tool will produce some nice results.

A few days back our database administrator let us know the statistics for the index optimizer could not be calculated, because some tables exceeded the memory capacity of the server... The optimizer had been using (very) old statistics for optimalization. The database has been reconfigured to use parts of the data for the stats and after this the performance made a huge step forward. But not enough: the query still takes several seconds.



Previous Topic: Enable InterMedia option - replacement script for dr0inst.sql in 10g?
Next Topic: SYNC INDEX runs forever
Goto Forum:
  


Current Time: Thu Mar 28 06:17:27 CDT 2024