DARTS : Query Usages

There is no delivered way in PeopleSoft to track historical query usage.  There are two tables designed for performance analysis that can give us some partial numbers.  However, these tables come with some important limitations described later.  If the limitations are understood, these tables could provide some useful information.    

1. PSQRYSTATS
   This table contains summary information on a query. It has how many times it has been run, the last time it has been run, average run time, etc.  It is essentially one row of data per query name.  It does *NOT* tell you who is running those queries.   It makes it easy to tell if a query has run recently, for example.  Statistics gathering is turned on or off for the system as a whole.  They have been turned on in FNPRD and HRPRD since 2007 and CSPRD and CSRPT a little later.   
   
   
   
2. PSQRYEXECLOG
   This table provides data when a query is executed. Logging can be turned on for a specific query.   However, there is no function for turning detail logging on for every query.
   

Other information:  PeopleSoft Query Administrators have access to this data off the Administration pages.  Both tables are known to PS Query.  

Limitations:  Because these tables were developed for analysis and not auditting, they have some limitations.  

• PSQRYSTATS:  
  • Although logging has been turned on for some time, it could have been turned off deliberately or as part of an upgrade process, leaving gaps.  For example, data is not available in CSRPT from December  2018 through November 20, 2019, and in CSPRD from June 6, 2019 through November 20, 2019.  There probably are other gaps as well.  
  • A typical use in development might be to collect the stats, change the query, delete the stats, rerun the changed queries and compare statistics to see if performance is improved. This is an example of why data may not be historical. 
• PSQRYEXECLOG:  This table is nearly empty.  A typical use might be to turn on logging for a specific query, monitor who is using it for a period of time, and then turn off the logging and clear out the data once the answer is known.  So it can be useful, but more to create answers to targeted, rather than historical, questions.