I am on Oracle 22.214.171.124 on Linux. I have implemented Oracle text in my database. My production dba was telling me today that there are some locking issues in production and he has sent me an ASH report. I can post that ASH in a seperate email thread, but first in this post wanted to ask that are there any known problems with Oracle text that it causes locks or locking related waits? I read somewhere that oracle text indexes get defragmented over time. My text indexes are defined as sync on commit. Do they, for example, need to be rebuilt over time - by say dropping and recreating them? What should be the frequency of doing this?
I will be most thankful for any pointer on this.
Thank you for your help as allways on Oracle Text! Can you also please review my further questions:
1) These oracle text indexes are in production. so how best to optimize them? I mean can or should it be done in online way - like "alter index <index_name> rebuild online" - is that preferred way to do this?
2) Or is it better to drop them and create them? also when we drop them, it can cause application error (since application queries depend on these). So how to schedule that? I mean do we need to get the app servers down and then drop and rebuild these indexes? I am looking for a practical way to do this in production. ( i mean we will go from dev. to prod. but ultimately what matters is production and that is where we have to do this repeatedly. So what is the best way to do that.
Thanks a lot
Per the documentation, "ONLINE Enables you to continue to perform updates, insertions, and deletions on a base table. It does not enable you to query the base table."
What is best depends on your situation. If your situation is such that there is no dml or queries on weekends or nights or some such thing, then you can drop and recreate at those times. If input is done 24/7, but queries are only done in the daytime, then you might want to rebuild online at night. You need to figure out what method will give you the least downtime of a sort that is tolerable at the best time. You may wish to optimize frequently and rebuild or recreate less often.
One last point: about this thing you said: "optimize frequently and rebuild or recreate less often." I did not understand it. Can you explain more about what you mean by optimize frequently and recreate less often? I understood optimize to mean the same as recreate. I mean as we recreate so index is defragmented so it is optimized right? or is it different?Can you please explain.
There are various ways to optimize, some fast and partial and some slower and thorough. Please see the optimize_index procedure of the ctx_ddl package:
The optimization type and frequency can be set in the index parameters, just as you do with sync(on commit), or scheduled using dbms_job or dbms_scheduler.
Thank you. i am thinking to go in this way:
1) Set up a dbms_scheduler job to optimize the index once every two weeks. It should be at night 1 am or so where hopefully the activities are less (but I guess selects and DMLs may still continue).
2) Have another once a month manual process (not automated) , in which I will ask our other groups to shut down the app servers and then drop and recreate the oracle text indexes. this is to be done once in a month.
Thanks a lot for the excellent help.