Automic Workload Automation

  • 1.  System slow, check  MQ_CHECK_TIME

    Posted Apr 26, 2018 02:33 AM

    If the system appears slow, check the logs to see if there are many messages like the following:

     

    U0003592 UCUDB - Status: '' NativeError: '10634' Msg: 'ORA-10634: Segment is already being shrunk


    If you find such Log-Messages, the parameter MQ_CHECK_TIME and MQ_BLOCK_COUNT in the variable UC_SYSTEM_SETTINGS should be evaluated.

     

    The MQ_Reorg process, controlled by the parameter MQ_CHECK_TIME, can improve the performance, but unfortunately that can backfire. The MQ-tables often change their size. During a high load period they have a lot of entries, during quiet times the number of records can decrease to only a few. That causes the tables to be extended again and again. According to the parameters MQ_CHECK_TIME and MQ_BLOCK_COUNT in UC_SYSTEM_SETTINGS, every x minutes (MQ_CHECK_TIME) system checks to see if one of the MQ-tables has more than extents (MQ_BLOCK_COUNT). If this is the case, a shrink of the table is executed. The shrink locks the tables. There is no check whether a WP is already performing a Reorg and if the system load is high. If the system is under heavy load, the MQ tables also have many entries. Then the table may be larger than given by MQ_BLOCK_COUNT, but a shrink would not work. It would even be counterproductive, as he only generates more load and locks the tables and therefore can cause WPs to freeze. If you set MQ_CHECK_TIME to a higher value this check will happen less often. If you set it to 0 it is deactivated.

     

    Please note: If you set this parameter to 0, a manual reorganization of the MQ-tables is required. This is a regular DBA task.



  • 2.  Re: System slow, check  MQ_CHECK_TIME

    Posted Jun 05, 2018 05:59 PM

    Yep, my organization runs the MQ REORG statements at a predefined non-peak time of day, rather than leaving it to be automatically triggered by the system.