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 to data warehouse queries. The ledger, ledger budget or summary ledger tables are the fact tables.

The ledger tables are analysed by their attribute columns. There are always literal conditions on the fiscal year and accounting period, there is usually a literal condition on currency code.  Then there are criteria on some of the other attributes.  I will take an example that analyses the ledger table in three dimensions: BUSINESS_UNIT, ACCOUNT and CHARTFIELD1, but there are many other attribute columns on the ledger tables.  These attributes are defined in lookup tables in the application, but their hierarchies are defined in trees.

nVision reports use the trees to determine which attribute values to report.  A report might report on a whole tree, or particular nodes, or branches of a tree.  nVision joins the tree definition to the attribute table and produces a list of attributes to be reported.  These are put into working storage tree selector tables (PSTREESELECT01 to 30).  The choice of selector table is controlled by the length of the attribute column.  BUSINESS_UNIT is a 5 character column so it goes into PSTREESELECT05. CHARTFIELD1 and ACCOUNT are 10 character columns so they use PSTREESELECT10.  These selector tables form the dimensions in the queries.

Here is an example of a SQL statement generated by nVision.  The tree selector 'dimension' tables are joined to the ledger 'fact' table.

SELECT L.TREE_NODE_NUM,L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM   PS_LEDGER A
,      PSTREESELECT05 L1
,      PSTREESELECT10 L
,      PSTREESELECT10 L2
WHERE  A.LEDGER='ACTUALS'
AND    A.FISCAL_YEAR=2016
AND    A.ACCOUNTING_PERIOD BETWEEN 1 AND 11
AND    L1.SELECTOR_NUM=30982 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05
AND    L.SELECTOR_NUM=30985 AND A.CHARTFIELD1=L.RANGE_FROM_10
AND    L2.SELECTOR_NUM=30984 AND A.ACCOUNT=L2.RANGE_FROM_10
AND    A.CURRENCY_CD='GBP'
GROUP BY L.TREE_NODE_NUM,L2.TREE_NODE_NUM

This SQL looks simple enough, but there are various complexities

• The tree selector tables are populated at runtime.  Many dimensions can be stored in each tree selector table, each keyed by a different SELECTOR_NUM.
• Selectors can be static or dynamic.  In dynamic selectors, the data is only stored temporarily for the lifetime of the report and will be deleted when it completes.  So immediately, there is a challenge of keeping statistics up to date, and even then Oracle doesn't always manage to find an effective execution plan.
• Different selectors will have different numbers of rows, so the statistics have to describe that skew.
• Different nVision reports and even different parts of the same report generate different statements that can use different combinations of attribute columns.  The number of dimensions can vary, I have seen systems that use as many as five different trees in a single query.
• Then the database needs to find the relevant rows on the ledger table for the dimensions specified as efficiently as possible.

This very quickly becomes a difficult and complex problem.  This series articles works through the various challenges and describe methods to overcome them.  Not all of them are applicable to all systems, in some cases, it will be necessary to choose between approaches depending on circumstances.

nVision Performance Tuning: Table of Contents

This post is an index for a series of blog posts that discuss how to get good performance from nVision as used in General Ledger reporting.

• Introduction
• nVision Performance Options
• Effects of Performance Options
• Indexing of Ledger, Budget and Summary Ledger Tables
• Partitioning of Ledger, Budget and Summary Ledger Tables
• Additional Oracle Instrumentation for nVision
• Logging Selector Usage
• Analysis of Tree Usage  with the Selector Log
• Interval Partitioning and Statistics Maintenance of Selector Tables
• Compression without the Advanced Compression option
• Maintaining Statistics on Non-Partitioned Selector Tables
• Excel -v- OpenXML
• Hinting nVision with SQL Profiles

Related Posts:

• Setting Oracle Session Parameters for Specific Process Scheduler Processes
• Running nVision in a Mixed OpenXML/Excel mode
• Practical Application Performance Tuning: An nVision Case Study (Video)

The current versions of scripts mentioned in the series are available on GitHub.

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.