Appendix: Query Administration

This appendix provides an overview of Query Administration and describes how to use the Query Administration component.

Understanding Query Administration

System administrators can use Query Administration to monitor query performance and usage. Some of the conditions that you can monitor include 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.

Note. Workflow queries are excluded from the statistics due to the large volume that can be run as part of workflow processing. Therefore, you should take care that workflow queries are as efficient as possible.

Query Administration also allows you to cancel queries that are currently running in Query Manager and Query Viewer as well as enable/disable queries and logging.

Note. You cannot cancel currently running scheduled queries.

See Also

Using Query Profiles

Using Query Administration

This section discusses how to:

• Administer queries.

• Cancel currently running queries.

Pages Used for Query Administration

Administering Queries

Access the Admin page.

To use Query Administration:

1. Use either the predefined search or manual search option to restrict the list of queries that are displayed.

   For a predefined search, select one of the following options:

   • Queries that belong to locked out accounts.

   • Queries that have been disabled.

   • Queries that have been run in the last (n) days.

   • Queries that have logging turned on.

   • Queries that have never been run.

   • Queries that have run but not in the last (n) days.

   • Top (n) queries by largest average number of rows.

   • Top (n) queries by longest run time.

   • Top (n) queries most frequently run.

     In the (n)= field, enter the desired value for the selected search option.

     For a manual search, select to search queries by query name or owner ID.

     You can define your search further by choosing either begins with or contains search type.

2. Click the Search button to display a list of queries that match your search criteria.

   The Query List group box lists those queries that match your search criteria.

3. Review the following statistics for each query listed:

   • 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.

     Note. If a query appears in the list as <UNTITLED>, a user has created and run one or more queries without saving them. Results from all unsaved queries appear as a single untitled row. You can only clear statistics for untitled queries. Attempting to perform any other action on untitled queries will result in an error message.

4. For the appropriate query, click the View Log link to view the log for that query.

   A message appears if no logs are available.

5. For the appropriate query, click the View SQL link to view the Structured Query Language (SQL) for that query.

6. Select the check box for each query on which you would like to perform an action. You can also click Check All to select all queries, or click Uncheck All to deselect all queries.

7. Perform an action by clicking one of the following buttons:

   • Logging On: Enables logging for the selected query.

     When you enable the logging feature, detailed statistics will be logged on the query after every time it has run to completion. The statistics are stored in a separate Query Log table.

     Note. The statistics log is updated with data only if the query runs to completion.

   • Logging Off: Disables logging for the selected query.

   • Enable: Enable the selected query to be run, previewed or scheduled.

   • Disable: Disable the selected query from being run, previewed, or scheduled.

   • Assign New Owner: Assign a new owner to the selected query.

   • Delete: Delete the selected query.

   • Rename: Rename the selected query.

   • Move to Folder: Move the selected query to a folder.

   • Clear Stats/Logs: Delete the statistics and logs for the selected query.

8. For the appropriate query, click the Logging button to enable logging for that query.

Note. Your changes are saved automatically. There is no Save button.

Cancelling Currently Running Queries

Access the Executing page.

The Executing page displays all of the currently running queries, allowing you to enable or disable queries, enable or disable logging, and cancel currently running queries. If an administrator needs to verify that a query has been cancelled, they can look in the Appsrv.log and verify that the PSMONITORSRV service has cancelled the selected query.

To cancel currently running queries:

1. Use either the predefined search or manual search option to restrict the list of queries that are displayed.

   For a predefined search, select one of the following options:

   • Queries that have been running longer than (n) minutes.

   • Top (n) queries by longest run time.

2. In the (n)= field, enter desired value for the search option.

   For a manual search, select to search queries by query name or owner ID.

   You can define your search further by choosing either the begins with or contains search type.

3. Click the Search button to display a list of queries that match your search criteria.

   The Query List group box lists those queries that match your search criteria.

4. Review the following statistics for each query listed:

   • User ID.

   • Owner ID.

   • Query name.

   • Domain ID.

   • Process identifier.

   • Host

   • Machine name.

   • Status

   • Time started.

   • Timeout end time.

   • Number of times killed.

   • Logging status.

   • Disabled status.

5. For the appropriate query, click the View Log link to view the log for that query.

   A message appears if no logs are available.

6. For the appropriate query, click the View SQL link to view the Structured Query Language (SQL) for that query.

7. Select the check box for each query on which you would like to perform an action. You can also click Check All to select all queries, or click Uncheck All to deselect all queries.

8. Perform an action by clicking one of the following buttons:

   • Kill Selected Processes: Forces the selected query to stop running.

   • Logging On: Enables logging for the selected query.

     When you enabling logging, detailed statistics will be logged on the query after every time it has run to completion. The statistics are stored in a separate Query Log table.

   • Logging Off: Disables logging for the selected query.

   • Enable: Enable the selected query to be run, previewed or scheduled.

   • Disable: Disable the selected query from being run, previewed, or scheduled.

9. For the appropriate query, select the Logging check box to enable logging for that query.

Note. Your changes are saved automatically. There is no Save button.

The kill (cancel) query/timeout mechanism represents each query that is run from Query Manager or Query Viewer as a row in the table PSQRYTRANS. Query Monitor, which implements the kill query/timeout functionality, has a mechanism that looks for orphan rows in PSQRYTRANS. Orphan rows are rows in PSQRYTRANS that do not have a query actively running. Orphan rows can be created because the server crashed while running a query, or other reasons.

Query Monitor only looks for orphan rows for the application server domain that it is running in. For this reason, there could be orphan rows in PSQRYTRANS when there is not an active domain. These rows are not seen by the online query monitoring facility, but could potentially exist in the database. Such rows are rare, and you may use the database query tool to clean them up. You can use the machine and domain fields to determine whether there are rows that should be cleaned up.

Setting Additional Options

Access the Settings page.

Enable Query Timeout

Select or clear this check box to enable or disable the query timeout feature on a systemwide basis. The timeout values are stored in each permission list. Note. The query timeout feature applies to queries that are run in Query Manager and Query Viewer. This feature does not apply to scheduled queries.

Run Query Statistics

Select or clear to this check box to enable or disable the query statistics feature on a systemwide basis. Note. By default, the query statistics feature is disabled. PeopleSoft recommends that you use the query statistics feature only for of analysis, and do not leave it enabled on an ongoing basis. Enabling this feature may compromise the performance and the system may have an increased possibility of query timeouts or may return query results with zero values. PeopleSoft also offers this recommendation to users of queries in reports.