Forum Stats

  • 3,814,069 Users
  • 2,258,814 Discussions
  • 7,892,523 Comments

Discussions

ctxsys.syncrn runs and slows down data load on table having Text index

user12085962
user12085962 Member Posts: 1
edited Sep 17, 2019 6:50PM in Text

We are on this Oracle version:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.7.0.0.0

Our use case:

Every morning we are loading a lot of data into a table. The data is loaded from a powershell script that has 6 database connection that run in parallel,

We do not need the Oracle Text index to be synchronized before all data has been loaded into the table.

However, during data load, this statement can be seen to run after a transaction has added rows to the table:

   ctxsys.syncrn( :idxownid,

                 :idxoname,

                 :idxid,

                 :ixpid,

                 :rtabnm,

                 :flg,

                 :smallr);

The statement  ctxsys.syncrn... runs for several minutes and increases the total load time from 1 to 4 hours.

How can we avoid  ctxsys.syncrn from being run?

We only need  CTX_DDL.SYNC_INDEX(......) to run after all data has been loaded into the table.

Our text index was created like this:

CREATE INDEX MY_ALL_TEXT_IDX

   ON MY_ALL_TEXT(TEXT)

   INDEXTYPE IS CTXSYS.CONTEXT

LOCAL (

  PARTITION EU,               

  PARTITION US,

  PARTITION DEF)

NOPARALLEL;

Answers

  • Ebalthes-Oracle
    Ebalthes-Oracle Member Posts: 107 Employee
    edited Sep 17, 2019 4:39PM

    Was this index re-created after upgrade to 18.7.0 or is this from a previous DB-version ?

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Sep 17, 2019 6:50PM

    SYNCRN is unrelated to SYNC_INDEX.

    SYNCRN runs during the internal commit callback, and is responsible for syncing the R and N tables. The heaviest load is usually on the $R table, especially when several processes are commiting in parallel.

    The first thing to determine is if your index has a $R table. If newly created in 18c, it won't have one, but if upgraded from a previous version it will.

    Assuming you do have a $R table, you have a couple of options:

    1/ Recreating the index from scratch will create it without a $R table.

    2/ You can convert an existing index to use the SMALL_R_ROW option. That reduces contention on the $R table during updates, though it won't be as effective as the first option.  To get the script to upgrade an existing index you'll need to contact support, or email me direct (shouldn't be too hard to find my email address - Oracle email addresses take the form [email protected]).

    fmonaldi65