Every so often the Indexing seems to hit a wall and we gaet a huge backlog of text files that need to be indexed. We get about 2 to 3 million files per day. Most of the time the problem gets resolved when we increase the degree parallel. How can we identify which text file is causing the problem?
I wonder if the problem is becuse of some bug when the DR$PENDING gets to zero rows. Apparently this causes the query on CTX_USER_PENDING to take a long time. I use that view to determine the partitions to pass to SYNC_INDEX procedure.
where pnd_index_name = '<index_name>'
This takes about 5 seconds for tow rows with 335 and 13 rows.
Tables DR$INDEX, DR$INDEX_PARTITION and DR$PENDING have had Statistics gathered
This query has taken over 45 minutes.
As an aside, we went back from parallel 8/16 to 1 and for now the sync index works a lot better. We are also "loading" at 1000 rows per cycle (10 seconds). Dont see any TX: row contention waits either.
But would still like to know which exact row is currently being processed by SYNC_INDEX.