Test Oracle InMemory Database with Maximo

For the last few years, SAP has been pushing hard on its HANA
InMemory data platform and everybody talks about it. For me it makes sense
because SAP’s ERP is such a huge system usually used by super large enterprises
and is both a data intensive and mission critical system.

 

 

Maximo on the other hand is usually much less data intensive
and for most clients I work with in Vietnam, they have small systems with
databases of less than 10-20GB. Thus, I believe InMemory database is not a big
deal for Maximo users. As I recently moved to Australia and got a chance to
work with a much bigger client. Their Maximo runs on a cluster of more than two
dozen JVMs yet somehow is still a bit slow considering the number of active
users that they have. I suspect (since I don’t have visibility to their DB server)
the bottle neck is the database in this case. Besides from the standard
suggestions of looking at disk storage/SAN, network, memory allocation etc., I
also mentioned they can consider implementing InMemory. Then I realized I never
seen it implemented with Maximo, it would be a huge embarrassment if they look
at it and find out that it doesn’t work.

 

This week I have some free time, so I decided to play around
with InMemory database for Maximo to (1) confirm if it is possible and (2) see
if it gives any real performance gain for Maximo.

 

Here is my system configuration:

 

 

 

 

As you can see, this is very different to a real production
environment and thus the result found in this test may not reflect what you will
find if implemented in production. Some key elements that I can think of that
could lead to differences in results of this setup and real-world production
system include:

 

 

 

 

As mentioned, Maximo demo system is very small and most
operations will complete almost instantly (less than .5 second). Thus, I
decided to test two operations:

 

 

The data I chose is the Job Plan: “PMBULKTR – Bulk Trailer PM
Servicing”, which applies to 5 trailers with Assetnum “44416x” (belong to ‘FLEET’
site). The reason I chose this job is because it has 70 tasks. It is a common
problem for Maximo users when changing status of a Work Package with large
number of child WO or tasks is that this operation is very resource intensive
and can take a lot of time to complete. I have seen in many cases, such as in
plant turn-around, changing WO status took so much time that the browser’s
session timed out. So I decided to carry out a test following these steps: Select
the 5 PMs, generate WO at once. Do it three times to measure the time it takes
to complete. After I got 15 new WOs, I move to the Work Order Tracking app, and
select all 15 of them, then try to change status to WAPPR, then to APPR, then
WAPPR again to measure the time it takes each time.

 

After I finished timing the above steps, I will turn on
INMEMORY for the whole ‘MAXIMO’ tablespace by issuing a command: ‘ALTER
TABLESPACE maximo DEFAULT INMEMORY;’ Restart the system, then do the same test again
to measure execution time with INMEMORY enabled.

 

Anyway, my expectation for this configuration would be a 2x
to 5x in term of improved processing speed. The reason is when we built an
add-on for inventory cataloguing, there was a process to compare technical specification
of one item with the spec of thousands of other items to find similarity. This
process took a lot of time to execute. The developer then cached the data in
memory and run the operation with cached data only. This resulted in a boost of
more than 100x in performance. So instead of hours, it takes only a few seconds
to complete. Thus, I think an expectation of 2x to 5x performance gain in this case
is reasonable.

 

Below is the result I got from the test:

 

 

For each operation, I got only about 20% reduction in
execution time. I tried a few other quick tests with other processes while
switching on/off the INMEMORY feature and the result is consistent. Obviously
with this result, my expectation of 2x – 5x improvement is proven to be
unrealistic.

 

Thinking of putting this into practice, if your organization
already have Oracle Enterprise Edition, setting up would be really simple.
Throw in some extra bars of memory, select a few key tables that got queried
the most such as the Work Order – related tables to populate them into INMEMORY
area. If it can give you 20% reduction in execution time, I believe it is still
very attractive to consider.

 

For Microsoft SQL Server, what I found on the web, we need
SQL Server 2016 or 2017 Enterprise Edition which works with Maximo. (I tested
installing Maximo 7.6.0.9 on SQL 2017 and it works well). The free Developer or
Evaluation edition can also be used to test at home. However, I found the configuration
steps seem to be more complex for Maximo as you have to review data structure
to modify/exclude columns with specific datatypes such as XML, BLOB etc. Thus,
if my company is using Maximo with SQL Server, I would think twice about implementing
INMEMORY feature.