PS360: A Utility to Extract and Present PeopleSoft Configuration and Performance Data

Introduction

Most of a PeopleSoft application is itself stored in the database in PeopleTools tables.  Therefore there is lot of information about the configuration and operation of a PeopleSoft system.  There are also performance metrics, particularly about batch processes.
PS360 is a new tool on which I am working.  It just uses SQL scripts to extract that data to html files, and package them up in a zip file so that they can be sent for further analysis.  The style and method is closely modelled on Enkitec's EDB360 by Carlos Sierra.  This is another free tool used for health check and performance analysis of any Oracle database system.  PS360 aims to gather PeopleSoft specific information that is not presented by EDB360.  It also runs in Oracle's SQL*Plus tool, and so is only available for use with an Oracle database.
Every section of PS360 is just the output of a SQL query, sometimes pre-processing is done in an anonymous PL/SQL block.  It does not install anything into the database, and does not update any table (other than the PLAN_TABLE which is used for temporary working storage).  Each report is in tabular and/or graphical format.  All the charts are produced with the Google chart API.

The output falls into three sections.  

• Configuration: Simple reports of certain configuration tables.
• Performance Metrics: Process Scheduler and Application Engine timings
• Consistency Checks: both within PeopleTools tables and between the PeopleSoft and Oracle catalogues.

Sample PS360 Index Page generated on PeopleSoft Demo Database

Instructions

The tool can be run by anyone with access to the PeopleSoft Owner database user (usually SYSADM).  That user will already have the privilege to read the Oracle catalogue.
Download the tool and unzip it into a directory.

• Navigate to the ps360 (master) directory
• Open SQL*Plus and connect as the owner of the PeopleSoft database (usually SYSADM).
• Execute the script ps360.sql.  
• The output will be written to a zip file in the same directory.  
• Unpack that zip file on your own PC.
• Open the file ps360_[database name]_0_index.html with a browser.

Feedback Enhancements

I am looking for feedback about the tool, and suggestions for further enhancements.
Please either leave comments here or e-mail me at info@go-faster.co.uk.

nVision Performance Tuning: Coalescing Tree Leaves

I have blogged recently about the effectiveness of the Tree Performance options in improving the performance of nVision reports.
However, a consequence of the Tree Performance Access Method suppress join; use literal values is that the resulting SQL in nVision will have a criteria for every leaf on each of the selected nodes on that tree.

There will be an equality condition for each single value leaf. I normally set Selector Options to Ranges of values (BETWEEN), so I get a between condition for each ranged leaf. Behind the scenes, Oracle rewrites between as a pair of inequalities, so there is no difference, but the SQL generated by nVision is slightly shorter.
The following is typical of nVision SQL with these performance options set.

SELECT A.ACCOUNT,SUM(A.POSTED_TOTAL_AMT) 
FROM
 PS_LEDGER A WHERE A.LEDGER='ACTUALS' AND A.FISCAL_YEAR=2015 AND 
  A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND A.CURRENCY_CD='GBP' AND 
  A.STATISTICS_CODE=' ' AND (A.BUSINESS_UNIT=
…
) AND (
  A.DEPTID='C500' OR A.DEPTID='C512' OR A.DEPTID='C117' OR A.DEPTID='C157' OR 
  A.DEPTID='C340' OR A.DEPTID='C457' OR A.DEPTID='C510' OR A.DEPTID='A758' OR 
  A.DEPTID='8220' OR A.DEPTID='A704' OR A.DEPTID='A121' OR A.DEPTID='A110' OR 
  A.DEPTID BETWEEN 'A153' AND 'A154' OR A.DEPTID BETWEEN 'A151' AND 'A152' OR 
  A.DEPTID='A724' OR A.DEPTID BETWEEN 'A131' AND 'A133' OR A.DEPTID='A733' OR 
  A.DEPTID='A217' OR A.DEPTID='A437' OR A.DEPTID='A130' OR A.DEPTID='A134' OR 
  A.DEPTID='A703' OR A.DEPTID='A714' OR A.DEPTID='A218' OR A.DEPTID='A226' OR 
  A.DEPTID BETWEEN 'A135' AND 'A138'
…

A consequence of all the criteria is that Oracle can take a long time to parse the SQL statement. It may only be a few seconds in the case of a single SQL statement, but an nVision report book can consist of thousands of SQL statements.
To produce the following performance profile, I enabled Oracle SQL trace for a report book and profiled the trace. SQL Parse accounted for nearly 8% of the total runtime of this report book, so it can be significant, and can vary widely.

Event Name	% Time	Seconds	Calls	- Time per Call -
				Avg	Min	Max
FETCH calls [CPU]	48.2%	3,699.8440s	16,068	0.2303s	0.0000s	178.0640s
db file sequential read	22.5%	1,728.2101s	4,413,352	0.0004s	0.0002s	0.1294s
SQL*Net message from client [idle]	8.0%	617.7042s	926	0.6671s	0.0002s	61.3147s
PARSE calls [CPU]	7.9%	605.9340s	5,383	0.1126s	0.0000s	11.0500s
…
Total	100.0%	7,681.4428s

Reducing the number of criteria in the SQL will reduce the parse time, but that is determined by the way the tree leaves are defined.
The leafcoal.sql script seeks to address this by repeatedly merging two consecutive leaves on the same tree node into a single ranged leaf where possible. It performs two checks before merging adjacent leaves on the same tree node:

• There is not an intermediate value on the detail field defined in the tree structure record. So if the detail field was DEPT_TBL.DEPTID, the script checks that there are no values of DEPTID on PS_DEPT_TBL that are not currently selected by existing leaves that would be included in the merged leaf.
• There is not another leaf on another node on the tree that would intersect with the merged leaf.

Instructions

leafcoal.sql was written as an anonymous PL/SQL block, so there is nothing to install. It should be run in SQL*Plus connected as the PeopleSoft owner ID (usually SYSADM). It is expected that there are some adjustments to the script that the user may need to make. As delivered, it runs in a test mode that does not update the database but reports on what it would do. Change k_testmode to FALSE to make it update the database.

k_testmode     CONSTANT BOOLEAN := FALSE; /*set this false to perform update*/

The level of output emitted depends on the variable l_debug_variable. 

l_debug_level  INTEGER := 4;

• 1. end of processing message 
• 2. start of processing for tree 
• 3. number of leaves in tree and number of leaves coalesced 
• 4. details of leaves being compressed 
• 5. start and end of each procedure 
• 6. parameters passed to functions 
• 7. number of rows updated/deleted during coalesce 
• 8. dynamic SQL statement 

The script reports on the work it has done. It does not commit its updates. That is left for the user to either commit or rollback.

.(3)Processing SHARE, ,XXX_ACCOUNT,151201                                       
.(4)634 nodes, 2636 leaves                                                      
.(4)1358 leaves coalesced (52%)                                                 
…
(1)Commit changes or rollback                                       

The query at the end of the script determines which trees will be processed and may need to be changed as required. For example, you might choose to coalesce the leaves on

• specific trees,
• most recent effective dated trees,
• trees with literal values performance option

  FOR i IN (
    SELECT DISTINCT d.setid, d.setcntrlvalue, d.tree_name, d.effdt
    FROM   pstreedefn d    
    ,      pstreestrct s
    ,      psrecfielddb f
    WHERE  d.tree_strct_id = s.tree_strct_id
    AND    s.node_fieldname = 'TREE_NODE'
--  AND    d.TREE_ACC_METHOD = 'L' --literal values
    AND    s.dtl_recname = f.recname 
    AND    s.dtl_fieldname = f.fieldname
--  AND    tree_name = 'XXX_ACCOUNT'
  ) LOOP

Conclusion 

The number of leaves coalesced depends entirely on how the trees have been built. At one customer it has produced a reduction of over 50%, at another it was only 10%. The reduction in the number of leaves does produce a corresponding reduction in time spent on SQL parse time during nVision reports.