FAQ
Hello,
I'm trying to change the SYNC parameter on already existing
ctxsys.context index without rebuilding the whole thing. The real index
in production is quite big and is currently configured to be sync(on
commit). This setting causes some latency when inserting en-masse.
Regarding to Oracle documentation, it's stated that only one session can
sync at a time, this serialize concurrent commits.

To relax things up, we want to change it to automatic sync at regular
intervals of, say 15, minutes.

I drove some tests to see what happens to the index configs when
modifying only the SYNC metadata.
SQL> -- Create original index
SQL> create index i_ft_qstr_titre
on questionnaire (titre_questionnaire)
indextype is ctxsys.context parameters('LEXER ICU_LEXER_QSTR_TITRE
WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON
COMMIT)')
Index created.
-- Show info
SQL> select idx_name, idx_status, idx_type, idx_sync_type,
idx_sync_interval, idx_sync_jobname
from ctx_user_indexes
where idx_name like 'I_FT_QSTR_TITRE'

IDX_NAME IDX_STATUS IDX_TYPE
IDX_SYNC_TYPE IDX_SYNC_INTERV IDX_SYNC_JOBNAME
------------------------- ------------------------- ----------
--------------- --------------- -------------------------
I_FT_QSTR_TITRE INDEXED CONTEXT ON COMMIT

SQL> select index_name, parameters
from user_indexes
where index_name like 'I_FT_QSTR_TITRE'

INDEX_NAME PARAMETERS
-------------------------
------------------------------------------------------------------------------------------------------------------------
I_FT_QSTR_TITRE *LEXER ICU_LEXER_QSTR_TITRE WORDLIST
ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)*

-- Alter SYNC parameter via ALTER INDEX
-- SYNTAX : ALTER INDEX index_name PARAMETERS (paramstring)
SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA
SYNC(EVERY "SYSDATE+15/1440")')
Index altered.

-- Show info
SQL> select idx_name, idx_status, idx_type, idx_sync_type,
idx_sync_interval, idx_sync_jobname
from ctx_user_indexes
where idx_name like 'I_FT_QSTR_TITRE'

IDX_NAME IDX_STATUS IDX_TYPE
IDX_SYNC_TYPE IDX_SYNC_INTERV IDX_SYNC_JOBNAME
------------------------- ------------------------- ----------
--------------- --------------- -------------------------
I_FT_QSTR_TITRE INDEXED CONTEXT
AUTOMATIC SYSDATE+15/1440 DR$I_FT_QSTR_TITRE$J

SQL> select index_name, parameters
from user_indexes
where index_name like 'I_FT_QSTR_TITRE'

INDEX_NAME PARAMETERS
-------------------------
------------------------------------------------------------------------------------------------------------------------
I_FT_QSTR_TITRE *REPLACE METADATA SYNC(EVERY "SYSDATE+15/1440")*

As you can see, it replaces the whole PARAMETERS column returned by
user_indexes view rather than changing only the sync metadata part.
Is it normal?
Does it negatively change other crucial parameters like the wordlist,
lexer, stoplist, etc.?
Why dows the «REPLACE METADATA» is now present in PARAMETERS?

I know it looks like this, but should I use instead an alter index with
the whole original parameters string?
/alter index i_ft_qstr_titre parameters ('REPLACE METADATA LEXER
ICU_LEXER_QSTR_TITRE WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST
CTXSYS.EMPTY_STOPLIST *SYNC(EVERY "SYSDATE+15/1440")*');/

New output from queries:
SQL> select idx_name, idx_status, idx_type, idx_sync_type,
idx_sync_interval, idx_sync_jobname
from ctx_user_indexes
where idx_name like 'I_FT_QSTR_TITRE'

IDX_NAME IDX_STATUS IDX_TYPE
IDX_SYNC_TYPE IDX_SYNC_INTERV IDX_SYNC_JOBNAME
------------------------- ------------------------- ----------
--------------- --------------- -------------------------
I_FT_QSTR_TITRE INDEXED CONTEXT
AUTOMATIC SYSDATE+1/24 DR$I_FT_QSTR_TITRE$J

SQL> select index_name, parameters
from user_indexes
where index_name like 'I_FT_QSTR_TITRE'

INDEX_NAME PARAMETERS
-------------------------
------------------------------------------------------------------------------------------------------------------------
I_FT_QSTR_TITRE REPLACE METADATA LEXER ICU_LEXER_QSTR_TITRE
WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST
SYNC(EVERY "SYSDATE+1/24")

*ENV Details:*
Oracle Database 10g Enterprise Edition Release 10.2.
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Product
NLSRTL Version 10.2.0.5.0 - Production

Thanks in advance
Bruno Lavoie
bruno.lavoie@gmail.com
bl@brunol.com

Search Discussions

  • Manuela Atoui at Nov 4, 2011 at 8:45 am
    Dear Mr. Lavoi, dear list members,

    I'm trying to answer you questions in three different paragraphs:

    1. - alter index...replace metadata:
    Your statement is correct according to the cited documentation below:
    SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA
    SYNC(EVERY "SYSDATE+15/1440")')

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#i996850

    alter index... does the same as ctx_ddl.replace_metadata
    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cddlpkg.htm#sthref1605

    Do not use the whole original parameter string.
    <quote>
    Note: parameter_string

    Specify the parameter string to be passed to ALTER INDEX. This
    must begin with 'REPLACE METADATA'.
    </quote>

    - For manual Oracle Text synchronisation, schedule a job every n
    minutes (allows you maximum flexibility, you can always change the
    interval)
    exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
    (MANUAL)');

    - or schedule hard coded, e.g. every 5 minutes
    exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
    (every "SYSDATE+5/1440")');

    2. to check index health use ctx_report.describe index. This procedure
    also shows you the current setting of the index metadata.
    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/crptpkg.htm#sthref1960

    3. To get a clearer picture of the needed interval for index
    synchronisations, query the view ctx_pending:

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/aviews.htm#sthref2637

    Hope that helps, if you have further questions you can also send me an
    email directly.

    Kind regards

    Manuela Atoui
    Expert Data Systems s.a.r.l.
    CTO, Senior Oracle DBA

    Berytech Technological Pole
    Mar Roukoz, Mkalles, Beirut, Lebanon
    +961 4 533 040 - +961 76 730 406
    Email: manuela@ed-sys.eu
    www.ed-sys.eu
    On Thu, Nov 3, 2011 at 8:41 PM, Bruno Lavoie wrote:
    Hello,
    I'm trying to change the SYNC parameter on already existing
    ctxsys.context index without rebuilding the whole thing. The real index
    in production is quite big and is currently configured to be sync(on
    commit). This setting causes some latency when inserting en-masse.
    Regarding to Oracle documentation, it's stated that only one session can
    sync at a time, this serialize concurrent commits.

    To relax things up, we want to change it to automatic sync at regular
    intervals of, say 15, minutes.

    I drove some tests to see what happens to the index configs when
    modifying only the SYNC metadata.
    SQL> -- Create original index
    SQL> create index i_ft_qstr_titre
    on questionnaire (titre_questionnaire)
    indextype is ctxsys.context parameters('LEXER ICU_LEXER_QSTR_TITRE
    WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON
    COMMIT)')
    Index created.
    -- Show info
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME                  IDX_STATUS                IDX_TYPE
    IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE           INDEXED                   CONTEXT    ON COMMIT

    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME                PARAMETERS
    -------------------------
    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE *LEXER ICU_LEXER_QSTR_TITRE WORDLIST
    ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)*

    -- Alter SYNC parameter via ALTER INDEX
    -- SYNTAX : ALTER INDEX index_name PARAMETERS (paramstring)
    SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA
    SYNC(EVERY "SYSDATE+15/1440")')
    Index altered.

    -- Show info
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME                  IDX_STATUS                IDX_TYPE
    IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE           INDEXED                   CONTEXT
    AUTOMATIC       SYSDATE+15/1440 DR$I_FT_QSTR_TITRE$J

    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME                PARAMETERS
    -------------------------
    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE *REPLACE METADATA SYNC(EVERY "SYSDATE+15/1440")*

    As you can see, it replaces the whole PARAMETERS column returned by
    user_indexes view rather than changing only the sync metadata part.
    Is it normal?
    Does it negatively change other crucial parameters like the wordlist,
    lexer, stoplist, etc.?
    Why dows the «REPLACE METADATA» is now present in PARAMETERS?

    I know it looks like this, but should I use instead an alter index with
    the whole original parameters string?
    /alter index i_ft_qstr_titre parameters ('REPLACE METADATA LEXER
    ICU_LEXER_QSTR_TITRE WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST
    CTXSYS.EMPTY_STOPLIST *SYNC(EVERY "SYSDATE+15/1440")*');/

    New output from queries:
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME                  IDX_STATUS                IDX_TYPE
    IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE           INDEXED                   CONTEXT
    AUTOMATIC       SYSDATE+1/24    DR$I_FT_QSTR_TITRE$J

    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME                PARAMETERS
    -------------------------
    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE           REPLACE METADATA LEXER ICU_LEXER_QSTR_TITRE
    WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST
    SYNC(EVERY  "SYSDATE+1/24")

    *ENV Details:*
    Oracle Database 10g Enterprise Edition Release 10.2.
    PL/SQL Release 10.2.0.5.0 - Production
    CORE    10.2.0.5.0      Production
    TNS for 64-bit Windows: Version 10.2.0.5.0 - Product
    NLSRTL Version 10.2.0.5.0 - Production

    Thanks in advance
    Bruno Lavoie
    bruno.lavoie@gmail.com
    bl@brunol.com



    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Manuela Atoui at Nov 4, 2011 at 4:33 pm
    Dear Mr. Lavoie, dear list members,

    in a nutshell, user_indexes is not updated when you change the Oracle
    text index parameters, please see test case below.

    0. create a test user for Oracle Text
    $ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 4 17:53:24 2011

    Copyright (c) 1982, 2009, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> create user textuser identified by textuser
    default tablespace users
    temporary tablespace temp; 2 3

    User created.

    SQL> grant connect, resource, ctxapp to textuser;

    Grant succeeded.

    1. set some configuration values in Oracle Text
    SQL> conn textuser/textuser
    Connected.
    SQL> set timing on
    SQL>set serveroutput on size 1000000
    SQL> exec ctx_ddl.create_preference('stem_preference', 'BASIC_WORDLIST');
    exec ctx_ddl.set_attribute('stem_preference', 'STEMMER', 'DERIVATIONAL');


    Elapsed: 00:00:00.02
    SQL>
    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.02
    SQL>
    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.01

    2. create a test table and an Oracle context index with synchronize 'on commit'
    SQL> create table quick(
    quick_id number
    constraint quick_pk primary key,
    text varchar2(80)
    ); 2 3 4 5

    Table created.

    Elapsed: 00:00:00.02
    SQL> create index quick_text on quick(text)
    indextype is ctxsys.context
    parameters ('wordlist stem_preference sync(on commit)') ; 2 3

    Index created.

    Elapsed: 00:00:00.14

    3. insert one row, commit ==> Oracle Text index gets automatically synchronised
    SQL> insert into quick (quick_id, text)
    values(1, 'english stem test for african nation'); 2

    1 row created.

    Elapsed: 00:00:00.04
    SQL> commit;

    Commit complete.

    Elapsed: 00:00:00.04

    4. Check the setting 'synchronise on commit' with Oracle Text view
    ctx_user_indexes, user_indexes and the procedure
    ctx_report.describe_index:
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'QUICK_TEXT'; 2 3 4

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------
    QUICK_TEXT
    INDEXED CONTEXT
    ON COMMIT

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------




    Elapsed: 00:00:00.01
    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'QUICK_TEXT'; 2 3

    INDEX_NAME
    --------------------------------------------------------------------------------
    PARAMETERS
    --------------------------------------------------------------------------------
    QUICK_TEXT
    wordlist stem_preference sync(on commit)


    Elapsed: 00:00:00.01
    SQL> set long 64000
    SQL> set pages 0
    SQL> set heading off
    SQL> set feedback off
    SQL> spool outputfile
    SQL> select ctx_report.describe_index('quick_text') from dual;
    ===========================================================================
    INDEX DESCRIPTION
    ===========================================================
    ================
    index name: "TEXTUSER"."QUICK_TEXT"
    index id: 1106
    index type: context

    base table: "TEXTUSER"."QUICK"
    primary key column: QUICK_ID
    text column: TEXT
    text column type: VARCHAR2(80)
    language column:
    format column:
    charset column:
    Query Stats Enabled: NO
    sync type: on commit


    status: INDEXED
    full optimize token:
    full optimize count:
    docid count: 1
    nextid: 2

    ===================================================================
    ========
    INDEX OBJECTS
    ===========================================================================
    datastore: DIRECT_DATASTORE

    filter: NULL_FILTER

    section group: NULL_SECTION_GROUP

    lexer: BASIC_LEXER

    wordlist: BASIC_WORDLIST
    stemmer: DERIVATIONAL

    stoplist: BASIC_STOPLIST
    stop_word: Mr
    .....
    stop_word: yours

    storage: BASIC_STORAGE
    r_table_clause: lob (data) store as (cache)

    i_index_clause: compress 2



    Elapsed: 00:00:00.03
    SQL> spool off
    SQL>

    Conclusion: ctx_user_indexes, user_indexes and
    ctx_report.describe_index all report for column parameters 'on
    commit'.

    ==========================================================
    5. let's change the sync behavior with CTX_DDL.REPLACE_INDEX_METADATA

    SQL> set serveroutput on
    SQL> exec CTX_DDL.REPLACE_INDEX_METADATA('quick_text','REPLACE
    METADATA SYNC(MANUAL)');

    PL/SQL procedure successfully completed.

    6. Check again the current setting with 'synchronise manual' with
    Oracle Text view ctx_user_indexes, user_indexes and the procedure
    ctx_report.describe_index:
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'QUICK_TEXT'; 2 3 4

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------
    QUICK_TEXT
    INDEXED CONTEXT
    MANUAL

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------

    Conclusion: ctx_user_indexes reflects the accurate current setting
    with 'synchronise manual'


    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'QUICK_TEXT'; 2 3

    INDEX_NAME
    --------------------------------------------------------------------------------
    PARAMETERS
    --------------------------------------------------------------------------------
    QUICK_TEXT
    wordlist stem_preference sync(on commit) <--- not updated !!!

    Conclusion: user_indexes still reports the old, now wrong parameter
    'sync on commit' !!

    SQL> set long 64000
    set pages 0
    set heading off
    set feedback off
    spool outputfile
    select ctx_report.describe_index('quick_text') from dual;
    spool offSQL> SQL> SQL> SQL> SQL>
    ===========================================================================
    INDEX DESCRIPTION
    ===========================================================
    ================
    index name: "TEXTUSER"."QUICK_TEXT"
    index id: 1106
    index type: context

    base table: "TEXTUSER"."QUICK"
    primary key column: QUICK_ID
    text column: TEXT
    text column type: VARCHAR2(80)
    language column:
    format column:
    charset column:
    Query Stats Enabled: NO
    sync type: manual


    status: INDEXED
    full optimize token:
    full optimize count:
    docid count: 1
    nextid: 2

    ======================================================================
    =====
    INDEX OBJECTS
    =====================================================
    ======================
    datastore: DIRECT_DATASTORE

    filter: NULL_FILTER

    section group: NULL_SECTION_GROUP

    lexer: BASIC_LEXER

    wordlist: BASIC_WORDLIST
    stemmer: DERIVATIONAL

    stoplist: BASIC_STOPLIST
    stop_word: Mr
    ....
    stop_word: yours

    storage: BASIC_STORAGE
    r_table_clause: lob (data) store as (cache)
    i_index_clause: compress 2

    Conclusion:
    ctx_user_indexes, and ctx_report.describe_index all report for column
    parameters 'on commit' BUT user_indexes is not updated, the value in
    parameter is still 'on commit'.


    Kind regards

    Manuela Atoui
    Expert Data Systems s.a.r.l.
    CTO, Senior Oracle DBA

    Berytech Technological Pole
    Mar Roukoz, Mkalles, Beirut, Lebanon
    +961 4 533 040 - +961 76 730 406
    Email: manuela@ed-sys.eu
    www.ed-sys.eu
    On Fri, Nov 4, 2011 at 2:26 PM, Bruno Lavoie wrote:
    Thanks, see my comments bellow.

    Le 2011-11-04 04:44, Manuela Atoui a écrit :
    Dear Mr. Lavoi, dear list members,

    I'm trying to answer you questions in three different paragraphs:

    1. - alter index...replace metadata:
    Your statement is correct according to the cited documentation below:
    SQL>  alter index i_ft_qstr_titre parameters ('REPLACE METADATA
    SYNC(EVERY "SYSDATE+15/1440")')


    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#i996850

    alter index... does the same as ctx_ddl.replace_metadata

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cddlpkg.htm#sthref1605

    Do not use the whole original parameter string.
    <quote>
    Note: parameter_string

    Specify the parameter string to be passed to ALTER INDEX. This
    must begin with 'REPLACE METADATA'.
    </quote>
    So why «do not user the whole parameter strings», is it normal to see my
    complete parameters list disapear from user_indexes.parameters field?
    In case of export, I presume that it will not transport all the necessary
    index settings.

    I've also read that ctx_ddl.replace_metadata is not the preferred way as
    opposed to alter index statement.

    Briefly, what is bugging in my mind is that user_indexes.parameters is not
    accurate...
    - For manual Oracle Text synchronisation, schedule a job every n
    minutes (allows you maximum flexibility, you can always change the
    interval)
    exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
    (MANUAL)');

    - or schedule hard coded, e.g. every 5 minutes
    exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
    (every "SYSDATE+5/1440")');

    2. to check index health use ctx_report.describe index. This procedure
    also shows you the current setting of the index metadata.

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/crptpkg.htm#sthref1960

    3. To get a clearer picture of the needed interval for index
    synchronisations, query the view ctx_pending:


    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/aviews.htm#sthref2637

    Hope that helps, if you have further questions you can also send me an
    email directly.

    Kind regards

    Manuela Atoui
    Expert Data Systems s.a.r.l.
    CTO, Senior Oracle DBA

    Berytech Technological Pole
    Mar Roukoz, Mkalles, Beirut, Lebanon
    +961 4 533 040 - +961 76 730 406
    Email: manuela@ed-sys.eu
    www.ed-sys.eu

    On Thu, Nov 3, 2011 at 8:41 PM, Bruno Lavoie<bruno.lavoie@gmail.com>
    wrote:
    Hello,
    I'm trying to change the SYNC parameter on already existing
    ctxsys.context index without rebuilding the whole thing. The real index
    in production is quite big and is currently configured to be sync(on
    commit). This setting causes some latency when inserting en-masse.
    Regarding to Oracle documentation, it's stated that only one session can
    sync at a time, this serialize concurrent commits.

    To relax things up, we want to change it to automatic sync at regular
    intervals of, say 15, minutes.

    I drove some tests to see what happens to the index configs when
    modifying only the SYNC metadata.
    SQL>  -- Create original index
    SQL>  create index i_ft_qstr_titre
    on questionnaire (titre_questionnaire)
    indextype is ctxsys.context parameters('LEXER ICU_LEXER_QSTR_TITRE
    WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON
    COMMIT)')
    Index created.
    -- Show info
    SQL>  select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME                  IDX_STATUS                IDX_TYPE
    IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE           INDEXED                   CONTEXT    ON COMMIT

    SQL>  select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME                PARAMETERS
    -------------------------

    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE *LEXER ICU_LEXER_QSTR_TITRE WORDLIST
    ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)*

    -- Alter SYNC parameter via ALTER INDEX
    -- SYNTAX : ALTER INDEX index_name PARAMETERS (paramstring)
    SQL>  alter index i_ft_qstr_titre parameters ('REPLACE METADATA
    SYNC(EVERY "SYSDATE+15/1440")')
    Index altered.

    -- Show info
    SQL>  select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME                  IDX_STATUS                IDX_TYPE
    IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE           INDEXED                   CONTEXT
    AUTOMATIC       SYSDATE+15/1440 DR$I_FT_QSTR_TITRE$J

    SQL>  select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME                PARAMETERS
    -------------------------

    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE *REPLACE METADATA SYNC(EVERY "SYSDATE+15/1440")*

    As you can see, it replaces the whole PARAMETERS column returned by
    user_indexes view rather than changing only the sync metadata part.
    Is it normal?
    Does it negatively change other crucial parameters like the wordlist,
    lexer, stoplist, etc.?
    Why dows the «REPLACE METADATA» is now present in PARAMETERS?

    I know it looks like this, but should I use instead an alter index with
    the whole original parameters string?
    /alter index i_ft_qstr_titre parameters ('REPLACE METADATA LEXER
    ICU_LEXER_QSTR_TITRE WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST
    CTXSYS.EMPTY_STOPLIST *SYNC(EVERY "SYSDATE+15/1440")*');/

    New output from queries:
    SQL>  select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME                  IDX_STATUS                IDX_TYPE
    IDX_SYNC_TYPE   IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE           INDEXED                   CONTEXT
    AUTOMATIC       SYSDATE+1/24    DR$I_FT_QSTR_TITRE$J

    SQL>  select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME                PARAMETERS
    -------------------------

    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE           REPLACE METADATA LEXER ICU_LEXER_QSTR_TITRE
    WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST
    SYNC(EVERY  "SYSDATE+1/24")

    *ENV Details:*
    Oracle Database 10g Enterprise Edition Release 10.2.
    PL/SQL Release 10.2.0.5.0 - Production
    CORE    10.2.0.5.0      Production
    TNS for 64-bit Windows: Version 10.2.0.5.0 - Product
    NLSRTL Version 10.2.0.5.0 - Production

    Thanks in advance
    Bruno Lavoie
    bruno.lavoie@gmail.com
    bl@brunol.com



    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Bruno Lavoie at Nov 4, 2011 at 7:09 pm
    Thanks to your Mr Atoui,

    It seems that ALTER INDEX clause modify the user_indexes, but that your
    technique with CTX_DDL.REPLACE_INDEX_METADATA replaces only things in
    Oracle Text metadata & dictionary.

    The pending question is: what happens when we need to export a schema
    containing a modified text index?
    Does export (expdp, dbms_metadata) handle well these incoherent changes?
    I would like to know what is happening with dbms_metadata and expdp
    respectively, prevent rather than fixing.
    If someone know the answer it is good, but if not i'll test it and get
    back about it. Maybe I need to search a bit about it on net and MOS.

    Many Thanks
    Bruno Lavoie



    Le 2011-11-04 12:31, Manuela Atoui a écrit :
    Dear Mr. Lavoie, dear list members,

    in a nutshell, user_indexes is not updated when you change the Oracle
    text index parameters, please see test case below.

    0. create a test user for Oracle Text
    $ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 4 17:53:24 2011

    Copyright (c) 1982, 2009, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> create user textuser identified by textuser
    default tablespace users
    temporary tablespace temp; 2 3

    User created.

    SQL> grant connect, resource, ctxapp to textuser;

    Grant succeeded.

    1. set some configuration values in Oracle Text
    SQL> conn textuser/textuser
    Connected.
    SQL> set timing on
    SQL>set serveroutput on size 1000000
    SQL> exec ctx_ddl.create_preference('stem_preference', 'BASIC_WORDLIST');
    exec ctx_ddl.set_attribute('stem_preference', 'STEMMER', 'DERIVATIONAL');


    Elapsed: 00:00:00.02
    SQL>
    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.02
    SQL>
    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.01

    2. create a test table and an Oracle context index with synchronize 'on commit'
    SQL> create table quick(
    quick_id number
    constraint quick_pk primary key,
    text varchar2(80)
    ); 2 3 4 5

    Table created.

    Elapsed: 00:00:00.02
    SQL> create index quick_text on quick(text)
    indextype is ctxsys.context
    parameters ('wordlist stem_preference sync(on commit)') ; 2 3

    Index created.

    Elapsed: 00:00:00.14

    3. insert one row, commit ==> Oracle Text index gets automatically synchronised
    SQL> insert into quick (quick_id, text)
    values(1, 'english stem test for african nation'); 2

    1 row created.

    Elapsed: 00:00:00.04
    SQL> commit;

    Commit complete.

    Elapsed: 00:00:00.04

    4. Check the setting 'synchronise on commit' with Oracle Text view
    ctx_user_indexes, user_indexes and the procedure
    ctx_report.describe_index:
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'QUICK_TEXT'; 2 3 4

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------
    QUICK_TEXT
    INDEXED CONTEXT
    ON COMMIT

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------




    Elapsed: 00:00:00.01
    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'QUICK_TEXT'; 2 3

    INDEX_NAME
    --------------------------------------------------------------------------------
    PARAMETERS
    --------------------------------------------------------------------------------
    QUICK_TEXT
    wordlist stem_preference sync(on commit)


    Elapsed: 00:00:00.01
    SQL> set long 64000
    SQL> set pages 0
    SQL> set heading off
    SQL> set feedback off
    SQL> spool outputfile
    SQL> select ctx_report.describe_index('quick_text') from dual;
    ===========================================================================
    INDEX DESCRIPTION
    ===========================================================
    ================
    index name: "TEXTUSER"."QUICK_TEXT"
    index id: 1106
    index type: context

    base table: "TEXTUSER"."QUICK"
    primary key column: QUICK_ID
    text column: TEXT
    text column type: VARCHAR2(80)
    language column:
    format column:
    charset column:
    Query Stats Enabled: NO
    sync type: on commit


    status: INDEXED
    full optimize token:
    full optimize count:
    docid count: 1
    nextid: 2

    ===================================================================
    ========
    INDEX OBJECTS
    ===========================================================================
    datastore: DIRECT_DATASTORE

    filter: NULL_FILTER

    section group: NULL_SECTION_GROUP

    lexer: BASIC_LEXER

    wordlist: BASIC_WORDLIST
    stemmer: DERIVATIONAL

    stoplist: BASIC_STOPLIST
    stop_word: Mr
    .....
    stop_word: yours

    storage: BASIC_STORAGE
    r_table_clause: lob (data) store as (cache)

    i_index_clause: compress 2



    Elapsed: 00:00:00.03
    SQL> spool off
    SQL>

    Conclusion: ctx_user_indexes, user_indexes and
    ctx_report.describe_index all report for column parameters 'on
    commit'.

    ==========================================================
    5. let's change the sync behavior with CTX_DDL.REPLACE_INDEX_METADATA

    SQL> set serveroutput on
    SQL> exec CTX_DDL.REPLACE_INDEX_METADATA('quick_text','REPLACE
    METADATA SYNC(MANUAL)');

    PL/SQL procedure successfully completed.

    6. Check again the current setting with 'synchronise manual' with
    Oracle Text view ctx_user_indexes, user_indexes and the procedure
    ctx_report.describe_index:
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'QUICK_TEXT'; 2 3 4

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------
    QUICK_TEXT
    INDEXED CONTEXT
    MANUAL

    IDX_NAME
    --------------------------------------------------------------------------------
    IDX_STATUS IDX_TYPE
    ------------------------------------ ---------------------
    IDX_SYNC_TYPE
    ------------------------------------------------------------
    IDX_SYNC_INTERVAL
    --------------------------------------------------------------------------------
    IDX_SYNC_JOBNAME
    --------------------------------------------------------------------------------

    Conclusion: ctx_user_indexes reflects the accurate current setting
    with 'synchronise manual'


    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'QUICK_TEXT'; 2 3

    INDEX_NAME
    --------------------------------------------------------------------------------
    PARAMETERS
    --------------------------------------------------------------------------------
    QUICK_TEXT
    wordlist stem_preference sync(on commit)<--- not updated !!!

    Conclusion: user_indexes still reports the old, now wrong parameter
    'sync on commit' !!

    SQL> set long 64000
    set pages 0
    set heading off
    set feedback off
    spool outputfile
    select ctx_report.describe_index('quick_text') from dual;
    spool offSQL> SQL> SQL> SQL> SQL>
    ===========================================================================
    INDEX DESCRIPTION
    ===========================================================
    ================
    index name: "TEXTUSER"."QUICK_TEXT"
    index id: 1106
    index type: context

    base table: "TEXTUSER"."QUICK"
    primary key column: QUICK_ID
    text column: TEXT
    text column type: VARCHAR2(80)
    language column:
    format column:
    charset column:
    Query Stats Enabled: NO
    sync type: manual


    status: INDEXED
    full optimize token:
    full optimize count:
    docid count: 1
    nextid: 2

    ======================================================================
    =====
    INDEX OBJECTS
    =====================================================
    ======================
    datastore: DIRECT_DATASTORE

    filter: NULL_FILTER

    section group: NULL_SECTION_GROUP

    lexer: BASIC_LEXER

    wordlist: BASIC_WORDLIST
    stemmer: DERIVATIONAL

    stoplist: BASIC_STOPLIST
    stop_word: Mr
    ....
    stop_word: yours

    storage: BASIC_STORAGE
    r_table_clause: lob (data) store as (cache)
    i_index_clause: compress 2

    Conclusion:
    ctx_user_indexes, and ctx_report.describe_index all report for column
    parameters 'on commit' BUT user_indexes is not updated, the value in
    parameter is still 'on commit'.


    Kind regards

    Manuela Atoui
    Expert Data Systems s.a.r.l.
    CTO, Senior Oracle DBA

    Berytech Technological Pole
    Mar Roukoz, Mkalles, Beirut, Lebanon
    +961 4 533 040 - +961 76 730 406
    Email: manuela@ed-sys.eu
    www.ed-sys.eu

    On Fri, Nov 4, 2011 at 2:26 PM, Bruno Lavoiewrote:
    Thanks, see my comments bellow.

    Le 2011-11-04 04:44, Manuela Atoui a écrit :
    Dear Mr. Lavoi, dear list members,

    I'm trying to answer you questions in three different paragraphs:

    1. - alter index...replace metadata:
    Your statement is correct according to the cited documentation below:
    SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA
    SYNC(EVERY "SYSDATE+15/1440")')


    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#i996850

    alter index... does the same as ctx_ddl.replace_metadata

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/cddlpkg.htm#sthref1605

    Do not use the whole original parameter string.
    <quote>
    Note: parameter_string

    Specify the parameter string to be passed to ALTER INDEX. This
    must begin with 'REPLACE METADATA'.
    </quote>
    So why «do not user the whole parameter strings», is it normal to see my
    complete parameters list disapear from user_indexes.parameters field?
    In case of export, I presume that it will not transport all the necessary
    index settings.

    I've also read that ctx_ddl.replace_metadata is not the preferred way as
    opposed to alter index statement.

    Briefly, what is bugging in my mind is that user_indexes.parameters is not
    accurate...
    - For manual Oracle Text synchronisation, schedule a job every n
    minutes (allows you maximum flexibility, you can always change the
    interval)
    exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
    (MANUAL)');

    - or schedule hard coded, e.g. every 5 minutes
    exec CTX_DDL.REPLACE_INDEX_METADATA('idx_name','REPLACE METADATA SYNC
    (every "SYSDATE+5/1440")');

    2. to check index health use ctx_report.describe index. This procedure
    also shows you the current setting of the index metadata.

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/crptpkg.htm#sthref1960

    3. To get a clearer picture of the needed interval for index
    synchronisations, query the view ctx_pending:


    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/aviews.htm#sthref2637

    Hope that helps, if you have further questions you can also send me an
    email directly.

    Kind regards

    Manuela Atoui
    Expert Data Systems s.a.r.l.
    CTO, Senior Oracle DBA

    Berytech Technological Pole
    Mar Roukoz, Mkalles, Beirut, Lebanon
    +961 4 533 040 - +961 76 730 406
    Email: manuela@ed-sys.eu
    www.ed-sys.eu

    On Thu, Nov 3, 2011 at 8:41 PM, Bruno Lavoie<bruno.lavoie@gmail.com>
    wrote:
    Hello,
    I'm trying to change the SYNC parameter on already existing
    ctxsys.context index without rebuilding the whole thing. The real index
    in production is quite big and is currently configured to be sync(on
    commit). This setting causes some latency when inserting en-masse.
    Regarding to Oracle documentation, it's stated that only one session can
    sync at a time, this serialize concurrent commits.

    To relax things up, we want to change it to automatic sync at regular
    intervals of, say 15, minutes.

    I drove some tests to see what happens to the index configs when
    modifying only the SYNC metadata.
    SQL> -- Create original index
    SQL> create index i_ft_qstr_titre
    on questionnaire (titre_questionnaire)
    indextype is ctxsys.context parameters('LEXER ICU_LEXER_QSTR_TITRE
    WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON
    COMMIT)')
    Index created.
    -- Show info
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME IDX_STATUS IDX_TYPE
    IDX_SYNC_TYPE IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE INDEXED CONTEXT ON COMMIT

    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME PARAMETERS
    -------------------------

    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE *LEXER ICU_LEXER_QSTR_TITRE WORDLIST
    ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)*

    -- Alter SYNC parameter via ALTER INDEX
    -- SYNTAX : ALTER INDEX index_name PARAMETERS (paramstring)
    SQL> alter index i_ft_qstr_titre parameters ('REPLACE METADATA
    SYNC(EVERY "SYSDATE+15/1440")')
    Index altered.

    -- Show info
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME IDX_STATUS IDX_TYPE
    IDX_SYNC_TYPE IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE INDEXED CONTEXT
    AUTOMATIC SYSDATE+15/1440 DR$I_FT_QSTR_TITRE$J

    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME PARAMETERS
    -------------------------

    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE *REPLACE METADATA SYNC(EVERY "SYSDATE+15/1440")*

    As you can see, it replaces the whole PARAMETERS column returned by
    user_indexes view rather than changing only the sync metadata part.
    Is it normal?
    Does it negatively change other crucial parameters like the wordlist,
    lexer, stoplist, etc.?
    Why dows the «REPLACE METADATA» is now present in PARAMETERS?

    I know it looks like this, but should I use instead an alter index with
    the whole original parameters string?
    /alter index i_ft_qstr_titre parameters ('REPLACE METADATA LEXER
    ICU_LEXER_QSTR_TITRE WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST
    CTXSYS.EMPTY_STOPLIST *SYNC(EVERY "SYSDATE+15/1440")*');/

    New output from queries:
    SQL> select idx_name, idx_status, idx_type, idx_sync_type,
    idx_sync_interval, idx_sync_jobname
    from ctx_user_indexes
    where idx_name like 'I_FT_QSTR_TITRE'

    IDX_NAME IDX_STATUS IDX_TYPE
    IDX_SYNC_TYPE IDX_SYNC_INTERV IDX_SYNC_JOBNAME
    ------------------------- ------------------------- ----------
    --------------- --------------- -------------------------
    I_FT_QSTR_TITRE INDEXED CONTEXT
    AUTOMATIC SYSDATE+1/24 DR$I_FT_QSTR_TITRE$J

    SQL> select index_name, parameters
    from user_indexes
    where index_name like 'I_FT_QSTR_TITRE'

    INDEX_NAME PARAMETERS
    -------------------------

    ------------------------------------------------------------------------------------------------------------------------
    I_FT_QSTR_TITRE REPLACE METADATA LEXER ICU_LEXER_QSTR_TITRE
    WORDLIST ICU_WORDLIST_QSTR_TITRE STOPLIST CTXSYS.EMPTY_STOPLIST
    SYNC(EVERY "SYSDATE+1/24")

    *ENV Details:*
    Oracle Database 10g Enterprise Edition Release 10.2.
    PL/SQL Release 10.2.0.5.0 - Production
    CORE 10.2.0.5.0 Production
    TNS for 64-bit Windows: Version 10.2.0.5.0 - Product
    NLSRTL Version 10.2.0.5.0 - Production

    Thanks in advance
    Bruno Lavoie
    bruno.lavoie@gmail.com
    bl@brunol.com



    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 3, '11 at 6:49p
activeNov 4, '11 at 7:09p
posts4
users2
websiteoracle.com

2 users in discussion

Bruno Lavoie: 2 posts Manuela Atoui: 2 posts

People

Translate

site design / logo © 2022 Grokbase