How to monitor PS/Query performance and usage

You can use Query Administration page to monitor query performance and usage. Some of the statistics you can get includes average runtime, number of times run, and the dates last run. Using a predefined search, you can also select queries to review and report on.
Query Administration also enables you to cancel queries that are currently running in Query Manager and Query Viewer as well as enable and disable queries and logging.

To turn on query statistics navigate to PeopleTools, then select Utilities, then select Administration, then select Query Administration page.

In this page you can see Owner ID, Query name, Folder, Average time to run, Average number of rows, Number of times run, Last run date and time, Logging status, Disabled status.
For the selected query, click the View Log link to view the log for that query. A message appears if no logs are available. You can click the View SQL link to view the SQL for that query.

Enable logging of Queries

You can enable logging of all or individual Query by clicking Logging On button. When you enable the logging feature, detailed statistics will be logged on the query every time it has run to completion. The statistics are stored in a separate Query Log table.
The metadata table to look at for query logs are PSQRYSTATS. The PSQRYSTATS is particularly useful if you want to check timing values if your query is running slow and you can take necessary action to optimise your query.

Other action you can performe with this page is;
Logging Off: Disables logging for the selected query.
Enable: Enable the selected query to be run, previewed, or scheduled.
Disable: Disables the selected query from being run, previewed, or scheduled.
Assigns New Owner: Assign a new owner to the selected query.
Delete: Deletes the selected query.
Rename: Renames the selected query.
Move to Folder: Moves the selected query to a folder.
Clears Stats/Logs: Delete the statistics and logs for the selected query.

To check which Queries are running from Query Manager or Query Viewer can be checked using meta table PSQRYTRANS.


Ayesha Wee

    Blogger Comment
    Facebook Comment


Post a Comment