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 (http://www2.go-faster.co.uk/docs.htm#nVision 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!