Forum Stats

  • 3,825,165 Users
  • 2,260,474 Discussions
  • 7,896,430 Comments

Discussions

Oracle 12cR1 CTXSYS.SYNCRN

OTG-467455
OTG-467455 Member Posts: 506 Bronze Badge
edited Nov 22, 2017 7:44AM in Text

During a recent project upgrading a clients Maximo system to IBM Power 8 processors running AIX 7.1 and Oracle 12cR1 we encountered the following issues:

  1. The system would after a restart, that is restarting the Host, AIX, Oracle RDBMS, the Listeners and the Weblogic Java clients, run without a hitch for a while.
  2. Sporadically a query would hang and the Websphere Java cliients would after a while respawn the request.
  3. On investigation it was found that the hanging was as a result of a sync operation on the CTXSYS indexes specifically the SYNCRN.
  • Verify the Indexes
  • Gather detailed stats

None of these seemed to help.

End result the CTX index was dropped and this releived the system of most of the bottleneck around the CTXSYS.SYNCRN.

This is not the solution but rather a temporary work around until the CTX index issue can be resloved.

Has anyone experienced this before and what actions do you propose I take to re-instate the CTX indexes.   These indexes are vitally important for Maximo queries to function properly.

OTG-467455

Best Answer

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2017 6:08AM Answer ✓

    Contention in ctxsys.syncrn is normally caused by multiple processes trying to access the $R table at the same time (DR$indexname$R).

    The best fix for this is to use the SMALL_R_ROW storage attribute. In 12.1.0.1 and 12.1.0.2 this is hidden under an event, and needs to be enabled by using:

    alter session set events '30579 trace name context forever, level 268435456';

    OTG-467455

Answers

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    edited Nov 6, 2017 12:04AM

    You may need to provide more information regarding the problem....

    However - something else to consider - are you sure the index is the problem, and not concurrency issues with many statements (deletes,updates,inserts,selects) using this index at the same time?

    OTG-467455
  • OTG-467455
    OTG-467455 Member Posts: 506 Bronze Badge
    edited Nov 6, 2017 3:50PM

    The transaction volume is high, but the occurrence of this issue is intermittent.  I will dig into my files and try post more details as you suggest.

  • OTG-467455
    OTG-467455 Member Posts: 506 Bronze Badge
    edited Nov 7, 2017 3:23PM

    Amazing how documents seem to vanish when you need it.   Regarding the concurrency issues with many statements.  This was looked into but the problem did not always occur during peak periods.  The odd thing about this was that when the CTX index was removed a lot of the performance issues went away.

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,442 Red Diamond
    edited Nov 8, 2017 1:02AM

    ***Moderator action (Timo): moved from to for better alignment***

    OTG-467455
  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2017 6:08AM Answer ✓

    Contention in ctxsys.syncrn is normally caused by multiple processes trying to access the $R table at the same time (DR$indexname$R).

    The best fix for this is to use the SMALL_R_ROW storage attribute. In 12.1.0.1 and 12.1.0.2 this is hidden under an event, and needs to be enabled by using:

    alter session set events '30579 trace name context forever, level 268435456';

    OTG-467455
  • OTG-467455
    OTG-467455 Member Posts: 506 Bronze Badge
    edited Nov 8, 2017 6:19AM
    Roger Ford-Oracle wrote:Contention in ctxsys.syncrn is normally caused by multiple processes trying to access the $R table at the same time (DR$indexname$R).The best fix for this is to use the SMALL_R_ROW storage attribute. In 12.1.0.1 and 12.1.0.2 this is hidden under an event, and needs to be enabled by using:alter session set events '30579 trace name context forever, level 268435456';

    Hi Roger,  As far as I can recall Oracle support recommended exactly what you have mentioned and in addition to this to recreate all indexes.   We did that but the problem still persisted.

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2017 8:58AM

    Was it slow immediately the indexes were created, or did they get slower over time?

    Can you run the following SQL on one of the indexes?

    SELECT max(docid) FROM dr$<yourindexname>$k;

    SELECT row_no, length(data) FROM dr$<yourindexname>$i;

    Also if you can check the size on disk of the $R table and its LOB segment - we've had reports of them growing very large over time.

    If you don't want to post that information here then email it to me - it shouldn't be too hard to find or work out my email address.

    OTG-467455OTG-467455
  • OTG-467455
    OTG-467455 Member Posts: 506 Bronze Badge
    edited Nov 8, 2017 12:09PM

    The speed is OK most of the time, but at random it bogs the system down.  The WebSphere system, when no response to queries are returned, retries the query.  The system still does not respond and eventually a backlog situation starts to develop. Removing the text index alleviated the situation and was only a temporary solution.  Maximo, is unfortunately designed around the CTX indexes for information searches.

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 8, 2017 4:20PM

    If you upgraded your database from 11.2 to 12.1 it's possible that there is an issue with the SMALL_R_ROW setting in the data dictionary. Please review this support note:

    Doc ID 2306624.1

    Roger

    OTG-467455
  • OTG-467455
    OTG-467455 Member Posts: 506 Bronze Badge
    edited Nov 8, 2017 4:29PM

    Oracle 12cR1 is a new installation on new IBM Power8 machines.   The database was created using a ksh script containing all the code to create the instance, tablespaces and datafiles etc.  So initially there was an empty db and the data was migrated from an Oracle 10gR2 db to 12cR1 using  scripts to extract specific data in stages to a 12cR1 database as a staging point prior to loading the new production database.   During stress testing no issues were identified.  Only post go live did the problem rear its head.

This discussion has been closed.