Re: ConText Index Maintenance - Optimize Blocking Sync

"at"@" <""caoimhinocrosbai(at\"@)yahoo.com"> wrote in message news:1118218047.df48c5bcfeb62023417123eaea798788_at_teranews...
> Hi,
>
> Sorry for the cross-post, I posted this on comp.databases.oracle.misc
> but I never know which newsgroup to post to for Oracle Text questions.
> This groups seems to be much more active.
>
> I'm using Oracle 9.2.0.5.0.
>
> I have a context index that I am trying to regularly sync and optimize.
> I have started 2 dbms_jobs, one to sync every hour and one to optimize
> at midnight every day.
>
> I have noticed however that the sync is blocked by the optimize,
> optimize holds a lock on the dr$myindex$i table, which sync seems to
> need to wait for.
>
> Reading MetaLink Note:221940.1
> Subject: Technical Overview: Oracle Text version 9.2.0
> "New Sync Locking Mechanism
>
> The locking algorithm has been rewritten in 9.2.0 to lock these rows
> only at the end of indexing. This greatly reduces the window in which
> user access is blocked."
>
> Reading MetaLink Note:111846.1
> Subject:Alter Index Rebuild Online ('sync') [or ('optimize')] Hangs
>
> "But the "sync" and "optimize" algorithms CAN be run
> concurrently - if and only if you invoke them via the PL/SQL api."
>
> Has anybody got any experience/advice for this issue? Is it possible
> to have the two processes running concurrently or should I schedule the
> sync for daytime hours and optimize overnight? (optimimize takes > 8
> hours to run).
>
> Many Thanks,
>
> Kevin

You might try ctxsrv. I know is not the 'supported' way to maintain indexes, but it works Received on Thu Jun 09 2005 - 08:38:16 CDT