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.

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.

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.

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.

                          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.