IBM Support

System Properties to Monitor and Troubleshoot Performance

Troubleshooting


Problem

How mxe.db.LogSQLTimeLimit, mxe.db.FetchResultLogLimit, MBOCount, mxe.db.logSQLPlan, and mxe.db.sqlTableScanExclude properties are set to troubleshoot or monitor performance and memory usage in Maximo and TPAE applications

Cause

Clarifying documentation

Resolving The Problem

There are five helpful properties that can be used to troubleshoot and monitor Maximo Asset Management performance. Follow the procedures here to enable logging for Maximo and Control Desk.

1. Open the Logging application.



2. Set the Root Logger in Logging to INFO and change the Appender to Rolling.







3. Set the Rolling Appender to a file size of 20 (megabytes) and Backup Index to 24.





4. Apply the settings.



5. Set the following properties in the System Properties Application:



a) mxe.db.logSQLTimeLimit=1000



b) mxe.mbocount=1



c) mxe.db.fetchResultLogLimit=200

Note: The default value is 1000, but in cases where memory is being exhausted by Maximo Business Objects (MBO) in memory, 1000 might not be sufficiently granular to capture the process responsible for loading the objects. See the description here.



6. Once the three properties are updated, select the property, choose Select Action or Common Actions, and click Live Refresh.






Performance Note: Using the FetchResultLogLimit, LogSQLTimeLimit, and MBOCount properties have minimal to no impact on performance. IBM recommends that these three settings always be turned to assist with troubleshooting when a problem arises that needs events captured by logging. However, the mxe.db.logSQLPlan and mxe.db.sqlTableScanExclude properties (Oracle database only) impact performance and should only be used when needed.

mxe.db.fetchResultLogLimit - This property is used to capture the number of objects a Maximo Business Object (MBO) loads while it is processing. It is common for a MBO to load several thousand objects temporarily and then release them; however, loading 60,000 or more objects might indicate a problem. The value of the property is the number of objects loaded before an entry is written to the log entry. For example, setting mxe.db.fetchResultLogLimit to 1000 means log entries will be written as 1000, 2000, 3000, objects are loaded into any single MBO set. For troubleshooting performance and memory problems, set this property's value to 200. When performance and memory are not an issue, set this value to 1000. This property does not impact performance but can impact log readability.

Note: We recommended against setting loggers to DEBUG unless requested by IBM support. Setting root loggers to DEBUG is different from setting these specific "debug" properties. Keep these three properties enabled in all environments.

mxe.db.logSQLTimeLimit - This property is used to capture SQL statements that take longer than the specified amount of time to return, which is expected to be less than one second. If you notice performance problems, set this property to capture long running SQL, which helps determine whether database tuning is required. The number associated with this property is milliseconds (1000 milliseconds = 1 second). This property does not impact performance, but the lower the value (a time period), the more SQL Statements are written to the logs. Writing many SQL statements to the log affects log readability.

mxe.mbocount - This property is used to capture snapshot intervals of memory usage. The MBOCount is useful in identifying trends of memory usage and release. Typically a log would show objects growing and shrinking as usage differed on the server. If objects are not released or the count grows continually, that is a sign of a memory leak. Many things including bad data, custom code, incorrect environment configuration, and bugs can cause memory leaks. The most important thing about fixing a memory leak is identifying it. This property does not impact performance.

mxe.db.logSQLPlan - This property is only available in when Oracle is used as the Maximo database. This property logs the execution plan for all SQL statements that contains a full table scan. The mxe.db.logSQLPlan property depends on the mxe.db.sqlTableScanExclude property. The mxe.db.logSQLPlan property can affect performance. Use it when needed only.

mxe.db.sqlTableScanExclude - This property is only available when Oracle is used as the Maximo database. This property provides exclude criteria for the mxe.db.logSQLPlan property. Tables that do not have indexes and are intended to use a full table scan can be excluded from logging by using this property. Table names are separated by commas and must be entered in uppercase. This property can affect performance.  Use it when needed only.

[{"Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"ARM Category":[{"code":"a8m50000000CbPQAA0","label":"Performance"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.5.0;7.6.0;7.6.1","Type":"MASTER"},{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSWT9A","label":"IBM Control Desk"},"ARM Category":[{"code":"a8m500000008b3TAAQ","label":"Miscellaneous Category (Portal, UI, Maximo, Install)"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.5.0;7.6.0;7.6.1"}]

Document Information

Modified date:
02 September 2021

UID

swg21291250