IBM Support

Understand Oracle Temp Usage of Maximo

Technical Blog Post


Abstract

Understand Oracle Temp Usage of Maximo

Body

There's some intermediate resource required by Oracle database to execute sql statements. One among them is Oracle Temporary Table Space when pure-in-memory operations could not be performed due to resource limitation or specific execution plan.
As Oracle doc says:"A temporary tablespace contains transient data that persists only for the duration of the session".  So likely, if you have many things produced in the middle of query, you will probably need a temporary space to put such intermediate stuff in.
 
Before we start to discuss how and when Maximo application will use temp in Oracle database, there's one thing worth mentioning: it's Oracle database to decide when and how to use the Temp segment, while any application code just want the query result. 
 
We simulated a lack of memory situation, which will for Oracle to use temporary space more frequently. Steps:
1. Set a small upper limit sort workorder for maximo user session(a logon trigger could be used)
2. Use a single user to list/sort record in one application, e.g. WORKORDER
       We sorted 48K workorders by Location, then captured that session was using several MB of Temp( Use Oracle dynamic views to achieve that) and the query is like “select count(*) from workorder…”
3. Monitor the temp usage on Oracle side
4. Observe which subsequent action will release the TEMP
5. Observe how long the TEMP is hold in Oracle
 
Observation 1: Stay in the same app(e.g. WO), user keep active.
If user continues working in the same app, select some items or switch TABs inside the same application (e.g. WO), the TEMP was not released. 
Observation 1.1: Stay in the same app, user inactive:
If user stay idle in the same app, the TEMP was released after a session timeout (default 30 minutes).
Observation 2: GOTO other apps;
After switch to another application, (e.g. from WO to ASSET), the TEMP was released.
Observation 3:  Normal log out:
After a normal log out, the TEMP is released.
Observation 4: User Close the Browser:
If user close the browser directly, the application has no idea if the user finished using the result set. So it will wait for a session timeout and cleanup the TEMP.
 
No doubt that the best way is to avoid using Temp, pure in-memory operations have the best performance. But if your Oracle database doesn't have enough memory configuration, you'd better train your end user more carefully or tune the sql statement to run more efficient with Oracle database.

[{"Business Unit":{"code":"BU005","label":"IoT"}, "Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11134663