nVision Performance Tuning: 7 Analysis of Tree Usage with the Selector Log

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Over time, the selector log will build up a picture of how each tree is used in a system. Here are two examples of how it can be used.

You may look at a piece of SQL generated by nVision, it will have a literal value for the selector number, and you want to know about that particular selector.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">REM treeanal.sql
WITH t as (
SELECT DISTINCT d.tree_name, s.dtl_fieldname, d.tree_acc_method, d.tree_acc_Selector, d.tree_acc_sel_opt
FROM pstreedefn d, pstreestrct s
WHERE d.tree_Strct_id = s.tree_strct_id
), l as (
SELECT *
FROM ps_nvs_treeslctlog l
WHERE l.selector_Num = &selector_num
)
SELECT l.*, t.dtl_fieldname, t.tree_acc_method, t.tree_acc_Selector, t.tree_acc_sel_opt
FROM t, l
WHERE t.tree_name = l.tree_name
/

Now you can see various pieces of information about the selector and the report in which it was used.

  • Process instance of the report, although it is also in the ACTION string
  • Length of the selector.  Here it was 10 characters so it was in PSTREESELECT10.
  • The number of rows inserted into the selector.  This is useful if you want to recreate the conditions at runtime by populating the selector table manually.
  • Time at which the selector was populated.
  • Session module, usually the process name.  
  • Session action string, which contains the process instance, report ID and business unit.
  • The client info string, containing the operator ID, database name, the name of the host running nVision and the name of the executable.
    • PSNVS is nVision running on Microsoft Excel
    • PSNVSSRV is nVision running in OpenXML
    • psae is a PS/Query using the IN TREE operator that has been scheduled to run on the Process Scheduler.
    • PSQED is the windows query client running in 2-tier mode and using an IN TREE operator.
    • PSSAMSRV indicates either nVision or PS/Query running either through the PIA or on the windows client in 3-tier mode.
  • Status of the selector.  X indicates that the selector has been deleted and the partition has been dropped.
  • Name of the tree.
  • Name of the schema in which the selector table is located.
  • Partition name in the tree selector table in which the data was held.  This will be blank if the partition has been dropped.
  • The current values of the three tree selector flags on the tree definition are also reported.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 75%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECTOR_NUM PROCESS_INSTANCE Len   NUM_ROWS TIMESTAMP                    MODULE       APPINFO_ACTION
------------ ---------------- --- ---------- ---------------------------- ------------ ----------------------------------------------------------------
CLIENT_INFO S TREE_NAME OWNERID PARTITION_NAME JOB_NO DTL_FIELDNAME T T T
---------------------------------------------------------------- - ------------------ -------- -------------------- ---------- ------------------ - - -
10233 1780069 10 362 10-NOV-17 02.40.50.755038 AM RPTBOOK PI=1780069:UKGL123I:UK001
GBNVISION,PSFINPRD,UKLONWIN001,,PSNVSSRV.EXE, X UKGL_ACCOUNT SYSADM 33052 ACCOUNT J D S

The following query aggregated log entries to report the number of times each tree was used over the last 7 days, and provide various statistics about the numbers of rows extracted from trees into the selector tables, and the current tree performance options.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">REM tree_usage.sql
WITH t AS (
SELECT DISTINCT d.tree_name, s.dtl_fieldname, d.tree_acc_method
, d.tree_acc_Selector, d.tree_acc_sel_opt
FROM pstreedefn d, pstreestrct s
WHERE d.tree_Strct_id = s.tree_strct_id
), l AS (
SELECT tree_name, length
, COUNT(*) num_uses
, MIN(num_rows) min_rows
, AVG(num_rows) avg_rows
, MEDIAN(num_Rows) med_rows
, MAX(num_rowS) max_rows
, STDDEV(num_Rows) stddev_rows
, SUM(num_rows) sum_rows
, COUNT(distinct process_instance) processes
FROM ps_nvs_treeslctlog l
WHERE num_rows>0
AND timestamp >= sysdate-7
GROUP BY tree_name, length
)
SELECT l.*, t.dtl_fieldname, t.tree_acc_method, t.tree_acc_Selector, t.tree_acc_sel_opt
FROM t, l
WHERE t.tree_name = l.tree_name
ORDER BY sum_rows
/

The default recommendation is that all trees should use:

  • Literal values where possible when working with less than about 2000 rows in the selector.  However, where more than 2000 rows it may be better to join the table due to parse and execution overhead of each criterion.
  • Dynamic selectors 
  • Single Value joins 

This report can help to identify trees where extreme volumes mean that different options should be considered.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">                          Num    Min Average Median    Max Std Dev       Sum   Num
TREE_NAME Len Uses Rows Rows Rows Rows Rows Rows Procs DTL_FIELDNAME T T T
------------------ --- ------ ------ ------- ------ ------ ------- --------- ----- ------------------ - - -

CORP_ACCT 10 5 1147 2839 2616 6668 2263 14194 1 ACCOUNT J D S
FUNCTION 10 480 9 32 35 35 8 15474 43 CHARTFIELD2 L D S
INT_SUP 6 7 225 2463 2838 2838 987 17243 1 PRODUCT L D S
STAT_PRODUCT 6 8 2889 2889 2889 2889 0 23112 1 PRODUCT J D S
AFFILIATE 5 43 215 576 509 938 223 24789 15 AFFILIATE L D S
INT_GAAP_CON 5 62 82 486 522 730 225 30153 10 BUSINESS_UNIT L D S
BU_GAAP_CON 5 96 44 619 614 731 115 59461 48 BUSINESS_UNIT L D S
STAT_ACCOUNT 10 45 23 4204 6516 6516 2905 189182 6 ACCOUNT J D S
INT_REP1 10 135 149 1563 1664 1664 379 211005 1 CHARTFIELD1 L D S
COMBO_CODE 10 172 17 1592 1532 2430 809 273846 18 CHARTFIELD1 L D S
UKGL_ACCOUNT 10 2586 2 1713 1147 7797 1793 4430262 110 ACCOUNT J D S
  • The account trees have been set to join the tree to the ledger table rather than literal values because sometimes in excess of 6000 rows are extracted. A query with 6000 literal terms would be extremely large, take time to generate in nVision, and time on the database to parse and execute each criterion. 
  • STAT_PRODUCT has been set to join partly because it is large, it always extracts 2889 rows, but also because the whole tree is extracted every time so it does not cut down the result set.