1) We have an XMLTYPE column on a table TABLE1, on which a transactional context index is defined (as well as an XML domain index).
2) We defined a job which calls CTX_DDL.SYNC_INDEX, and scheduled the job to run every 20 minutes for a max of 10 minutes.
Recently the sync job was stopped. We then noticed that the following procedure:
begin ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg); end;
was locking a number of tables like DR$PENDING, DR$WAITING, DR$DELETE but was also locking TABLE1, the domain index path table and a number of TABLE1's foreign-key tables.
I've found it difficult thus far to find out more about "CTXSYS.SYNCRN" - is it responsible for placing updates onto the pending queue? Any information would be greatly appreciated.
Also, if the sync job is not run and the XMLTYPE continues to be updated, will the performance of these updates deteriorate - as the pending updates continue to be made but the sync job is not running (effectively to drain the queue)?
Don't quite understand that, I thought CTXSYS.SYNCRN was the internal function which implemented CTX_DDL.SYNC_INDEX.
But you're saying that you're not running SYNCs at all?
Can you check the parameters used in creating the index? Are you sure you don't have SYNC(ON COMMIT) ?
As an aside, it's pretty dangerous to use the TRANSACTIONAL keyword without SYNC(ON COMMIT), or at least very regular syncs. The TRANSACTIONAL keyword requires queries to scan through all committed but not-yet-synced records as part of the query. If there are more than a few such records, it can make your queries run really slowly.