Query Stats and Logging
PeopleSoft comes delivered with an option to have Query statistics as well as logging.
Query Statistics
The Query Statistics can be turned on, but only for the entire application. This stores a single row for each Query that is run, with information on how many times it has been run and the average execution time, among many other collected statistics. This information is very useful when preparing for an upgrade; you can determine if a query has been run in the past (starting the day you turn this on).
Steps to turn on Query Statistics:
- Navigate to: PeopleTools, Utilities, Administration, Query Administration.
- Go to the last tab (Settings).
- Turn on Run Query Statistics.
- No Save button. The settings are set automatically.
- Review the PSQRYSTATS table or the first tab (Admin) to see the statistics.
There is no performance degradation we have witnessed, when this it set. It is recommended that this be turned on. The statistics are only collected when this is on, so the sooner you turn it on, the more data you will have in the future, to make better decisions for query cleanups.
Query Logging
In addition to query statistics, you can turn on Query Logging for each query. This will give you information on each query that is executed, including userid, row counts, and timings.
This is on the same navigation/component as the Query Statistics, but on the first tab (Admin). Search for the query you want to add Logging to, then select Logging On.
It is recommended that you search for all queries that begin with your predefined prefix for queries, select all rows, then click Logging On. As new queries are created, the logging for each will be turned off. You may want to come into this page periodically, and turn Logging On for new queries.
