OraFAQ Forum: Text & interMedia » Performance issues and options to reduce load with Oracle text implementation

Home » Server Options » Text & interMedia » Performance issues and options to reduce load with Oracle text implementation (Oracle 11.2.0.2 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance issues and options to reduce load with Oracle text implementation [message #615796]

Mon, 09 June 2014 06:10

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi Experts,

My database on Oracle 11.2.0.2 on Linux. We have Oracle Text implemented for fuzzy search. Our oracle text indexes are defined as sync on commit as we can not afford to have stale data. Now our application does literally thousands of inserts/updates/deletes to those columns where we have these Oracle text indexes defined. As a result, we are seeing a lot of performance impact due to the oracle text sync routines being called on each commit. We are doing the index optimization every night (full optimization every night at 3 am). The oracle text index related internal operations are showing up as top sql in our AWR report and there are concerns that it is causing lot of load on the DB. Since we do the full index optimization only once at night, I am thinking should I change that , and if I do so, will it help us?

For example here are some data from my one day's AWR report -in the uploaded file - awr.gif. The Oracle text internal procedures are taking as much as 20% of the DB time in the AWR report.

Now if I do the full index optimization more often and not just once at night 3 PM, will that mean, the load on DB due to sync on commit will decrease? If yes how often should I run the optimize procedure and doesn't the optimization itself lead to some load? Can someone suggest?

Note that there is another thread on this in OTN forum at this link: https://community.oracle.com/message/12474670
One expert has suggested to increase the memory related to the Oracle Text indexes but I am not sure how to do so and how to measure its impact.

I will be thankful for suggestions on the issue.

Thanks,
./fa/11934/0/

[mod-edit: image inserted into message body by bb]

Attachment: awr.gif
(Size: 260.15KB, Downloaded 5373 times)

[Updated on: Mon, 09 June 2014 08:06] by Moderator

Report message to a moderator

[Message index]

		Performance issues and options to reduce load with Oracle text implementation By: orausern on Mon, 09 June 2014 06:10
		Re: Performance issues and options to reduce load with Oracle text implementation By: Barbara Boehmer on Mon, 09 June 2014 12:43
		Re: Performance issues and options to reduce load with Oracle text implementation By: orausern on Mon, 09 June 2014 13:17
		Re: Performance issues and options to reduce load with Oracle text implementation By: Barbara Boehmer on Mon, 09 June 2014 18:04

Previous Topic:	Text Index returns column name as well
Next Topic:	CTXSYS fuzzy searching

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 30 09:49:48 CDT 2024