[Oracle-l] ctx_ddl.sync_index question

Vendors aren't responsible for your system -- YOU are. Push back to them
with questions and requests for documentation.

Then again, there's no dev/test system -- is this a critical production
system that can't sustain downtime? Can you create a copy of this database
to test the effects of the index sync as well as possible performance tweaks
(e.g. increasing the 12M index memory iteratively between test system
restores or using the parallel option of the sync_index)?

I could have sworn from a past life that the creation of a CTX index
automagically created a DBMS_JOB to sync the index in 10gR2, but there's no
way for me to test this now. [insert malted hops / brain cell disclaimer
here]

Finally, make sure you can restore exactly to the point before you run this
resync. Whether that be database, external data, whatever -- do what you
must to ensure that you can start over again if it goes wrong. There's all
sorts of business decisions on that one that no one outside your company can
ultimately make for you.

GL! Let us know how it goes!

Rich

Michael,



This simply keeps the index in sync. So inserts into the table are
added to the context index by this job.



We run a cron job to do this on a nightly basis for some of our
databases.



We have a COTS application that creates indexes like this:



create index {index_name} on {table_name}({column_name}) indextype is
CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)');



Note the 'SYNC ON COMMIT' option. According to the docs, this keeps the
index in sync at commit time. I'm not sure how this performs, but we
are just starting out with it.

Hope this helps.

Tom





From: oracle-l-bounce_at_freelists.org
On Behalf Of Michael McMullen
Sent: Wednesday, April 08, 2009 10:25 AM
To: 'Oracle L'
Subject: ctx_ddl.sync_index question



10.2.0.4

Redhat linux

2 cpu

12 GB ram

COTS app: kana



Kana is telling our developer that we need to run

begin

ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;

to resync the text index.

The index is 2.5GB and there are 3 million records in ctx_user_pending.
Of course, there is no dev/test or anything and they want to run it
straight on prod. Why the app doesn't have a job predefined to do this I
don't know. I'm trying to get a handle on what this will actually do
under the covers. This will be run off peak load but is it so resource
intensive that the system will essentially lock (from the users
viewpoint) or any idea of how long this type of thing would take.

I've told them we will just have to go with it as again we have no way
of testing beforehand.



Thanks

Mike

Thomas,

With sync on commit, only 1 session at a time can sync (see note 249991.1 and oracle docu:
http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#sthref197), the other sessions will have to wait.
So if you have a high concurrenty of sessions inserting into a table with a text index, the sync on commit can give performance
problems due to locking.

Regards,


Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer

We have a COTS application that creates indexes like this:

create index {index_name} on {table_name}({column_name}) indextype is CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)');

'12M' seems to be quite low. That is the memory allocation for the
index synchronization.
Values of 50M and 100M and higher wouldn't be unreasonable.

Ensure that you have a job to sync the index frequently.

Monitor the count of pending updates by doing a count on CTX_USER_PENDING;
Hemant K Chitale

Hi!

Surelly 12M seems to be pretty low...

check out:
http://www.oracle.com/technology/products/text/htdocs/Index_Maintenance.html

Also maybe you have big redo logs alreasy but I suggest to check and increase if needed... because sync gives you lots of usage.

Also remember that no other operations(ALTER INDEX... or optimize_index) on text index when sync or optimizing is running.... or you might get negative effect.

-Teijo
'12M' seems to be quite low. That is the memory allocation for the
index synchronization.
Values of 50M and 100M and higher wouldn't be unreasonable.

Ensure that you have a job to sync the index frequently.

Monitor the count of pending updates by doing a count on CTX_USER_PENDING;
Hemant K Chitale

Mike,

Others had excellent answers. I want to point out the locks taken during the sync_index operation. My earlier test (in 10.2.0.1) shows that "All TM locks are in mode 3. So there shouldn't be conflict with DML." See
http://yong321.freeshell.org/oranotes/TextIndex.txt

If the index were ctxsys.ctxcat type, you wouldn't need to run sync_index. But ctxsys.context type does.

Yong Huang