FAQ
10.2.0.4

Redhat linux

2 cpu

12 GB ram

COTS app: kana



Kana is telling our developer that we need to run

begin

ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;

to resync the text index.

The index is 2.5GB and there are 3 million records in ctx_user_pending. Of
course, there is no dev/test or anything and they want to run it straight on
prod. Why the app doesn't have a job predefined to do this I don't know. I'm
trying to get a handle on what this will actually do under the covers. This
will be run off peak load but is it so resource intensive that the system
will essentially lock (from the users viewpoint) or any idea of how long
this type of thing would take.

I've told them we will just have to go with it as again we have no way of
testing beforehand.



Thanks

Mike

Search Discussions

  • Rich Jesse at Apr 8, 2009 at 2:41 pm
    Vendors aren't responsible for your system -- YOU are. Push back to them
    with questions and requests for documentation.

    Then again, there's no dev/test system -- is this a critical production
    system that can't sustain downtime? Can you create a copy of this database
    to test the effects of the index sync as well as possible performance tweaks
    (e.g. increasing the 12M index memory iteratively between test system
    restores or using the parallel option of the sync_index)?

    I could have sworn from a past life that the creation of a CTX index
    automagically created a DBMS_JOB to sync the index in 10gR2, but there's no
    way for me to test this now. [insert malted hops / brain cell disclaimer
    here]

    Finally, make sure you can restore exactly to the point before you run this
    resync. Whether that be database, external data, whatever -- do what you
    must to ensure that you can start over again if it goes wrong. There's all
    sorts of business decisions on that one that no one outside your company can
    ultimately make for you.

    GL! Let us know how it goes!

    Rich
    10.2.0.4

    Redhat linux

    2 cpu

    12 GB ram

    COTS app: kana



    Kana is telling our developer that we need to run

    begin

    ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;

    to resync the text index.

    The index is 2.5GB and there are 3 million records in ctx_user_pending. Of
    course, there is no dev/test or anything and they want to run it straight on
    prod. Why the app doesn't have a job predefined to do this I don't know. I'm
    trying to get a handle on what this will actually do under the covers. This
    will be run off peak load but is it so resource intensive that the system
    will essentially lock (from the users viewpoint) or any idea of how long
    this type of thing would take.

    I've told them we will just have to go with it as again we have no way of
    testing beforehand.
    --
    http://www.freelists.org/webpage/oracle-l
  • Anonymous at Apr 8, 2009 at 3:37 pm
    Michael,



    This simply keeps the index in sync. So inserts into the table are
    added to the context index by this job.



    We run a cron job to do this on a nightly basis for some of our
    databases.



    We have a COTS application that creates indexes like this:



    create index {index_name} on {table_name}({column_name}) indextype is
    CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)');



    Note the 'SYNC ON COMMIT' option. According to the docs, this keeps the
    index in sync at commit time. I'm not sure how this performs, but we
    are just starting out with it.

    Hope this helps.

    Tom





    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Michael McMullen
    Sent: Wednesday, April 08, 2009 10:25 AM
    To: 'Oracle L'
    Subject: ctx_ddl.sync_index question



    10.2.0.4

    Redhat linux

    2 cpu

    12 GB ram

    COTS app: kana



    Kana is telling our developer that we need to run

    begin

    ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;

    to resync the text index.

    The index is 2.5GB and there are 3 million records in ctx_user_pending.
    Of course, there is no dev/test or anything and they want to run it
    straight on prod. Why the app doesn't have a job predefined to do this I
    don't know. I'm trying to get a handle on what this will actually do
    under the covers. This will be run off peak load but is it so resource
    intensive that the system will essentially lock (from the users
    viewpoint) or any idea of how long this type of thing would take.

    I've told them we will just have to go with it as again we have no way
    of testing beforehand.



    Thanks

    Mike
  • freek D'Hooge at Apr 8, 2009 at 11:31 pm
    Thomas,

    With sync on commit, only 1 session at a time can sync (see note 249991.1 and oracle docu:
    http://download.oracle.com/docs/cd/B19306_01/text.102/b14218/csql.htm#sthref197), the other sessions will have to wait.
    So if you have a high concurrenty of sessions inserting into a table with a text index, the sync on commit can give performance
    problems due to locking.

    Regards,


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer

    We have a COTS application that creates indexes like this:

    create index {index_name} on {table_name}({column_name}) indextype is CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)');
  • Hemant K Chitale at Apr 8, 2009 at 3:51 pm
    '12M' seems to be quite low. That is the memory allocation for the
    index synchronization.
    Values of 50M and 100M and higher wouldn't be unreasonable.

    Ensure that you have a job to sync the index frequently.

    Monitor the count of pending updates by doing a count on CTX_USER_PENDING;
    At 10:25 PM Wednesday, you wrote:
    10.2.0.4
    Redhat linux
    2 cpu
    12 GB ram
    COTS app: kana

    Kana is telling our developer that we need to run
    begin
    ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;
    to resync the text index.


    Hemant K Chitale
  • Teijo Lallukka at Apr 8, 2009 at 5:12 pm
    Hi!

    Surelly 12M seems to be pretty low...

    check out:
    http://www.oracle.com/technology/products/text/htdocs/Index_Maintenance.html

    Also maybe you have big redo logs alreasy but I suggest to check and increase if needed... because sync gives you lots of usage.

    Also remember that no other operations(ALTER INDEX... or optimize_index) on text index when sync or optimizing is running.... or you might get negative effect.

    -Teijo
    Hemant K Chitale 04/08/09 6:51 pm >>>
    '12M' seems to be quite low. That is the memory allocation for the
    index synchronization.
    Values of 50M and 100M and higher wouldn't be unreasonable.

    Ensure that you have a job to sync the index frequently.

    Monitor the count of pending updates by doing a count on CTX_USER_PENDING;
    At 10:25 PM Wednesday, you wrote:
    10.2.0.4
    Redhat linux
    2 cpu
    12 GB ram
    COTS app: kana

    Kana is telling our developer that we need to run
    begin
    ctx_ddl.sync_index('kc_ctx_rawtext', '12M'); end;
    to resync the text index.


    Hemant K Chitale
  • Yong Huang at Apr 9, 2009 at 5:42 pm

    is it so resource intensive that the system
    will essentially lock (from the users viewpoint)
    Mike,

    Others had excellent answers. I want to point out the locks taken during the sync_index operation. My earlier test (in 10.2.0.1) shows that "All TM locks are in mode 3. So there shouldn't be conflict with DML." See
    http://yong321.freeshell.org/oranotes/TextIndex.txt

    If the index were ctxsys.ctxcat type, you wouldn't need to run sync_index. But ctxsys.context type does.

    Yong Huang

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 8, '09 at 2:25p
activeApr 9, '09 at 5:42p
posts7
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase