For any database enthusiasts out there, here are the explain-plans (via Toad for Oracle 12):


Maximo Message Tracking:
Performance = Poor
Time:16 seconds


Accidently treating MEAMSGID as a number, not text:
Performance: Poor 
Time: 11 seconds



Properly structured query:
Performance: Fast !
Time: 0.06 seconds

The index is used, which is good.
The explain-plan estimated "cost" is 3 (compared to 9,673 for the others).

MAM 7.6.1.2; Oracle 19c:


I've found querying message tracking (MAXINTMSGTRK) to be surprising slow.

For example, if I search for an exact message ID, it takes 16 seconds to return a single record (table has 400,000 records).
(We can use the list view to search for the record  -or-  the Find Message ID textbox in the top left corner. The result is the same either way.)

Why so slow?
I could be wrong, but I believe Maximo generates the WHERE clause incorrectly. It wraps the MEAMSGID field in a function, which means the database can't utilize the index on MEAMSGID.



In contrast, if I edit the WHERE clause by removing the UPPER() function ( meamsgid = '2765716276627860479179' ) then it executes instantly.

A Simple Tip:
To get around this issue, I've been writing my own WHERE clauses in Message Tracking, instead of letting Maximo do it the slow way with the function. This saves a fair amount of time/annoyance when exploring/querying individual messages.

Note:
It seems like Oracle will allow us to search for MEAMSGID = 2765716276627860479179, even though MEAMSGID is text, not a number. But it's painfully slow. It's much better to use single quotes on the ID to treat it as text: MEAMSGID = '2765716276627860479179'. That executes instantly.

Related post:  Index ignored due to UPPER( ) function?


I just thought I'd share my findings in case anyone finds that interesting. 

is this improvement seen via the application? or from the DB? IE, if run a query from the DB for a specific integration and error, i get all rows quickly. but via the application, it is very slow.

MAM 7.6.1.2; Oracle 19c:


I've found querying message tracking (MAXINTMSGTRK) to be surprising slow.

For example, if I search for an exact message ID, it takes 16 seconds to return a single record (table has 400,000 records).
(We can use the list view to search for the record  -or-  the Find Message ID textbox in the top left corner. The result is the same either way.)

Why so slow?
I could be wrong, but I believe Maximo generates the WHERE clause incorrectly. It wraps the MEAMSGID field in a function, which means the database can't utilize the index on MEAMSGID.



In contrast, if I edit the WHERE clause by removing the UPPER() function ( meamsgid = '2765716276627860479179' ) then it executes instantly.

A Simple Tip:
To get around this issue, I've been writing my own WHERE clauses in Message Tracking, instead of letting Maximo do it the slow way with the function. This saves a fair amount of time/annoyance when exploring/querying individual messages.

Note:
It seems like Oracle will allow us to search for MEAMSGID = 2765716276627860479179, even though MEAMSGID is text, not a number. But it's painfully slow. It's much better to use single quotes on the ID to treat it as text: MEAMSGID = '2765716276627860479179'. That executes instantly.

Related post:  Index ignored due to UPPER( ) function?


I just thought I'd share my findings in case anyone finds that interesting.