The ability to log executions of ad-hoc Queries executed via the Query Viewer in the PIA was introduced in PeopleTools 8.44. However, not all queries are executed directly, they can also be scheduled via the Process Scheduler. Queries whose result sets are larger than the maximum fetch size for the PSQRYSRV server raise an error, and they must be scheduled.
Don't be tempted to increase the maximum fetch size because the entire result set for queries run in the PIA is set to the Java pool in the web server, and so it can impact all other users of that web server.
Scheduling queries is a good thing because:
i) You no longer copy the results to the web server Java pool.
ii) You can restrict the number of queries that can execute concurrently with standard Process Scheduler functionality.
However, Query logging does apply to scheduled queries, and recently I needed to find a way to work out which scheduled queries were executed most frequently and took the longest time. With most batch processes, it is just a matter of querying the process scheduler request table PSPRCSRQST, but scheduled queries are executed by an Application Engine program called PSQUERY. The query name is specified on a run control, but operators tend to recycle the run controls, so I can't use them. All queries will look the same. However, the name of the query output file includes the name of the query. The list of posted output files is held on the table PS_CDM_FILE_LIST. By joining these two tables, I know how long it took to execute each query, and I can construct the following query for Oracle RDBMS (updated 10.10.2011).
But there some limitations:
i) Once a query has been purged from the Process Monitor is will no longer be included in the statistics.
ii) This technique cannot distinguish between a public and a private query of the same name. If any operators copy a public query to a private one of the same name, this query will report them all as one.
I have also produced a version of this query for SQL Server: