Slow Database: VOMDOBJCAC too big

Symptoms

Database operations take very long. Redeploys are very slow - this might cause redeploys of really large applications to fail due to the timeout for this operation.

Further investigations on the database show that the VOMDOBJCAC table has millions of rows. This may happen when old caches are not deleted properly on shutdown of VoiceObjects. Thus this description takes VOMDOBJCAC as an example table.

Resolution

Remove old VoiceObjects cache data from the VOMDOBJCAC table.

General steps

Delete all rows from VOMDOBJCAC

Note: This is usually not possible in e.g. productive environments.

• Stop all VoiceObjects instances
• Remove all data the VOMDOJCAC table
  • Oracle: TRUNCATE
• Restart all instances - new caches will be built from the first instance.

Specific Steps for Oracle 10g: No restart needed

Reset the "high watermark" in Oracle 10g without truncating the table.

Please ensure that the database is backed up before starting any change in the database.

Clearing the table without interrupting VoiceObjects is a 3-step process:

1. Prepare the table VOMDOBJCAC:

   Run the SQL
   alter table vomdobjcac enable row movement
   to enable the movement of rows in the table.
   This step needs only to be done the first time this process is executed.

2. Delete the old data from VOMDOBJCAC
   • To get all cache IDs which are not to be deleted, please open the Control Center and note down the Cache IDs for the active services as well as the cache IDs from the restore caches.
     The screenshot below shows this for the Desktop for Web control center.
   • Then create a SQL to delete all rows where the CACHE_SID is not one of the cache IDs from the control center.
     With this SQL delete the old data.
3. Now reset the high watermark for this table

   Run the SQL
   alter table vomdobjcac shrink space

After these operations:

1. Redeploy all Services.
2. Assure that all services run as ecpected - and if there are no error messages for database issues in the VoiceObjects logs.

In case something fails in the above steps, it will be needed to restart all VoiceObjects instances to force the creation of new caches.