Using SQL Tracing for Start Centre performance problems
This post refers to entries in the SystemOut.log / <jvmName>.log file described in this
Start centres are a very useful component but they have the potential to cause serious performance problems.
Start centres can contain a number of portlets.
JVMs have a pool of threads that can service user requests.
Each portlet will generate a separate request to fulfil the portlet. Each request will consume a thread on the JVM until the portlet is rendered.
The following SQL is executed to retrieve the list of start centres for a user, it retrieves details of an individual start centre.
- select * from scconfig where userid = 'MAXADMIN' order by groupname asc
- select * from sctemplate where (sctemplateid = 52)
These entries can be seen when the SQL logger is configured to log all SQL statements. This is discussed in the SQL tracing article.
Impact of start centres with lots of portlets
Start centres are the first things users see when a user logs into Maximo. If the Maximo login takes a long time then it gives a bad impression of Maximo.
If a large group of users are using the same start centre and they login at the same time then there will be a large number of requests being generated.
If the threads are occupied doing other work then the requests will be paused until a thread becomes available. From the user’s perspective the login will be delayed and the screen will display a white screen sometimes described as a whiteout.
If one of the pieces of SQL used for the portlet is slow then this can cause a major performance problem. The SQL may not be enough to trigger the slow SQL threshold and generate the warnings discussed in this article.
Users often use the start centre as a way of leaving applications and users are sometimes advised to navigate to the start centre to free memory, this will be discussed in a later article.
Impact of start centres with portlets that return a lot of data
Tip -> Check if the resultsets are returning huge numbers of records e.g. all workorders for a site. Will a user really need to see 1,000 records? My rule of thumb is that a user is unlikely to view/process more than 200 records in a portlet.
Queries loading large numbers of MBOs will result in a lot of MBOs being loaded into memory which combined with other users, can then lead to the memory being exhausted and coredumps/heapdumps being generated. High memory usage is discussed in a later article.
Tip -> Ask why a user needs a lot of records …. Are they downloading the results into Excel or some other tool for further manipulation which could be performed using Maximo functionality?
Reducing the performance impact of a start centre
Start centres are often used to provide daily summaries but if users are navigating to them several times a day then unnecessary queries could be being executed.
A good solution would be to create a new basic start centre. This could provide key information that is absolutely necessary. Additional start centres could be used to provide the details that the users need to see, they can click on the tab to view the information.
If a start centre is suspected of being the cause of a performance problem then enable additional SQL tracing and watch the SQL that is being monitored. The web server’s access.log will also record details of the portlet requests, that log will be discussed in a later article.
Are you assessing the impact of changes to start centres or new start centres when they go live?
It is always best to test the changes and monitor the SQL being generated. This article explains how to enable SQL tracing.
Tip -> Check the SQL being executed and the number of records being returned and typical execution times
You may not own the start centre but you should expect to be heavily involved in resolving any performance problem after a new start centre has been deployed.
Resultsets in a start centre and the impact of jumping back and forth
Some administrators create resultset portlets so users can use the list to manage their work and use the list to jump to the application to perform their work.
It is important that users don’t navigate like this when they use the resultset:
goto start centre -> open application for a record in the resultset portlet -> goto start centre -> open application for a record in the resultset portlet
When the users switch between the application and the start centre, the start centre queries are executed again and this can lead to serious performance problems. The best solution is to provide a default query in the application to provide the list of records to process.
Tip -> Confirm how users are expected to use the start centre and whether a default query is better than a resultset portlet
Pampering yourself with a system administrator start centre
It is worth creating a start centre to make your job easier.
Things to include:
- Links to key applications e.g. Application Designer, DB Configuration, Message tracking and Message Reprocessing
- Resultsets to show key information e.g. number of users who are logged in, who are logged in and on which JVMs
- Number of users logged in by license type
- Details of failed login attempts
- Details of blocked login accounts
If you like this article then please share or like it. Why not look at the other articles listed in this index.