Problems with GIS/SI index/backup/purge services: consequences

As I wrote in my previous post, there were some problems with the BackupService and it was turned off for about 2 months. The DB has grown up dramatically and we’ve started having problems with some BPs. So, we had to re-build a couple of indexes in our GIS DB (Oracle) as it described here SQL queries taking 100% CPU in the database. It solved our problems perfectly.

01 May 2014: There have been some changes on the IBM portal and it looks it doesn’t work anymore – all the links are broken

02 Jul 2014: The blog with this article has been deleted. As I remember, one of 2 options were supposed to do:

1. Rebuild indexes on CORRELATION_SET
2. On CORRELATION_SET table – drop index SCI_IDX_57 and create new one with column (OBJECT_ID, KEY_ID, VALUE_UPPER)

Gennady Kim


2 thoughts on “Problems with GIS/SI index/backup/purge services: consequences

  1. Gennnady приветствую!

    I appreciate your recommendations, and I was hoping you could validate my thoughts about a problem we are currently having due to increased volume on SI 5.1
    We currently have about 20 million records on TRANS_DATA and my DBA wanted to make sure all of them are legit.
    ((We have set 30 days archiving mostly due to correlation data we like to keep for 30 days (we use the data for business and research needs). That’s why TRANS_DATA might be so big))
    I was thinking I could join TRANS_DATA and WORKFLOW_CONTEXT (that table has dates on it) on the WF_ID & WORKFLOW_ID, but by whatever reason the query doesn’t work on SQL Manager. Any thoughts why?

    select * from trans_data,workflow_context where trans_data.rownum < 100 and trans_data.wf_id = workflow_context.workflow_id

    Another thing I've tried was

    select * from trans_data where wf_id < 10000000 and rownum < 10000

    just to poke around to see if WF_ID is ever smaller than my current number 10833790, and I got a lots of records where WF_ID = "-1" and REFERENCE_TABLE is DOCUMENT or DOCUMENT_EXTENSION

    Any way I can make sure my TRANS_DATE is clean has nothing there I don't really need?

    Is there a way to make archiving more aggressive without sacrificing correlation data?

    Would setting expiration date on some BPs for less than 30 default days would help me get the TRANS_DATA size down significantly overtime?

    I sure hope you see this comment as I have no clue how else to contact you directly.

    You are more than welcome to link with me on LinkedIn or Facebook if you want to chat about SI, as I am sure we would have interesting stories to tell 🙂

    Заранее благодарю 🙂

    • (don’t want to leave this comment unanswered :))
      there are several ways to reduce the amount of data , stored in the DB:
      1. change BPs persistence level – by default it’s set to “full” and you can try to decrease it. But be careful and test every change 🙂
      2. setup custom expiration periods for unnecessary BPs

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s