Hi,
I'm having a situation that I don't know what to do anymore ...
What we have:
- We have Oracle 11.2.0.0.0 and context index on one table with 5 columns..
- We have a .NET application that is executing SELECT queries for 550 customers with bind variables for 6000 times a day...
- Some queries have more then 550 bound variables but there is maybe 1-2 like that big .... otherwise bellow 100
- we have set a JOB for index optimization for FULL mode, gather stats like this :
exec ctx_ddl.optimize_index('ORATEXT_ART_IDX','FULL');
exec DBMS_stats.gather_index_stats(ownname=>TEST,indname=>'ORATEXT_ART_IDX');
exec DBMS_stats.gather_table_stats('TEST','S_ARTICLE',cascade=>TRUE);
This is the code for index:
BEGIN
CTX_DDL.CREATE_PREFERENCE('S_ARTICLE_LEX','BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE('S_ARTICLE_LEX','SKIPJOINS','+&-');
CTX_DDL.CREATE_PREFERENCE('DATASTORE_S_ARTICLE','MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE(' DATASTORE_S_ARTICLE ','columns','TITLE,SUBTITLE,AUTHOR,SUMMARY,FULLTEXT');
END;
CREATE INDEX ORATEXT_ART_IDX ON S_ARTICLE(ORATEXT) INDEXTYPE IS CTXSYS.CONTEXT
FILTER BY ID
PARAMETERS (' LEXER S_ARTICLE_LEX STOPLIST CTXSYS.EMPTY_STOPLIST sync(ON COMMIT) DATASTORE DATASTORE_S_ARTICLE');
Scenario is like this :
When ever a new news article comes we :
- insert this ONE article into DB and save it so that the index syncs
- run 550 queries to find out if any of the queries has a match...
- query is of a form : SELECT * FROM ARTICLES WHERE ID=xxxx AND TYPE=xx AND (MEDIA=yy OR MEDIA=yy) AND (CONTAINS(a) OR CONTAINS(b) AND NOT CONTAINS(c) .........) etc ....
- so different types are used but always on this current article ...
- because query always starts with exactly specific ID (the last inserted one) we set FILTER BY ID when creating CONTEXT index ... so ID is always part of a query, but other TYPE,MEDIA,CATEGORY,WORDS are user defined and in user defined way...
-every article has from 10 - 3000 or more words .... like usual newspaper articles ...
So query is always created dynamically upon user criterias ...
Where is our problem ..... When we started the times were at 9-12sec for this search .... after 1 day we are at 30-40sec.....
If we do an index optimization every 3-5 hours then we are at 20-23sec ... but this is not a solution for this ... because times are getting bigger
Our computer is a new I7.. 8core 16GBRam Windows server 2008 ....
The CPU is running all the time between 97-100% when doing search ... and we saw that we have a lot of some sort of WAITs ...
we have increased SGA to 6Gbram... but this didn't help, increased cursors, we have swiched on/off Auto memory management ...
What ever we have done, nothing has helped ...
SQLArea shows that the most running sql is the oracle generated query for Dynamic sampling of the context index ...
Another question would be ... is there any better way to do the query search instead of using Context index ...
would normal search like INSTR be a better way to do the search ?
Thank you.
Kris