Users sometimes complain that a particular operation is slow e.g. retrieving a large number of workorder records when using a filter on a particular attribute e.g. Location.

The developers recognised this and added specialist logging to help debug these kinds of problems.

This post refers to entries in the SystemOut.log /

Out Of the Box (OOB) Maximo is configured to warn if SQL queries take a long time to run. The warnings look like this:

[WARN] [MAXIMO] BMXAA6720W - USER = (SMITHJ) SPID = (70) app (WOTRACK) object (WORKORDER) : select * from WORKORDER (execution took 30000 milliseconds)

These warnings are quite common in Maximo logs, particularly in fresh installations. Fresh installations are particularly at risk of slow SQL because of the WILDCARD search type which is explained in a later article.

Interpreting the message

[WARN] [MAXIMO] BMXAA6720W - USER = (SMITHJ) SPID = (70) app (WOTRACK) object (WORKORDER) : select * from WORKORDER (execution took 30000 milliseconds)

Translated into non-technical terms this message is telling us:

• User SMITHJ was working in the WOTRACK (Workorder Tracking) application
• The user performed an action that meant Maximo executed a piece of SQL that ran for 30 seconds (30,000 milliseconds).
• The query was a select on the whole of the workorder table - select * from WORKORDER

This is normally a very bad query to execute because there are normally lots of workorder records including historic records. It is not surprising to see a million rows in this table in a large system that has been running for several years.

In this case it would be wise to have a conversation with the user (SMITHJ) and discuss how they can use filters to bring back the records that they need.

If a user is filtering on an attribute and it is still slow then it is worth going to the DB Configuration application and opening the object referenced in the query – in this case WORKORDER.

Figure 1 Checking the columns in index WORKORDER_NDX18

Then move to the indexes tab and check if there are indexes that reference that column. Applying an additional filter that allows an index to be used could dramatically speed up the query.

Standard database tools can show the statements with the highest cost and thus which statements to prioritise. There are some very powerful tools out there and too many to list here.

 The threshold to generate the log entries is controlled via the mxe.db.logSQLTimeLimit system property, the default is one second.

The length of time is the length of time from the SQL statement being submitted to the database and the time that the database returns the query results to be processed.

Tip -> Review your logs and see how many of these warnings you are receiving. Initially it would be better to set this to a higher value (e.g. 15 seconds = 15000) and tune those statements before reducing the threshold.

If you want to find out more about this log message then look at technote 1426051.

What is the SPID and why haven’t you explained about it?

The SPID uniquely identifies the DB connection used to execute the query. In other applications this would be very important because you could check all the log entries for that connection and find all the SQL statements being executed by that user.

Maximo is a very clever application and it allows users to share connections simultaneously and to use multiple connections at the same time. This means that you can’t make the assumption that all SQL statements being executed on that connection are for the same user.

A later article will discuss database connections in more detail.

Why are there so many entries for the MAXADMIN user – no administrators were logged in at the time …

The Maximo system uses the MAXADMIN account for its system related queries, with the exception of the integration work. A later blog post will discuss the MAXADMIN account and why it is so important to Maximo.

Can I use this message to track the queries that my users are executing?

There is other SQL logging that will log the SQL being executed regardless of the time spent executing it. Use this setting to generate warnings about SQL that you may need to tune.

Should I use these messages for a system that is performing very badly?

These messages have their place for individual problems but other tools that operate on data produced from the database often provide quicker results and some can make suggestions based on the database configuration.

Vetasi customisation – Slow SQL report

When Vetasi support staff analyse logs from a system we can provide a report that groups and sorts the Slow SQL so you can prioritise it and understand which users / statements are having the highest impact.