Query Administration - All about PeopleSoft Query Administration

 

1. As a PeopleSoft technical developer working with PS-Queries ever wondered how to monitor PS-Query performance?
2. How to cancel/kill a currently running PS-Query?
3. How to see the currently running PS-Queries?
4. How to see the average run-time of a query?
5. How and where to find the frequency of a query being run and its last run date?
6. Which record keeps track of PS-Query run statistics?
7. Whether these stats are captured for scheduled queries and queries run from 2 and 3 tiers?

Please read this post further to find answers to these questions. PeopleSoft provides Query Administration component from where one could administer the currently running queries and view usage statistics of queries run earlier. It also provides few more options and details. Please continue reading this post further to know them in detail. Some of this information is also available in Peoplebooks and I have also included my experience in dealing with PS-Queries and underlying PeopleSoft tables which store information on the performance and usage statistics.

1. PeopleTools > Utilities > Administration > Query Administration > Admin

This page allows selection by:

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

The following details and statistics get displayed for each query returned by the above selection:

Owner ID, Query name, Folder, Average time the query has ran, Average number of rows fetched by the query, Number of runs, Last run date and time of the query along with Logging and Disabled statuses, links to View SQL and Log are displayed.

Unsaved queries which are run appear as <UNTITLED>. If there are multiple queries unsaved and ran, they all uppear as a single UNTITLED query. Any attempt to perform action on these Untitled queries could result in some error.
Additional buttons available in the Query Administration page are:

• Logging On and Off buttons
• Enable and Disable buttons allowing or restricting the selected query to be run, previewed or scheduled
• Assign New Owner
• Delete
• Rename,
• Move to Folder and
• Clear Stats/Logs

The most important difference from this page(component) to other general PeopleSoft pages is that, it doesn't have a SAVE button. All the changes are Auto-Saved across this component.

Important points on Statistics:

• The statistics log is updated with data only if the query runs to completion.
• The statistics can be cleared only for Untitled queries.
• Workflow queries are excluded from the statistics.

2. PeopleTools > Utilities > Administration > Query Administration > Executing

This Executing page displays all the currently running queries, providing options to enable or disable queries, enable or disable logging, and to cancel currently running queries.

The following statistics gets displayed:

User Id, Owner ID, Query name, Domain ID, Process identifier, Host and Machine names, Status, Time started, Time-out end time, Number of times killed, Logging and Disabled statuses.

PSQRYTRANS Table:
For every PS-Query run from either the Query Manager or Query Viewer a row is added to the PSQRYTRANS table. Query Monitor, which implements the kill query/time-out functionality, has a mechanism that looks for orphan rows in PSQRYTRANS. Orphan rows in PSQRYTRANS table represent queries that are not actively running. Orphan rows can be created because the server crashed while running a query, or due to 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 record when no active domain is present. These rows are not seen by the online query monitoring facility, but could potentially exist in the database. Such rows are rare, and using the database query tool they can be cleaned up. The machine and domain fields can be used to determine whether rows exist and if that should be cleaned up.

PSQRYEXECLOG Table:

Alternatively one could also look upon PSQRYEXECLOG table which keeps track of the queries executed.

However, PSQRYEXECLOG table does not capture data for PS-Queries run from Client PSQuery 2-tier and 3-tier. Even if there are statistics recorded in PSQRYEXECLOG they may not be complete and the reason why they could be captured is because some user could have used the PIA Query Administration Page to change a Query Field value EXECLOGGING in the PSQRYDEFN Table.

This is because PSQRYEXECLOG record is designed for running Query in PIA (4-tier) only, and not designed to be used by 2 or 3-tier Query.  For this reason, all statistic results from 2 or 3-tier Query should be discarded.

What happens when a query is killed?

• The NUMKILLS field in PSQRYSTATS record gets incremented, and this is also reflected in the query stats GUI. 
• A new row gets written in the PSQRYEXECLOG record.
• In the PSQRYEXECLOG record, the field KILLEDREASON reflects why the query was killed. It takes in two values  – 'T' for timed out and 'K' for killed.  
• EXECDTTM field in PSQRYEXECLOG record is set to the time the query was killed.  This is also reflected in the exec log GUI.

What is a Query Monitor?

• The query monitor is an appserver function which runs every minute to find out queries that have reached their time limit, or have been marked to be killed.  
• The query monitor will check for orphaned rows – ie rows that have no timeout, and the appserver process doesn’t exist or isn’t running a query. 
• As it finds a row to kill or timeout it will ensure that the process is really a PSAPPSRV or PSQRYSRV that is running a query.

How to check if a cancelled query has really got cancelled? In other words, how to verify that a query has got cancelled?
Take a look into the Appsrv.log file and verify that the PSMONITORSRV service has canceled the selected query.
Currently running scheduled queries cannot be canceled.

3. PeopleTools > Utilities > Administration > Query Administration > Settings
There are couple of additional settings in the above navigation:

• Enable Query Timeout - The timeout values are stored in each permission list. The value specified in the time out for every permission list represents the number of minutes that a query can run before being timed out. For specifying no time limit enter the number 0 which represents an infinite timeout value.  The query timeout value that is used is the greatest number, or 0 if it exists, for all permission lists of all roles that a user belongs to.
• Run Query Statistics - PeopleSoft recommends to use this feature only for analysis, and leaving this feature enabled may compromise the performance and the system may have an increased possibility of query time-outs or may return query results with zero values.

Note:

1. The query time-out feature applies to queries that are run in Query Manager and Query Viewer. This feature does not apply to scheduled queries. 
2. The QRYTIMEOUT field in PSQRYFLAGS table acts as a global flag that controls the enabling of the query timeout functionality.

PeopleTools Performance Utilities

 

In PeopleTools 8.4, PeopleSoft has started to introduce some utilities to help detect and identify performance problems in the online part of the application. This chapter discusses some aspects of these utilities.

This chapter does not seek to replace PeopleSoft’s documentation. Instead, you are advised to read this chapter in conjunction with PeopleBooks.

 

1UERY METRICS IN 0EOPLE4OOLS 8.4

In PeopleTools 8.4, PeopleSoft introduced two new methods of collecting query execution times: query statistics and query logging.

 

Query Statistics

Whenever a query is executed via the Pure Internet Architecture (PIA), the new average execution time, fetch time, and number of rows for that query across all executions are either inserted into or updated on the table PSQRYSTATS, as shown in Listing 10-1.

 

Listing 10-1. How PeopleSoft Updates Query Statistics

 

UPDATE PSQRYSTATS

SET    AVGEXECTIME = (AVGEXECTIME * EXECCOUNT + :4)/(EXECCOUNT + 1),

      AVGFETCHTIME = (AVGFETCHTIME * EXECCOUNT + :5)/(EXECCOUNT + 1),

      AVGNUMROWS = (AVGNUMROWS * EXECCOUNT + 0)/(EXECCOUNT + 1),

      LASTEXECDTTM = TO_DATE(SUBSTR(:3, 0, 19),'YYYY-MM-DD-HH24.MI.SS'),

      EXECCOUNT=EXECCOUNT + 1 WHERE OPRID = :1

AND    QRYNAME = :2

The limitation of this technique is that you can only see an average for a query since the averages were last deleted from the table. Query logging, described in the next section, provides information about each query execution.

The content of this table can be queried via the PeopleTools ⮞ Utilities ⮞ Administration ⮞ Query Administration component.

This example illustrates the fields and controls on the Admin page.

To administer queries:

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

Although the component can list the top n queries by cumulative execution time, it does not calculate total execution time. Queries are listed by average rather than cumulative execution time. However, it is easy to query this table in SQL, as shown in Listing 10-2.

 

Listing 10-2. Top Ten Queries by Cumulative Execution Time (top10qry.sql)

 

SELECT * FROM (
SELECT RANK() OVER (ORDER BY tottime DESC ) as qryrank
,      oprid, qryname, totexec, tottime
,      100*RATIO_TO_REPORT(tottime) OVER () as pcttime FROM     (SELECT oprid, qryname
,       SUM(execcount) totexec
,        SUM(execcount*avgexectime) tottime FROM psqrystats
GROUP BY oprid, qryname
) a
)
WHERE qryrank <= 10

/

The results of the query are shown in Listing 10-3.

 

Listing 10-3. Top Queries by Cumulative Execution Time (top10qry.lst)

 

QRYRANK OPRID     QRYNAME                         TOTEXEC   TOTTIME PCTTIME

 

1	DMKPUB		4		2.8		51.3
2 PS	DMK		7		2.4		43.6
3	PER701 DEPT_TBL		1		0.3		5.1

Use the Executing page (QRY_KILL) to cancel currently running queries, and enable or disable queries and logging.

Navigation

PeopleTools > Utilities > Administration > Query Administration > Executing

Image: Executing page

This example illustrates the fields and controls on 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 administrators needs to verify that a query has been canceled, they can look in the Appsrv.log and verify that the PSMONITORSRV service has canceled the selected query.

To manage 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 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.

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

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 SQL for that query.

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

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

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

Note: Your changes are saved automatically and the Save button is not available.

The kill query (cancel query or 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 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.