PeopleTools Table Reference Generator

Like many other PeopleSoft professionals, I spend a lot of time looking at the PeopleTools tables because they contain meta-data about the PeopleSoft application. Much of the application is stored in PeopleTools tables. Some provide information about the Data Model. Many of my utility scripts reference the PeopleTools tables, some of them update them too. Therefore, it is very helpful to be able to understand what is in these tables. In PeopleSoft for the Oracle (more...)

PS360 enhancement: Added report of DDL models

I have written several blogs and presentations recently about how and how not to collect statistics in PeopleSoft.
  • If you are going to continue to use DBMS_STATS in the DDL model then
  • Do not specify ESTIMATE_PERCENT because it disables the hash-based number-of-distinct-values calculation, forcing it to go back to the COUNT(DISTINCT ...) method that requires a sort, and may not produce accurate values because it only samples data.
  • (more...)

PeopleSoft Adminstrator Podcast: #184 – nVision Performance

I recorded a second podcast with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast, this time about nVision.

(10 May 2019) #184 – nVision Performance

You can listen to the podcast on, or subscribe with your favourite podcast player, or in iTunes.

PeopleSoft Administrator Podcast: #183 – Effective Performance Monitoring

I recently recorded a podcast with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast, this time about instrumentation, monitoring the performance of PeopleSoft system, and Performance Monitor.  There is also just a little about cursor sharing.

(3 May 2019) #183 – Effective Performance Monitoring

You can listen to the podcast on, or subscribe with your favourite podcast player, or in iTunes.

Effective PeopleSoft Performance Monitoring

This advice note describes how to configure PeopleSoft systems on Oracle so that performance metrics are collected that are useful performance monitoring and subsequent performance tuning.


  • Oracle RDBMS Instrumentation
  • On-Line Component/Page Information
  • Application Engine Step Information
  • Cobol/nVision Instrumentation
  • nVision Layout Instrumentation
  • 2nd Database Connection Instrumentation
  • PeopleTools Performance Metrics
    • Cobol and Application Engine Batch Timings
    • PeopleSoft Performance Monitor

    Summary of Recommendations

    • Set EnableAEMonitoring=1 in all Application Server and process scheduler domains in order to enable (more...)

    How Not to Collect Optimizer Statistics in an Application Engine Program

    I was told about a PeopleSoft customer experiencing an Oracle error when collecting statistics during an Application Engine.
    ORA-06533: Subscript beyond count 
    ORA-06512: at "SYS.DBMS_STATS", line 36873
    It is possibly a manifestation of a database bug. The workaround was not to use AUTO_SAMPLE_SIZE, so instead, this customer initially coded an explicit call to DBMS_STATS.GATHER_TABLE_STATS with a specific sample size.
    This blog is not about the bug, but how to manage the (more...)

    Managing Cost-Based Optimizer Statistics for PeopleSoft

    I gave this presentation to UKOUG PeopleSoft Roadshow 2018

    PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics use the cost-based optimizer.

    I have previously written and blogged on this subject.  This presentation focuses exclusively on the Oracle database and draws together the various concepts into a single consistent picture and makes clear recommendations for Oracle 12c that will help you work with the cost-based optimizer, rather than (more...)

    Application Engine in Process Scheduler: PSAESRV Server Process -v- Standalone PSAE executable

    Whether to use the Application Engine server process (PSAESRV) in the process scheduler tuxedo domain or the standalone PSAE executable is a frequently discussed point amongst PeopleSoft administrator.  Over the years, I have written various things on the subject.  I am going to draw them together in this blog, and restate Oracle’s now clear advice about when to use which option.

    In PeopleTools 8.4, the Process Scheduler became a fully fledged Tuxedo (more...)

    Resetting High Water Marks on On-line Temporary Table Instances

    PeopleSoft has always used regular database tables for temporary working storage in batch processes.   Up to PeopleTools 7.x working storage tables were shared by all instances of a program.  That led to consistent read contention when multiple processes concurrently used the same table, and much higher high water marks that increased durations of full scans.
    From PeopleTools 8, many copies of each temporary working storage table are created.  Application Engines that (more...)

    Setting Oracle Session Parameters for Specific Process Scheduler Processes

    This note describes a mechanism for setting initialisation parameters for specific processes run on the process scheduler. I will demonstrate it relation to nVision, but it has general application in PeopleSoft.
    A table is used to hold metadata that described what setting is applied to which processes. A trigger on the process scheduler request table PSPRCSRQST reads that data and alters the session setting. This approach is easier to adjust and understand that static PL/SQL (more...)

    PeopleSoft and Invalid Views in the Oracle Database

    I was listening to the section on Invalid Views in PSADMIN Podcast #117 (@19:00). Essentially, when you drop and recreate a view that is referenced by a second view, the status on the second view in the database goes invalid. This is not a huge problem because as soon as you query the second view it is compiled. However, you would like to know whether any change to a view prevents any dependent views from (more...)

    nVision Performance Tuning: 9. Using Compression without the Advanced Compression Licence

    This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

    Table compression can significantly decrease the size of tables and reduce the volume of I/O required to retrieve data.  Compression of the ledger, ledger budget, and summary ledger tables can significantly improve the performance of scans in nVision.
    The Advanced Compression licence enables compression features to be used where segments are still updated (more...)

    nVision Performance Tuning: Introduction

    This blog post is the first in a series that discusses how to get good performance from nVision as used in General Ledger reporting.

    PS/nVision is a PeopleTools technology that extracts data from the database and places it in an Excel spreadsheet (see PS/nVision Overview).  Although PS/nVision can be used with any PeopleSoft product, it is most commonly used in Financials General Ledger.

    The SQL queries generated by nVision are, at least conceptually, similar (more...)