Understanding Query Administration
This appendix provides an overview of Query Administration and discusses how to use Query Administration.
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 because of 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 enables you to cancel queries that are currently running in Query Manager and Query Viewer as well as enable and disable queries and logging.
Note. You cannot cancel currently running scheduled queries.
See Also
Using Query AdministrationThis section discusses how to:
Administer queries.
Cancel currently running queries.
Set additional options.
Pages Used for Query AdministrationAdministering Queries
Access the Admin page (PeopleTools, Utilities, Administration, Query Administration).
To administer queries:
Use either the predefined search or manual search option to restrict the list of queries that are displayed.
For a predefined search, enter the desired value for the selected search option in the (n)= field and 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.
For a manual search, select to search queries by query name or owner ID.
You can define your search further by selecting either begins with or contains search type.
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.
Review the following statistics for each query that is 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 clear statistics only for untitled queries. Attempting to perform any other action on untitled queries will result in an error message.
For the appropriate query, click the View Log link to view the log for that query.
A message appears if no logs are available.
For the appropriate query, click the View SQL link to view the SQL for that query.
Select the check box for each query on which you want to perform an action.
You can also click the Check All button to select all queries, or click the Uncheck All button to clear all queries.
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: 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.
For the appropriate query, click the Logging button to enable logging for that query.
Note. Your changes are saved automatically. No Save button is available.
Managing Currently Running Queries
Access the Executing page (PeopleTools, Utilities, Administration, Query Administration, Executing).
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 canceled, he or she can look in the Appsrv.log and verify that the PSMONITORSRV service has canceled the selected query.
To manage currently running queries:
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.
In the (n)= field, enter the value that you want 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 selecting either the begins with or contains search type.
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.
Review the following statistics for each query that is listed:
User ID.
Owner ID.
Query name.
Domain ID.
Process identifier.
Host.
Machine name.
Status.
Time started.
Time-out end time.
Number of times killed.
Logging status.
Disabled status.
For the appropriate query, click the View Log link to view the log for that query.
A message appears if no logs are available.
For the appropriate query, click the View SQL link to view the SQL for that query.
Select the check box for each query on which you want to perform an action.
You can also click the Check All button to select all queries, or click the Uncheck All button to clear all queries.
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 enable logging, detailed statistics are 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: Enables the selected query to be run, previewed, or scheduled.
Disable: Disables the selected query from being run, previewed, or scheduled.
For the appropriate query, select the Logging check box to enable logging for that query.
Note. Your changes are saved automatically. No Save button
is available.
The kill (cancel) query/time-out 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/time-out
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 looks for orphan rows only for the application server
domain that it is running in. For this reason, orphan rows could be in PSQRYTRANS
when no active domain exists. These rows are not seen by the online query
monitoring facility, but could potentially exist in the database. Such rows
are rare, and you can use the database query tool to clean them up. You can
use the machine and domain fields
to determine whether rows exist that should be cleaned up.
Setting Additional Options
Access the Settings page (PeopleTools, Utilities, Administration, Query Administration, Settings).