Friday, March 25, 2016

Interview with PeopleSoft Administrator Podcast

I recently recorded an interview with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast. It has been spread over three episodes. There is lots of other good stuff on the website and other episodes that are well worth listening to.

(25 March 2016) #21 - Temporary Tables

(8 April 2016) #23 - The Application Server

(15 April 2016) #24 - Application Server Tuning

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

Sunday, March 06, 2016

nVision Performance Tuning: General Principles

(27.10.2017)  This post has been superceded by a new series of posts.

Over the years I have dealt with performance problems with nVision reporting on General Ledger on various Financials systems in various kinds of businesses.  Different businesses use nVision very differently, and have different challenges, but I have produced an approach that mostly works well at most customers.  I have collected that advice in to a document that I have recently published on my website ( Tuning.Generic.pdf).

The key points are
  • Indexing
    • Effective indexing of LEDGER and LEDGER_BUDG tables to match the analysis criteria of the reports.
    • Enhanced indexing of the PSTREESELECT tables, so that the indexes fully satisfy the queries without the need to visit the tables.
  • Collection of statistics and extended statistics on the PSTREESELECT tables.
  • Using the nVision performance options: 
    • use static selectors instead of dynamic selectors.  It is difficult to maintain up-to-date optimizer statistics on the selector tables with dynamic selectors.
    • simplify SQL statements by replacing joins with literal criteria
    • updated 11.4.2016: reduce SQL parse time by coalescing leaves on trees.
  • I also suggest use of Oracle Fine Grained Auditing to 
    • enhance instrumentation,
    • detect the use of dynamic selectors.
  • Appropriate partitioning of the LEDGER, LEDGER_BUDG and summary ledger tables.
    • compression of historical, and therefore static, partitions
  • Archiving.
    • If the partitioning option is not available, then I strongly recommended that as much historical data as possible is purged from the LEDGER and LEDGER_BUDG tables.
Caveat: This is a general approach, and the document makes general statements.  Every customer is different because their data is different and often their method of analysis differs.  There is always something that requires adjustment or an exception to the general approach.  Your mileage will vary!