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:

• Host laptop: Processor Core i7-6600U – 2 cores x 2.6 Ghz, 16GB RAM, SSD 500GB. Windows 7
• VMWare: I gave it 10G Ram, 4 cores, Windows 7
• Database: Oracle 12c R2 x64 Enterprise Edition. I gave it 1G for PGA, 4G for SGA (of which, 2G is given to INMEMORY store)
• Maximo 7.6.0.0 – Demo database. I setup only one MXServer and gave it 2G heap size

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:

• Standard Maximo’s demo database is super small, less than 500MB. Thus, InMemory may not lead to any improvement at all
• Maximo App and Database deployed on the same box. Thus, there’s no network latency between App and DB server as usually seen in production environment where DB server and App server are placed in two different subnets, and can have a firewall in between. This makes resource retrieval process much more expensive.
• This has only one session instead of hundreds if not thousands of concurrent sessions as can be seen in production.
• DB servers on production usually use superfast storage systems through SAN/RAID configuration, thus InMemory may not improve processing time in that case.

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:

• Generating Work Order from PM
• Changing Work Order status

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.

(Note: I don’t provide detailed steps to configure INMEMORY feature in Oracle 12c here because it is super simple, all you have to do is set INMEMORY_SIZE parameter to something other than 0 such as 2G or 4G. Make sure your SGA is larger than this, e.g. if INMEMORY_SIZE is 2G, SGA should be 3G or more. Otherwise, you will not be able to start the database service. Once INMEMORY_SIZE is allocated, issue the ALTER TABLESPACE or ALTER TABLE command to enable/populate the tablespace or table into INMEMORY area. Also make sure that you have Oracle 12c Enterprise Edition)