Question & Answer
Question
In the System Properties application, there is a property named mxe.db.logSQLTimeLimit.
Cause
Documentation
Answer
The mxe.db.logSQLTimeLimit property can generate critical debug information for troubleshooting performance, stability, and runtime issues.
In Maximo, the mxe.db.logSQLTimeLimit property can be found in the System Properties application under Platform Configuration. The default value for this property is 1000.
The value associated with this property refers to milliseconds (1000 = 1 second), and the output generates any SQL statement that runs for longer than the value specified in the property
When enabled, this property generates an entry in the log for any SQL statement that runs for longer than the value specified in the property.
Each activity the user invokes in the application executes many SQL statements against the database. When too many SQL statements are running for more than 1 second, users may experience sluggish response from the application.
This property will not generate any output if the threshold is not met. This means that setting the value to a higher number may not provide the necessary results to diagnose a problem.
Typical output to the logs might look as follows:
16 May 2016 08:37:18:030 [INFO] app (null) object (INVOICELINE) :select * from invoiceline where (cstm_invsuspcode='QTY' and invoicenum in (select invoicenum from maximo.invoice where exists (select * from maximo.wfassignment where processname='INVQTYDISC' and ownerid=invoiceid)) and(select sum(invoiceqty*conversion) from maximo.invoiceline i1, maximo.invoice i2 where invoiceline.ponum=i1.ponum and invoiceline.polinenum=i1.polinenum and invoiceline.orgid=i1.orgid and invoiceline.siteid=i1.siteid and i1.invoicenum = i2.invoicenum and i1.orgid = i2.orgid and i1.siteid = i2.siteid and i2.documenttype not in ('CREDIT') and i2.status not in (select value from maximo.synonymdomain where maxvalue='CANCEL' and domainid = 'IVSTATUS')) <= (select 1.05*nvl(sum(quantity),0) from maximo.matrectrans, maximo.poline where matrectrans.ponum=poline.ponum and matrectrans.polinenum=poline.polinenum and matrectrans.orgid = poline.orgid and matrectrans.siteid = poline.siteid and matrectrans.polinenum=invoiceline.polinenum and matrectrans.ponum=invoiceline.ponum and matrectrans.orgid = invoiceline.orgid and matrectrans.siteid = invoiceline.siteid and ((matrectrans.issuetype in ('RECEIPT', 'RETURN') and poline.inspectionrequired=0) or (matrectrans.issuetype in ('TRANSFER', 'RETURN') and poline.inspectionrequired=1)) )) (execution took 336836 milliseconds)
In this example, the SQL statement ran for 336836 milliseconds or 5.6 minutes. This type of statement would have a heavy impact on database server resources. This will impact all users attached to the system, while the database server provides services to this one request.
IBM has internal tools that can correlate and analyze this information when combined with the output of the mxe.mbocount and mxe.db.fetchResultLogLimit monitoring properties to determine many causes of performance, stability, and runtime problems. Since this runs every minute, IBM can gather data on the health of the JVM over a period of time. Often IBM will request up to 24 hours of logs to determine trends in user loads, memory requirements and many other points of interest.
This property should be enabled at all times, since a problem can often not be foreseen or replicated.To troubleshoot or find root cause of many performance issues, the data from this property is required. When enabled. it does not itself have any impact on performance.
For more information on useful debug properties, see the document System Properties to Monitor and Troubleshoot Performance.
Related Information
Was this topic helpful?
Document Information
Modified date:
29 June 2018
UID
swg21426051