6 Replies Latest reply: Jun 19, 2014 1:59 PM by kevinUCB RSS

    Extremely slow syncing of substring index

    user109389

      Oracle 11.2.0.2 on RedHat Linux x64 (5.7)

       

      I created a partitioned CONTEXT index in a NOPOPULATE state.  The index is defined on a CLOB column of a partitioned table using a NULL_FILTER and SUBSTRING attribute.  I then used the following procedure to populate the pending records for indexing.

       

      BEGIN

        CTX_DDL.POPULATE_PENDING (

              idx_name    => 'CONTENT_VALUE_IDX',

              part_name    => 'P_2012_12'

        );

      END;

      /

       

      This procedure seemed to run for an appropriate amount of time, but ended with the following error:

       

      ORA-20000: Oracle Text error:

      DRG-50857: Oracle error in drvddl.ProcessOnlinePending

      ORA-20000: Oracle Text error:

      DRG-50857: Oracle error in textindexmethods.ODCIIndexDelete

      ORA-20000: Oracle Text error:

      DRG-10607: index meta data is not ready yet for queuing DML

      DRG-50857: Oracle error in drdmlpo

      ORA-20000: Oracle Text error:

      DRG-10502: index 1766 does not exist

      ORA-06512: at "CTXSYS.DRUE", line 160

      ORA-06512: at "CTXSYS.DRVXMD", line 42

      ORA-06512: at line 1

      ORA-30576: ConText Option dictionary loading error

      ORA-06512: at "CTXSYS.DRUE", line 160

      ORA-06512: at "CTXSYS.CTX_DDL", line 1366

      ORA-06512: at line 2

       

      However, the count of records that were populated in the DR$PENDING corresponded to the same number of records in the table that is having the index built on it.  So, I am hoping this error can be ignored??

       

      I then try to sync the pending rows with the following:

       

      BEGIN

        CTX_OUTPUT.START_LOG('content_value.log');

      END;

      /

       

      BEGIN

        CTX_DDL.SYNC_INDEX (

              idx_name    => 'CONTENT_VALUE_IDX',

              memory        => '2000M',

              part_name    => 'P_2012_12',

              parallel_degree    => 16

        );

      END;

      /

       

      The process starts as parallel 16 and the logs show the documents being indexed.  After about an hour or two, the log then shows "Writing index data ($I) to database." and then appears to process the substring data as the log shows "Writing substring data ($P) to database."  This is the point that the parallel process start to go serially.  Contention starts to show in the database.  The blocking session indicates a wait event of "db file sequential read" and the sessions that start to queue up have a wait state of "enq: TX - row lock contention". 

       

      The supporting $P TEXT table is being populated and rows are being removed from the DR$PENDING table, so the process is performing work.  The partition that was being indexed only has 27million rows, however the process ran for a couple WEEKS before I finally killed the process.

       

      There is a note on Metalink (Parallel and Serial Text SYNC_INDEX is Slow when SUBSTRING_INDEX is Enabled (Doc ID 840097.1)) that seems to address this same scenario, but mentions that the problem should be resolved in 11.2.0.2.

       

      Other non-SUBSTRING TEXT indexes sync without any problems.  The "Oracle Text" option is valid in the database and none of CTXSYS's objects are INVALID.

       

      Does anyone have any ideas?