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. WORKORDERWe 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 side4. Observe which subsequent action will release the TEMP5. 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":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
UID
ibm11134663