Trying to identify why mass deletes from Oracle Text is SLOW
We have some ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg) statements that run for > 28 MINUTES each when purging old data causing severe performance issues on the delete processes. The current rate cannot keep up with data purge requirements. Data is partitioned by day, and to reduce some perceived locking, have converted the $R to small_r_row so a $R lob may take 2-3 rows (490000 bytes max per lob row) We can tell that it does LOBWRITEs in chunks of 6400 bytes with an average write time of .990seconds
Has anyone else experience this?