Below is an extract from a PeopleTools trace. What happened is that the query ran until the Tuxedo service timed out. Tuxedo terminated application server process 31944, and spawned a new process (ID 3598). The user session received an error.
Even when each query finishes within a reasonable amount of time, the query can be run several times by the component.
This is the definition of PSPMSESSIONS_VW delivered by PeopleSoft.
Firstly, accurate object statistics are required on the transaction history table and its indexes.
An additional index is required on PSPMTRANSHIST:
Finally, I have changed the view.
- The IN() operators have been changed to WHERE EXISTS(). The new index supports the efficient execution of these sub-queries.
- The sub-queries are now both correlated back to the main query on T3.
- The ROWNUM criteria have been added to restrict the number of rows the sub-queries can return.
The effect of these changes is a significant improvement in the performance of the query. I ran a test query for a single agent on a system with over 3 million rows on PSPMTRANSHIST:
- Original: 24895 consistent gets
- New Index: 22547 consistent gets
- and View Changes: 85 consistent gets