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.