Note: This blog entry was originally created by Zach Zhang in the Asset Management Group. I wanted to share this info with the Process Automation Group too.
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.
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.
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