Announcement

Collapse
No announcement yet.

Oracle sys.obj$ deleted objects killing performance

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Oracle sys.obj$ deleted objects killing performance

    I have a Mirth Connect 1.8.2 configured and running against an Oracle Database.

    It ran great for the first several months, but has been experiencing problems with the performance of the underlying data dictionary tables. (sys.obj$ appears to be the biggest problem)

    Is there any way to configure Mirth Connect so that it doesn't create and delete new database objects for the message files that are being processed. My current system has 64 million deleted records in the sys.obj$ data dictionary table and it is killing performance.

    thanks,

    Kevin Hrim

  • #2
    Do you have message pruning set?

    Its under...plugins in 1.8.2 I think. In 2.0 and later its under settings.

    Go into your channels and set how long to keep messages for, and then set up your pruner (I run mine hourly).

    If that's not it, I'll defer to someone who knows Oracle.
    I can be reached through gmail and Google Talk using davidrothbauer at gmail dot com
    http://www.linkedin.com/pub/david-rothbauer/5/923/518
    codeismydrug.wordpress.com
    hl7coders.wordpress.com

    Test all my code suggestions prior to implementation

    Comment


    • #3
      It is an Oracle issue. But, I'm trying to prevent it from re-occurring once I get the database repaired. I was hoping somebody who had contributed to the source could identify if the behavior I've noted (all message files appear to create and destroy a unique set of database objects during processing).

      Comment


      • #4
        Mirth 1.8.X uses some temporary objects when configured w/ oracle.
        When searching for events or messages in the admin app, Mirth seems to create some tables/sequences/indexes to store the search results and help pagination. This may be causing the problems you're experiencing.

        Can you identify the object names ? are they somewhat like MSG_TMP_$uid$, IDX_MSG_TMP_$uid$, SEQ_EVT_TMP_$uid$ ...?

        HTH

        Comment


        • #5
          Yes, those are exactly the object names that I'm encountering. tens of millions of them.

          The problems that this is causing:

          1. The database will no longer close without a "shutdown abort" . (I believe that this is caused by the high object count, and the SMON background process failing to finish it's cleanup of the sys.obj$ and other dictionary tables)

          2. Any queries that access "all_objects" or "dba_objects" are terrible performers

          3. Even though the "mirth" schema is small, the expdb utility cannot export in less than 24 hours.

          Thanks for your response to my question. So, is there any way already built into Mirth Connect to avoid these MSG_TMP_.... IDX_MSG_TMP_.... etc... objects being created?

          Or, is it my problem to fix and submit back to the code base?

          Thanks again,
          Kevin Hrim

          Comment


          • #6
            As far as I know you can't do anything to avoid these objects being created, unless
            -don't use message/event search, even revoking CREATE INDEX,TABLE,SEQUENCE to Mirth user.
            -switch to Mirth 2.x (I don't know if this behaviour has been fixed)

            which oracle are you using? XE? Standard? Enterprise? patched?
            I've been running Mirth w Oracle 10gr2 Standard and didn't hear about these performance issues.

            Why do you think that these objects are related to the shutdown abort and SMON problems?

            If you use expdp after reorganizing Mirth'schema with:
            alter table .... MOVE;
            alter index ... rebuild online;
            takes 24h ??

            HTH

            Comment


            • #7
              I think those are created less in the latest releases. If I remember right, they used to be created more often when you did things like reprocessing, removing messages, etc. Try upgrading and let us know if it solves the issue.
              Jacob Brauer
              Director, Software Development
              NextGen Healthcare

              sigpic

              Comment


              • #8
                Thanks for the feedback.

                I feel that we are experiencing these issues (SMON failing to keep up) for a couple of reasons.

                1. It's a dual core box with a couple other instances. Nothing that consumes too many resources, but enough to keep the cpu wait queue running between 2 and 4 continuously.

                2. The instance is shared with Cyclone (EDI) that is also processing a high volume of messages.

                3. It is older code, so I should upgrade to see if the impact is less.

                4. I believe that I hit a tipping point with the object left overs, after that it was inevitable that I would be creating more deleted objects faster that the SMON process could clean up. (and it appears that this is a very steep linear degradation )

                My next steps:

                1. Use transportable tablespace feature in Oracle to move the mirth tablespace to a fresh instance.

                2. Upgrade to Mirth Connect 2.x

                3. Watch to see if issue has been addressed.

                Thanks again for everybody's assistance.

                Kevin Hrim

                Comment

                Working...
                X