Home » Server Options » Text & interMedia » Oracle Text Search Performance issue with frequent words
Oracle Text Search Performance issue with frequent words [message #622930] Tue, 02 September 2014 03:00 Go to next message
ramesh.sannapureddy
Messages: 5
Registered: June 2014
Location: Bangalore
Junior Member
Hi,

We are using the oracle text search with Context index and contains function in the query. But when we search for the keywords which occur so frequently is taking more time to give the results. But if we execute the query with same words second time it is performing well.
We are using the query template and progression with sequence to order the results.
We are using, FIRST_ROWS hint in the query, gathering stats on the table. Query is getting generated dynamically based on the user input to say in which columns the input words have to be found, so it is little tough for us to use bind variables in the query. But ready to add the bind the variables if it improves the performance so much.

Please let me know whether we can do some additional configuration to improve the performance.

More info:
We refresh the data in search table with some frequency like thrice a day using a scheduled procedure. So is it good to execute some queries with frequent words so that those words get cached in the DB or will be used while deciding the execution plan.

Thanks In advance for your help.

Regards,
Ramesh.S

Report message to a moderator

Re: Oracle Text Search Performance issue with frequent words [message #622932 is a reply to message #622930] Tue, 02 September 2014 03:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Your question is about Oracle text, so I have moved it to Text and interMedia forum.

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

If your question is specific about performance, please read How to tune SQL or Identify Performance Problem and Bottleneck and provide the required details.

Report message to a moderator

Re: Oracle Text Search Performance issue with frequent words [message #622975 is a reply to message #622930] Tue, 02 September 2014 15:27 Go to previous message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
The reason that the same query runs faster the second time is because it can re-use the query in the SGA without hard parsing it again. If you use bind variables, then the same query with any values for the bind variables runs as fast the first time as running the query the second time. With the bind variables, Oracle sees it as the same query, no matter what the variable values are, and uses the query in the SGA, without having to hard parse it again. You need to structure your query so that it is the same query each time, but with different values for the bind variables.

Report message to a moderator

Previous Topic: like performance than CATSEARCH Performance incase of wild character
Next Topic: Index creation performance problem with LOB/CLOB content
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 06:28:10 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.