Maintaining MQ tables in Oracle

Our DBAs made a check on the size of the Oracle table spaces associated to some AutomationEngine servers.
Most of the time, the size of the table related to the MQMEM table is quiet small (300 to 500MB).
But some servers have a huge one (14 and 25 GB).

How to know if this is really in use ? Is there a way to purge
these tables (COLD restart ?) and if we do this what is the impact of
the database size (does the COLD restart free the MQMEM tables space,
and do we get the space back ?)

1 - 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.

If there are any issues with the database performance you should check at least all indexes and table statistics.

Below our recommendation regarding an oracle database:

• Tablespaces for AUTOMIC should be created with ASSM (Automatic Segment Space Management). In addition to several performance advantages when inserting data, ASSM also allows tables to be transparently reorganized.

• Use of local disks if the database is not installed on an external disk subsystem, the data files of both the index and data tablespaces (UC4_INDEX, UC4_DATA) should be kept on physically separate disks in order to achieve maximum I/O throughput.

In order to further improve the I/O, the tables E*, A*, R* and MELD, as well as the temporary AUTOMIC tables (UC_TEMP*, MQ*) should be placed in their own tablespaces or data files on physically separate disks. For this, further data tablespaces must be created (e.g. UC4_DATA_E, UC4_DATA_A, UC4_DATA_R, UC4_DATA_M, UC4_DATA_T).

However, the final distribution of data files always depends on the requirements and the available infrastructure.

• Reorganization with database resources you should use Oracle SEGMENT ADVISOR here.

This generates a list of all tables/indexes that require reorganization. You can also do this manually following extensive changes in the AUTOMIC database.

• Statistics

The access statistics of the database tables should be regularly updated. To do so, a daily update job (GATHER_STATS_JOB) is scheduled during database installation, as long as this option is not explicitly deselected. The time of the daily update can be freely selected and should take place in the AUTOMIC system during times of high load, in order to calculate representative values. Should an automatic update controlled by Oracle not take place, an update can be scheduled in AUTOMIC with the following statement.

Exception: Because it is not necessary to create access statistics for the UC_TEMP* tables (UC_TEMP, UC_TEMP1, UC_TEMP2, UC_TEMP3,), any available statistics for these tables must be deleted and the tables then locked so that new statistics cannot be created.