MQ-Table Reorganization (Oracle)

Discussion created by Andreas_Sprosec_7439 on Mar 31, 2017
Latest reply on Mar 31, 2017 by Roney.Uba

you Asked 

How the UC_REORG procedure is working (will be all tables in the UC4 schema reorganized)?

and we said ... 

This procedure is especially designed to reorganize the MQ% tables in an Oracle database.

Ensure that reorganization of the MQ tables is enabled on the affected Automation Engine. Look for 'MQ_CHECK_TIME' in UC_SYSTEM_SETTINGS and verify that it is not set to '0', in which case MQ tables are not automatically maintained by the AE. 

The reorganization is caused by the settings of MQ_CHECK_TIME and MQ_BLOCK_COUNT within the UC_SYSTEM_SETTINGS. The default values of 600 seconds and 64 blocks but from our experience it seems to be too low. Please adjust the values according to your needs:


MQ_CHECK_TIME = 86400 (1 day) 

MQ_BLOCK_COUNT = 1300 (around 10 MB) 

Also a DBA should analyze the MQ-tables and it might be that a reorganization every 7 days is enough.