Practical Application Performance Tuning: An nVision Case Study

Uncategorized
| Dec 2, 2019
I gave this presentation at the UKOUG Techfest 19 conference.  It is closely based on a previous presentation about PeopleSoft nVision performance tuning, and uses the experience of a PeopleSoft project as a case study, so I am also posting here on my PeopleSoft blog.
This video was produced as a part of the preparation for this session.  The slide deck is also available on my website.

Learning about and understanding the (more...)

nVision Bug in PeopleTools 8.55/8.56 Impacts Performance

Uncategorized
| Nov 12, 2019
I have recently come across an interesting bug in nVision that has a significant performance impact on nVision reports in particular and can impact the database as a whole.

Problem nVision SQL

This is an example of the problematic SQL generated by nVision.  The problem is that all of the SQL looks like this. There is never any group by clause, nor any grouping columns in the select clause in from of the SUM().
 (more...)

Which Version Number Corresponds Which PeopleTools Object?

Uncategorized
| Oct 15, 2019
Recently somebody asked me "Our “CRM” values in PSLOCK and PSVERSION are growing tremendously and we don’t know why. We will be opening a case with Oracle but … do you know what that “CRM” value is related to? We are not using the CRM product in our PeopleSoft installation."
There is some documentation on the Oracle Support website, but it is not exhaustive and may not be completely up to date.

Understanding PeopleTools Object Version Numbers

Uncategorized
| Oct 14, 2019
I was recently asked a question about PeopleSoft version numbers, but before I address that directly, I think it would be useful to explain what is their general purpose.

Caching

The PeopleSoft data model and application are mostly stored in the database in PeopleTools tables.  These tables are queried as the application executes.  For example, when you open a component, the component and pages, including all the PeopleCode, the definition of any records (more...)

PeopleTools Table Reference Generator

Uncategorized
| Jun 9, 2019
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

Uncategorized
| Jun 7, 2019
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

Uncategorized
| May 29, 2019
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 psadmin.io, or subscribe with your favourite podcast player, or in iTunes.

PeopleSoft Administrator Podcast: #183 – Effective Performance Monitoring

Uncategorized
| May 8, 2019
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 psadmin.io, or subscribe with your favourite podcast player, or in iTunes.

Effective PeopleSoft Performance Monitoring

Uncategorized
| Mar 8, 2019
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.

Contents

  • 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

    Uncategorized
    | Sep 6, 2018
    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

    Uncategorized
    | Jun 29, 2018
    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

    Uncategorized
    | Apr 14, 2018
    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

    Uncategorized
    | Mar 20, 2018
    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

    Uncategorized
    | Mar 2, 2018
    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

    Uncategorized
    | Jan 31, 2018
    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

    Uncategorized
    | Nov 24, 2017
    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...)