Monday, April 12, 2021

Running nVision in a Mixed OpenXML/Excel mode

This blog post follows on from a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.
Updated 20.4.2021: OpenXML is an alternative method for running PeopleSoft nVision introduced in PeopleTools 8.54.  It is recommended by Oracle and is the default configuration in the process scheduler.  It generally outperforms Excel because it is a lightweight process and it evades Excel's concurrency limitations.
Only one Excel nVision will run concurrently on a Windows server because Excel itself is a single-threaded process since Microsoft Excel 2010.  If you need to run 10 concurrent nVision reports you would need 10 windows servers each running a process scheduler that will run a single nVision report in Excel mode.  One of the major advantages of OpenXML is that it is not subject to this restriction.  See also:

What is the Problem?

I have worked with PeopleSoft Financials customers where it became apparent that some of their nVision layouts did not run correctly on OpenXML, and until they could be redeveloped they had to run on Excel.  The problems seem to be around pagination and nPlosion.  Also, some variables are not populated when the layouts are run in OpenXML.  For example, the report on the left was run in OpenXML, and the one on the right was run in Excel.
I have also found a very few layouts, that contrary to expectation, perform better on Excel than OpenXML.
Reverting to Excel is the only other option, but to do so across the board would have a significant performance impact.  However, my experience suggests that customers can run the majority of their layouts perfectly successfully on OpenXML, but a relatively small proportion still has to be run on Excel.  
A mixed mode of operation is therefore a good option, where nVision is run on OpenXML by default, but certain reports and report books are run on Excel.  It avoids having to train end-users to run certain reports on certain process schedulers.  
This blog sets out a method by which reports using certain nVision layouts are automatically redirected to certain process schedulers that run Excel nVision by a combination of configuring process categories and two database triggers to adjust the request records before they are picked up by the process schedulers.

Excel nVision Process Schedulers and OpenXML nVision process schedulers

Whether nVision is run in Excel or OpenXML mode is determined by the UseExcelAutomation variable in the nVision section of the process scheduler configuration file (psprcs.cfg).  It applies to all nVision process run by that scheduler.  However, this variable is not documented well in the PeopleTools manual.  It takes the following values:
…
[nVision]
;=========================================================================
; General settings for nVision
;=========================================================================
…
UseExcelAutomation=2
…
Today, most PeopleSoft systems run most of their batch processes on Linux/Unix servers.  nVision is one of the few PeopleSoft process types that had to run on Windows, and that is still the case for Excel nVision.  It is typical for these PeopleSoft systems to have several Windows servers dedicated to nVision execution.  To run in a mixed-mode, it is necessary to have an Excel nVision process scheduler and an OpenXML nVision process scheduler on each server.

Process Categories 

In the past, I have suggested creating additional process types, but I think it is easier and more in keeping with the process scheduler structure to create additional process definitions and process categories.  It is necessary to create two new process categories (in PeopleTools->Process  Scheduler->System Settings)

Servers

Process Schedulers (or Servers) should be created in pairs for each physical server available.  In this example, I have created PSNT_X1 to run the OpenXML category and PSNT_E1 to run the Excel category.  
Note that:
  • Maximum concurrency for the Excel server has been reduced to 1.
  • The maximum number of API aware processes for PSNT_E1 has also be reduced to 1.
  • The 'Default' category is disabled on both PSNT_E1 and PSNT_X1.
  • These servers only run nVision and PSJob process types,
  • They load balance within the same operating system (although this is not appropriate for PSNT_X1 if you also run OpenXML on other non-Windows process schedulers).

Process Definitions

The various nVision process definitions are allocated to the nVisionOpenXML process category so they run on those process schedulers.

The whole configuration can be set up manually through the Process Scheduler components. However, I have also written a couple of scripts to manage the configuration that should ensure consistency.
  • nvisionprcsdefnclone.sql updates process definitions and creates the cloned server definitions.  It can also be configured to create multiple pairs of server definitions etc.
  • nvision_prcscategory.sql updates the concurrency on existing process definitions.

Scripts

When a process request is submitted either in the PeopleSoft application or an enterprise scheduling tool (such as AutoSYS or Control-M), request records are inserted into:
  • PSPRCSQUE is polled by the process schedulers when looking for work.
  • PSPRCSRQST reports the status of processes and is exposed in the Process Monitor component in PeopleSoft.
These two tables are siblings, both keyed on PRCSINSTANCE.  Both have the following columns:
  • PRCSNAME determines the name of the process to be run.
  • PRCSCATEGORY specifies the category of the process in the request.

Triggers

I have created two pairs of almost identical database triggers that fire on insert into each of these tables that will make certain changes to request for nVision layouts that should be run on Excel:
  • One pair of triggers updates requests for nVision-Report and nVisionReportBook process types.  The other pair update the PSJob request record where nVision reports are run in a Jobset.
  • The triggers change the process category from nVisionOpenXML to nVisionExcel where necessary.  It is changing the process category rather than the name of the process that makes the difference to which process scheduler picks up the request.
  • If the process request specifies a particular process scheduler (specified in SERVERNAMERQST) that cannot run the updated process category, then the requested server name is blanked out so that the master process scheduler reassigns the request.
If any one layout in a ReportBook is listed as requiring to be run on Excel, then the whole ReportBook is moved to Excel because the report book runs as a single process.  This may become a reason to split ReportBooks where only some layouts require to run on Excel.
Script gfc_nvsion_excel_redirect_triggers.sql creates the meta-data table PS_NVS_REDIR_EXCEL, and the triggers on PSPRCSRQST and PSPRCSQUE.
  • The corresponding triggers on PSPRCSRQST and PSPRCSQUE are almost identical except for the name of the table and one additional column that is updated on PSPRCSQUE (apart from the conditionally compiled debug code).  I will only exhibit the trigger on PSPRCSRQST in this post.
  • Normally, one would not code WHEN OTHERS THEN NULL exceptions handlers in PL/SQL.  However, I do this on triggers on process scheduler tables to prevent any error being raised causing the scheduler to crash.
  • Conditionally compiled debug code in the trigger is not compiled if the mydebug flag is set to FALSE.

ALTER SESSION SET PLSQL_CCFLAGS = 'mydebug:FALSE';

CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_excel_redirect_rqst 
BEFORE INSERT ON s.psprcsrqst
FOR EACH ROW
WHEN (new.prcstype IN('nVision-Report','nVision-ReportBook'))
DECLARE
  l_excel INTEGER := 0;
  l_maxconcurrent INTEGER := 0;
  k_prcscategory CONSTANT VARCHAR2(15) := 'nVisionExcel';
BEGIN
  $IF $$mydebug $THEN dbms_output.put_line('Entering Trigger psoft.gfc_nvision_excel_redirect_rqst'); $END

  IF :new.prcstype = 'nVision-ReportBook' THEN
    --check for reportbook running report that uses layout on Excel list
    SELECT 1
    INTO   l_excel
    FROM   psnvsbookrequst b
    ,      ps_nvs_report n
    ,      ps_nvs_redir_excel e
    WHERE  b.oprid = :new.oprid
    AND    b.run_cntl_id = :new.runcntlid
    AND    b.eff_status = 'A'
    AND    n.business_unit = b.business_unit
    AND    n.report_id = b.report_id
    AND    n.layout_id = e.layout_id
    AND    e.eff_status = 'A'
    AND    rownum=1;
  ELSE
    --look in command line for report running layout on Excel list
    SELECT 1
    INTO   l_excel
    FROM   psprcsparms p
    ,      ps_nvs_report n
    ,      ps_nvs_redir_excel e
    WHERE  p.prcsinstance = :new.prcsinstance
    AND    n.report_id = substr(regexp_substr(p.parmlist,'-NRN[^ ]+'),5)
    AND    n.layout_id = e.layout_id
    AND    e.eff_status = 'A'
    AND    rownum=1;
  END IF;

--set category of request
  :new.prcscategory := k_prcscategory;

  --get max concurrency of new category on new server
  SELECT maxconcurrent
  INTO   l_maxconcurrent
  FROM   ps_servercategory
  WHERE  prcscategory = :new.prcscategory
  AND    servername = :new.servernamerqst;

  --if request assigned to server where it cannot run blank out server assignment and allow load balancing to determine it
  IF l_maxconcurrent = 0 THEN
    :new.servernamerqst := ' ';
  END IF;

  $IF $$mydebug $THEN dbms_output.put_line('set process name to '||:new.prcsname); $END
EXCEPTION
  WHEN no_data_found THEN 
    $IF $$mydebug $THEN dbms_output.put_line('No excel redirect found'); $ELSE NULL; $END
  WHEN others THEN 
    $IF $$mydebug $THEN dbms_output.put_line('Other Error'); $ELSE NULL;  $END
END gfc_nvision_excel_redirect_rqst;
/
show errors

MetaData

The script gfc_nvsion_excel_redirect_metadata.sql populates a list of nVision layouts that must run on Excel into the metadata table PS_NVS_REDIR_EXCEL. You have to enter your own list of nVision layouts here.
REM gfc_nvsion_excel_redirect_metadata.sql
REM (c)Go-Faster Consultancy 2021
REM load metadata of layouts that have to run on Excel rather than OpenXML

spool gfc_nvsion_excel_redirect_metadata

INSERT INTO ps_nvs_redir_excel VALUES ('EXCELNVS','A');
commit;

spool off

Other Scripts

  • excel_only_reportbooks.sql determines which nVision ReportBooks contain only some layouts that require to be run on nVision.  These are candidates to be split up.
                                                              ReportBooks with both Excel and OpenXML nVision layouts

                                                   Number of
                                              All      Excel
OPRID      RUN_CNTL_ID                    Layouts    Layouts Excel Layouts                  OpenXML Layouts
---------- ------------------------------ ------- ---------- ------------------------------ ----------------------------------------------------------------------
BATCH      ADHOC_NVISION                        8          1 GLXXXO21                       GLXYZD03, GLXXXO03, GLXXXO05, GLXXXO22, GLXXXO23, GLXXXO31, GLXXXO32
BATCH      ADHOC_09062016                       3          1 ZYXVIS14                       ZYXVBS14, ZYXVIS12
BATCH      GLXXX_GLXXXO02_ABDC_YYY              2          1 GLXXXO02                       GLXXXO28
BATCH      GLXXX_GLXXXO21_ABDC_YYY              3          2 GLXXXO21, GLXXXO98             GLXXXO71
BATCH      GLXXX_ZYXB4080_M000_ZZZ             10          2 ZYXVBS64, ZYXVIS14             ZYXVBS04, ZYXVBS14, ZYXVBS14_LED, ZYXVBS16, ZYXVBS16_LED, ZYXVBS54, ZB
                                                                                            UVIS04, ZYXVIS16
  • nvision_processsmonitor.sql reports on nVision ReportBooks scheduled in the last 24 hours, their process category and the excel layouts within them, so that you can verify that the configuration is working.
  •                                                        Process         Server   Server   Server
    PRCSINSTANCE RUNDTTM                      PRCSNAME     Category        Request  Run      Assign   RUNCNTLID                      STA EXCEL_LAYOUT_IDS
    ------------ ---------------------------- ------------ --------------- -------- -------- -------- ------------------------------ --- ------------------------------
        12345680 31-MAR-21 07.42.51.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO10_ABDC_YYY        OK  GLXXXO10
        12345681 31-MAR-21 07.43.25.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO21_ABDC_YYY        OK  GLXXXO21, GLXXXO98
        12345683 31-MAR-21 08.06.42.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO98_ADHOC           OK  GLXXXO98
        12345684 31-MAR-21 08.32.12.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO21_ABDC_YYY        OK  GLXXXO21, GLXXXO98
        12345685 31-MAR-21 09.18.23.000000 AM FBRPTBK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYAD06_ABDC_YYY        OK
        12345686 31-MAR-21 09.20.01.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYBP00_ABDC_YYY        OK
        12345687 31-MAR-21 09.22.21.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYAD03_ABDC_YYY        OK
        12345688 31-MAR-21 09.23.11.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLVLAD03_ABDC_XXX        OK
        12345689 31-MAR-21 09.24.18.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_ZYXAB001_M000_ZZZ        OK
    All the scripts mentioned in this blog have been included in my nVision Github repository.

    Tuesday, December 01, 2020

    Tuning Dynamically Generated SQL from PeopleSoft COBOL and Application Engine

    When working on a performance issue, you may reach the point where you want to introduce one or more hints into a SQL statement.  It is often tempting to simply add the hints to the source code.  Doing so may appear to be simpler.  That is often the case with Application Engine programs, however, it is much more difficult in PeopleSoft COBOL programs.  

    A strong argument against such code change is that having made it, you have also to functionally test the change and push it through the entire release management process to get it into production.  Then, should you ever want to change or remove the hints, you have to go through the entire process again.

    Oracle provides several mechanisms to define a database object containing a hint or set of hints and to apply them to matching SQL statements.  These mechanisms work best with SQL that uses bind variables rather than literals.  If an otherwise identical SQL statement has different literal values then it has a different SQL_ID and is treated by the database as a new statement.  SQL Plan Baselines, SQL Patches and SQL Profiles match to the specific text of a SQL statement.  Different literal values will prevent matching and defeat these mechanisms.  These techniques must still be tested and migrated in a controlled manner, but they have no functional impact and so only testing of performance is needed.

    SQL Profiles can also perform forced matching, where statements that are similar except for literal values are matched.  However, note that they also require licencing of Tuning Pack.

    Some parts of PeopleTools and the PeopleSoft applications are better than others at producing sharable SQL with bind variables.  Most of the SQL generated by the component processor uses bind variables.  In Application Engine, if the ReUse Statement property is not set, which it is not by default, the %BIND fields are substituted with their literal values in the SQL statement.  However, if the property is set then %BIND fields become bind variables.  Over the years much more PeopleSoft code has been delivered with this attribute enabled.  Doing so has significant performance benefits (see Performance Benefits of ReUse Statement Flag in Application Engine).  

    Where, under normal circumstances, I might use a baseline or patch to inject a hint or profile of hints into a particular SQL statement (i.e. where I am dealing with a single SQL_ID), if the statement has literal values that change, then each statement has a different SQL_ID.  I have experimented with setting CURSOR_SHARING to FORCE at session-level for a specific scheduled process, but I have always had very poor experiences with that approach.  It invariably causes more problems than it solves.  Instead, I use force matched SQL Profiles.

    The PeopleTools documentation sets out situations where ReUse Statement cannot be set.  This includes dynamically generated code where %BIND(…,NOQUOUTES) is used to embed a piece of SQL held in a variable.  This is a very common technique in PeopleSoft; often dynamically generated code is driven by the application configuration.  

    We also see a similar design in PeopleSoft's COBOL programs.  Static statements are loaded from the stored SQL statements table (PS_SQLSTMT_TBL) and do use bind variables, but dynamic statements are assembled at runtime from static fragments in the COBOL code and any variable values are embedded as literals rather than using bind variables.

    Forced matching will allow a SQL profile to match a statement that is the same except for different literal values.   However, dynamic SQL statements can change in ways that are beyond that, including:

    • Different instances of working storage tables can be used by different instances of the same process.
    • Different columns can be used in select and group by clauses.
    • Different criteria can be introduced. 
    • A different number of terms in an IN() condition.

    Occasionally, and I really mean very occasionally when I have exhausted other alternatives, I have dynamically created groups of SQL Profiles (still with forced matching) to cover every permutation of the variations of the dynamic SQL statement.

    Example

    Here is a dynamic statement from such a COBOL process, FSPCCURR.  This General Ledger Revaluation process adjusts the base currency value of the account balances by creating adjusting entries for the accounts being revalued. It creates corresponding entries for any gain or loss that results from the revaluation.  It was captured by AWR, and I have extracted it with DBMS_XPLAN.DISPLAY_AWR.
    The ledger table (PS_LEDGER) is joined to a working storage table of tree selectors (PS_TREE_SEL10_R001) and working storage table (PS_CURR_WRK_RT001) and the result is put into another working storage table (PS_CURR_WRK_TBL001).
    INSERT INTO PS_CURR_WRK_TBL001 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
    LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
    HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
    R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
    TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
    ,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
    ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
    TARGET_CURRENCY) SELECT A.ACCOUNT,003,AFFILIATE,' ',' ','
    ',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
    ',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'X_UKCORE
    ',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
    0002858795,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
    ',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
    ),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
    - SUM(A.POSTED_BASE_AMT),'USD' FROM PS_LEDGER A , PS_TREE_SEL10_R001 B
    , PS_CURR_WRK_RT001 R WHERE A.LEDGER='X_UKCORE' AND A.FISCAL_YEAR =
    2020 AND A.ACCOUNTING_PERIOD IN ( 0, 1, 2, 3) AND B
    .PROCESS_INSTANCE=0002858795 AND B .CHARTFIELD='ACCOUNT' AND
    A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
    A.BUSINESS_UNIT='96013' AND A.CURRENCY_CD <> 'GBP' AND FROM_CUR =
    A.CURRENCY_CD AND TO_CUR = 'GBP' AND R.PROCESS_INSTANCE = 0002858795
    GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
    LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
    OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT
    However there are several copies of each of these working storage tables, and different concurrent instances of this process may be allocated different copies. 
    There is also an in clause that lists the accounting periods to be processed. 
    So the statement can vary. Here is another version of what is essentially the same statement with different literal values and different tables and for a different accounting period (this time period 5). The parts in bold a the ones that vary from statement to statement that are not literal values
    INSERT INTO PS_CURR_WRK_TBL001 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
    LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
    HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
    R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
    TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
    ,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
    ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
    TARGET_CURRENCY) SELECT A.ACCOUNT,005,AFFILIATE,' ',' ','
    ',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
    ',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'XXXX',OP
    ERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,0002
    991789,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
    ',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
    ),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
    - SUM(A.POSTED_BASE_AMT),'AUD' FROM PS_LEDGER A , PS_TREE_SEL10_R B ,
    PS_CURR_WRK_RT002 R WHERE A.LEDGER='XXXX' AND A.FISCAL_YEAR = 2020 AND
    A.ACCOUNTING_PERIOD IN ( 0, 1, 2, 3, 4, 5) AND B
    .PROCESS_INSTANCE=0002991789 AND B .CHARTFIELD='ACCOUNT' AND
    A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
    A.BUSINESS_UNIT='13051' AND A.CURRENCY_CD <> 'AUD' AND FROM_CUR =
    A.CURRENCY_CD AND TO_CUR = 'AUD' AND R.PROCESS_INSTANCE = 0002991789
    GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
    LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
    OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT
    If I want to use SQL Profiles to introduce hints to control the execution plan, then I will need a different profile for every possible permutation.
    I start by using Carlos Sierra's coe_xfr_sql_profile.sql script.  This is a part of Oracle's SQLTEXPLAIN (SQLT) tool. It generates a SQL script that generates a SQL profile to reproduce a given execution plan for a given SQL statement that was captured by AWR.
    From there is not a big jump to add a SQL statement to generate all the permutations of the variations in the SQL (other than for bind variables) and create a profile inside a loop.  The exact details will vary depending on the behaviour of the program.  However, in this particular example I need:
    • Different SQL profiles will be needed for each accounting period because there will be a different list of accounting periods in the IN() condition.  Subquery factors n and n1 produce a list of accounting periods.
    WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
    ), n1 AS (
    SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
    FROM   n n1, n n2
    WHERE  n2.n <= n1.n 
    AND    n1.n <= 12
    GROUP BY n1.n
    )
    SELECT * FROM n1
    /
    
        PERIOD PERIODS
    ---------- ----------------------------------------
             1 1
             2 1, 2
             3 1, 2, 3
             4 1, 2, 3, 4
             5 1, 2, 3, 4, 5
             6 1, 2, 3, 4, 5, 6
             7 1, 2, 3, 4, 5, 6, 7
             8 1, 2, 3, 4, 5, 6, 7, 8
             9 1, 2, 3, 4, 5, 6, 7, 8, 9
            10 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
            11 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
            12 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
    • Lists of the various tables used for working storage can be queried from the PeopleSoft data dictionary, PSRECDEFN.  I can see that there are 5 versions of the current work table that the process can choose from.  Note that these are ordinary tables, so there are 5 different records in PSRECDEFN. 
    SELECT TO_NUMBER(substr(recname,13)) seq
    ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
    FROM   psrecdefn
    WHERE  recname like 'CURR_WRK_TBL%'
    /
    
    SEQ        TABLE_NAME
    ---------- ------------------
               PS_CURR_WKK_TBL
             1 PS_CURR_WKK_TBL001
             2 PS_CURR_WKK_TBL002
             3 PS_CURR_WKK_TBL003
             4 PS_CURR_WKK_TBL004
    
    • However, if I was working on a temporary record used in an Application Engine program, I would need to look up the number of instances of that record.  
      • The number of non-shared temporary record tables is the sum of all the instances defined on each application engine program to which the record is allocated, plus the number of global instances, up to a maximum of 99.  Instance 0 is the shared instance.  The number can be altered in development and the additional tables built by Application Designer.  This can require additional SQL Profiles be built. 
    WITH n AS (
    SELECT rownum-1 n FROM dual CONNECT BY LEVEL<=100
    ), c AS (
    SELECT c.recname
    ,      n.n instance
    ,      DECODE(r.sqltablename, ' ', 'PS_'||r.recname,r.sqltablename)||DECODE(n.n,0,'',n.n) table_name
    FROM   n
    ,      pstemptblcntvw c
    ,      psrecdefn r
    ,      psoptions o
    WHERE  r.recname = c.recname
    AND    n.n <= c.temptblinstances+o.temptblinstances
    )
    SELECT instance, table_name
    FROM   c
    WHERE  recname = 'WRK_XREF_CAL'
    /
    
      INSTANCE TABLE_NAME
    ---------- ----------------------------------------------------------
             0 PS_WRK_XREF_CAL
             1 PS_WRK_XREF_CAL1
             2 PS_WRK_XREF_CAL2
             3 PS_WRK_XREF_CAL3
             4 PS_WRK_XREF_CAL4
             5 PS_WRK_XREF_CAL5
             6 PS_WRK_XREF_CAL6
    • In this particular example, I know that every permutation of all three tables could occur in all accounting period, so I simply Cartesian join all the subquery factors.  
      • In other cases, only some permutations may occur.  This must be handled in the code that is written.  Literal values do not need to be considered because the profile will be created with force matching.
      • In Application Engine, although you often see the same instance of different temporary records used in the same process, there is nothing to prevent different instances of different records being used, and so all permutations must be considered.
    • I will also concatenate the ID for each table, and also the accounting period to produce an ID string that I can use in the name of the SQL profile.
    'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period
    The profile generated by coe_xfr_sql_profile.sql will contain the complete profile of hints for the SQL statement captured by AWR.  That is officially the only way to guarantee a particular execution plan.  However, as in this example, I could specify the just that I want to introduce in the statement, effectively treating a SQL Profile as if it was a SQL Patch.  See also:
    Here is the modified script.  Note the sections in bold.
    • The SQL statement in the FOR clause returns all the permutations of the variations in the SQL statement in an implicit cursor.  
    • Table names are concatenated into the SQL text from the columns in the implicit cursor.
    • Single quotation marks are doubled so that the string contains the single quotation mark.
    • It is important not to add or remove any spaces when introducing these changes.
    • Profiles are dropped and created inside the loop.  Force Matching is enabled.
    REM coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.sql
    SPO coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.log;
    
    WHENEVER SQLERROR CONTINUE
    REM WHENEVER SQLERROR EXIT SQL.SQLCODE;
    VAR signature NUMBER;
    
    DECLARE
      sql_txt CLOB;
      h       SYS.SQLPROF_ATTR;
      e_no_sql_profile EXCEPTION;
      PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
    BEGIN
      FOR i IN(
        WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
        ), n1 AS (
        SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
        FROM   n n1, n n2
        WHERE  n2.n <= n1.n
        GROUP BY n1.n
        ), ts AS (
        SELECT TO_NUMBER(substr(recname,13)) seq
        ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
        FROM   psrecdefn
        WHERE  recname like 'TREE_SEL10_R%'
        ), rt AS (
        SELECT TO_NUMBER(substr(recname,12)) seq
        ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
        FROM   psrecdefn
        WHERE  recname like 'CURR_WRK_RT%'
        ), wk AS (
        SELECT TO_NUMBER(substr(recname,13)) seq
        ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
        FROM   psrecdefn
        WHERE  recname like 'CURR_WRK_TBL%'
        )
        SELECT 'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period id
        ,      ts.table_name ts_table_name
        ,      rt.table_name rt_table_name
        ,      wk.table_name wk_table_name
        ,      n1.period, n1.periods
        FROM   n1, ts, rt, wk
      ) LOOP
        sql_txt := 'INSERT INTO '||i.wk_table_name||' (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,
    BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEAR,
    FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,
    POSTED_TRAN_AMT,POSTED_TRAN_CR,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATISTICS_CODE,
    RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,TARGET_CURRENCY)
    SELECT A.ACCOUNT,011,AFFILIATE,
    '' '','' '','' '',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'' '',A.CURRENCY_CD,DATE_CODE,DEPTID,2016,
    FUND_CODE,GL_ADJUST_TYPE,''X_UKCORE'',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
    0001234567,PRODUCT,PROGRAM_CODE,PROJECT_ID,'' '',R.RATE_DIV,R.RATE_MULT,'' '',
    ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
    ),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
    - SUM(A.POSTED_BASE_AMT),''GBP'' FROM PS_LEDGER A , '||i.ts_table_name||' B
    , '||i.rt_table_name||' R WHERE A.LEDGER=''X_UKCORE'' AND A.FISCAL_YEAR =
    2016 AND A.ACCOUNTING_PERIOD IN ( 0, '||i.periods||')
    AND B .PROCESS_INSTANCE=0001234567 AND B .CHARTFIELD=''ACCOUNT'' AND
    A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
    A.BUSINESS_UNIT=''12345'' AND A.CURRENCY_CD <> ''GBP''
    AND FROM_CUR = A.CURRENCY_CD
    AND TO_CUR = ''GBP''
    AND R.PROCESS_INSTANCE = 0001234567
    GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,
    DEPTID,FUND_CODE,GL_ADJUST_TYPE,OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT';
    h := SYS.SQLPROF_ATTR(
    q'[BEGIN_OUTLINE_DATA]',
    q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
    q'[ALL_ROWS]',
    q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1" "R"@"SEL$1")]',
    q'[USE_NL(@"SEL$1" "A"@"SEL$1")]',
    q'[SWAP_JOIN_INPUTS(@"SEL$1" "R"@"SEL$1")]',
    q'[END_OUTLINE_DATA]');
        :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
        dbms_output.put_line(i.id||':'||:signature);
    
        BEGIN
          DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'FSPCCURR_'||i.id);
        EXCEPTION 
          WHEN e_no_sql_profile THEN NULL;
        END;
    
        IF 1=1 THEN
          DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
            sql_text    => sql_txt,
            profile     => h,
            name        => 'FSPCCURR_'||i.id,
            description => 'coe FSPCCURR '||i.id||' @ '||:signature||'',
            category    => 'DEFAULT',
            validate    => TRUE,
            replace     => TRUE,
            force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). 
                                        FALSE:EXACT (similar to CURSOR_SHARING) */ );
        END IF;
    
      END LOOP;
    END;
    /
    
    column name format a30
    select name, status, sql_text
    from dba_sql_profiles
    where name like '%FSPCCURR%'
    order by 1
    /
    SPO OFF;
    
    When I implemented this particular example, it created 900 SQL profiles.  Each one has a different force matching signature.  Having this many SQL Profiles is not a problem for the database because they are looked up by the signature. 
    …
    TS1_RT4_TBL4_12:1278395827722096644
    TS2_RT4_TBL4_12:4126874017730826984
    
    PL/SQL procedure successfully completed.
    
    However, managing that many profiles could become a cause for concern by the DBA.  Therefore, I think it is important that they have a sensible naming convention so that it is obvious to what they relate.
    NAME                           STATUS   SQL_TEXT                                                                        
    ------------------------------ -------- --------------------------------------------------------------------------------
    …
    FSPCCURR_TS1_RT4_TBL4_12       ENABLED  INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN
    …
    FSPCCURR_TS2_RT4_TBL4_12       ENABLED  INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN
    …
    
    900 rows selected.

    Further reading

    Tuesday, November 24, 2020

    PeopleSoft Financials Ledger Partitioning Recommendations

    I have written previously about partitioning the PS_LEDGER table in Financials for the benefit of General Ledger (GL) reporting.  I have always recommended top-level range partitioning on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD with separate partitions for each accounting period.  It also leaves sub-partitioning available to use another column, usually LEDGER.

    However, recent research into partition change tracking (PCT) and materialized views has made me question that advice.  The decision is not as clear cut.

    Summary Ledgers or Materialized Views

    You can create summary ledger tables in PeopleSoft Financials that are incrementally maintained by batch processes, and then nVision reports can reference the summary ledgers instead.  If the summary ledgers are not up to date, then the reports will produce out of date results.

    Similarly, materialized views can be used in an Oracle database to create pre-generated reporting tables.  An application can reference a materialized view directly, or the database can dynamically rewrite submitted SQL queries to use the materialized view if they are sufficiently similar to the query that created the materialized view and if the optimizer judges that it is cheaper to query the materialized view.  By default, the database will check that the materialized view is up to date, that is no change has been made to the underlying tables since the last refresh commenced, before it can rewrite the SQL query.  So the query will always return the same data, but if the materialized view is out of date you don't get the performance improvement.

    You can optionally choose to configure the database to write SQL queries to use stale materialized views by setting QUERY_REWRITE_INTEGRITY=stale_tolerated at either database or session-level.  

    Materialized views can be created for the nVision queries that you wish to optimise, and no further code change is required because the database will rewrite the SQL.  You can see a typical example of this in my blog about PCT.

    Partition Change Tracking is a mechanism the Oracle database uses to 'track freshness to a finer grain than the entire materialized view'.  It can identify which partitions and subpartitions are fresh and can be used for query rewrite, and to refresh just the partitions that are stale or that contain stale sub-partitions.  

    Alternatives for Partitioning PS_LEDGER

    If you wish to create materialized views on the main ledger table, and rely upon query rewrite, and keep the materialized views up to date with respect to the ledger table, and only use them when they are up to date, then you probably want PCT to help with both rewrite and refresh.

    1. Multi-column composite partitioning 

    I usually like to range partition PS_LEDGER on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to produce a separate range partition for each accounting period.   Partition pruning works very effectively with the SQL generated by nVision.  It also allows sub-partitioning on another column, usually LEDGER.  This is desirable when a system has multiple actuals ledgers, and especially since 9.2 where the budget data is also stored in PS_LEDGER rather than PS_LEDGER_BUDG.  

    However, amongst the documented restrictions of PCT is that partitioned tables must use either range, or list partitioning, or composite partitioning with range or list as the top-level partitioning strategy.  Also, the top-level partition key must consist of only a single column (see Database Data Warehousing Guide -> Advanced Materialized Views -> About Partition Change Tracking)

    If I want to use query rewrite to materialized views for queries on LEDGER table then I have a few choices.

    • If I stick with multi-column range partitioning, then I cannot use PCT.  I must either keep the materialized views fresh, or the queries remain on the ledger table.  Any update to any partition in the ledger table will render the entire materialized view stale and prevent query rewrite.  Many customers run a suite of nVision reportbooks overnight.  I could set QUERY_REWRITE_INTEGRITY=stale_tolerated at session-level for the report books processes using a trigger on the process scheduler request table (PSPRCSRQST) - see Setting Oracle Session Parameters for Specific Process Scheduler Processes.  In this case, I would have to take responsibility for refreshing the materialized views prior to running, say, a suite of report processes.  This is effectively the same situation as using summary ledgers, but without code change to the reports.
      • I have created materialized views on summary ledger tables in order to provide compressed copies of the summary ledger.  Again, in this case, the materialized views had to be refreshed after the summary ledger maintenance process.
    • Or, I have to produce a simpler partitioning strategy for the ledger table that is still compatible with PCT.

    2. Composite Range-Range Partitioning on FISCAL_YEAR and ACCOUNTING_PERIOD

    I could composite partition both the LEDGER table and the materialized views by FISCAL_YEAR and ACCOUNTING_PERIOD, but then I cannot further subpartition by other columns.  This would degrade queries on smaller ledgers that could not be rewritten to dedicated materialized views.

    CREATE TABLE ps_ledger
    (business_unit VARCHAR2(5) NOT NULL
    …
    ) PCTFREE 10 PCTUSED 80
    PARTITION BY RANGE (FISCAL_YEAR) 
    SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
    SUBPARTITION TEMPLATE
    (SUBPARTITION ap_bf VALUES LESS THAN (1) 
    ,SUBPARTITION ap_01 VALUES LESS THAN (2) 
    ,SUBPARTITION ap_02 VALUES LESS THAN (3) 
    ,SUBPARTITION ap_03 VALUES LESS THAN (4) 
    ,SUBPARTITION ap_04 VALUES LESS THAN (5) 
    ,SUBPARTITION ap_05 VALUES LESS THAN (6) 
    ,SUBPARTITION ap_06 VALUES LESS THAN (7) 
    ,SUBPARTITION ap_07 VALUES LESS THAN (8) 
    ,SUBPARTITION ap_08 VALUES LESS THAN (9) 
    ,SUBPARTITION ap_09 VALUES LESS THAN (10) 
    ,SUBPARTITION ap_10 VALUES LESS THAN (11) 
    ,SUBPARTITION ap_11 VALUES LESS THAN (12) 
    ,SUBPARTITION ap_12 VALUES LESS THAN (13) 
    ,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
     (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
    ,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
    ,PARTITION ledger_2020 VALUES LESS THAN (2021) 
    ,PARTITION ledger_2021 VALUES LESS THAN (2022) 
    )
    ENABLE ROW MOVEMENT NOLOGGING
    /
    The materialized view will be similarly composite partitioned.  Note that I have created the materialized view for specific fiscal years and for a specific ledger.  I would create materialized views for each combination of ledger and each distinct set of analysis columns that are regularly reported upon.
    CREATE MATERIALIZED VIEW mv_ledger_2020
    PARTITION BY RANGE (FISCAL_YEAR)
    SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
    SUBPARTITION TEMPLATE
    (SUBPARTITION ap_bf VALUES LESS THAN (1) 
    ,SUBPARTITION ap_01 VALUES LESS THAN (2) 
    ,SUBPARTITION ap_02 VALUES LESS THAN (3) 
    ,SUBPARTITION ap_03 VALUES LESS THAN (4) 
    ,SUBPARTITION ap_04 VALUES LESS THAN (5) 
    ,SUBPARTITION ap_05 VALUES LESS THAN (6) 
    ,SUBPARTITION ap_06 VALUES LESS THAN (7) 
    ,SUBPARTITION ap_07 VALUES LESS THAN (8) 
    ,SUBPARTITION ap_08 VALUES LESS THAN (9) 
    ,SUBPARTITION ap_09 VALUES LESS THAN (10) 
    ,SUBPARTITION ap_10 VALUES LESS THAN (11) 
    ,SUBPARTITION ap_11 VALUES LESS THAN (12) 
    ,SUBPARTITION ap_12 VALUES LESS THAN (13) 
    ,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
    (PARTITION ledger_2019 VALUES LESS THAN (2020)
    ,PARTITION ledger_2020 VALUES LESS THAN (2021)
    ) PCTFREE 0 COMPRESS PARALLEL
    REFRESH COMPLETE ON DEMAND
    ENABLE QUERY REWRITE AS
    SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
    sum(posted_total_amt) posted_total_amt
    FROM ps_ledger
    WHERE fiscal_year >= 2019
    AND   ledger = 'ACTUALS'
    AND   currency_cd = 'GBP'
    GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
    /

    Note that I have equality criteria on LEDGER and CURRENCY_CD in the materialized view, but I have not included those columns in the select clause, and so they are not in the view.  Oracle can still rewrite queries to use this materialized view and that specify the same criteria on PS_LEDGER

    PCT will determine whether any unpruned partitions or subpartitions are stale and if so prevent query rewrite.  It is documented behaviour that the materialized view refresh will truncate and rebuild the whole top-level partitions, in this case each fiscal year, where the partition or any constituent sub-partitions are stale.  So even if just one subpartition, for one accounting period is stale, the whole fiscal year is refreshed.

    3. Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEAR

    I investigated making ACCOUNTING_PERIOD the top-level partitioning key, and sub-partitioning on FISCAL_YEAR.  

    • Partitioning pruning works correctly, so the query performance will be unaffected, 
    • PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year.  That is less work if you have fewer fiscal years than accounting periods.  Generally, this is the case.  I usually see systems that contain 3 to 6 fiscal years of data.

    However, it has a number of problems.

    • I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.
    • It is not possible to interval range sub-partition an object, so I can't automatically add partitions for future fiscal years on demand.  Instead, I am going to have to add new fiscal year subpartitions to each of the 14 range partitions.

    On balance, I don't think I would choose to implement this.

    Conclusion

    There is no single clear recommendation for partitioning and PCT.  It will depend on the circumstances.

    • If I don't need to introduce materialized views on PS_LEDGER then I would usually stick with the multi-column composite partitioning with the top-level range partition on FISCAL_YEAR and ACCOUNTING_PERIOD.
      • PeopleSoft provides summary ledgers to improve the performance of the ledger queries and compressed materialized views can be built on these.
    • If you only have a single actuals ledger then composite range-range partition on FISCAL_YEAR and ACCOUNTING_PERIOD is attractive.  
      • I do not recommend interval partitioning on FISCAL_YEAR because this affects the scope of the refresh process.  It processes a second top-level range partition.
    • If the budget data is stored in the PS_LEDGER table rather than LEDGER_BUDG then consider building separate materialized views for each value of LEDGER.  
      • If you can manage to build materialized views to support rewrite of most the queries on the smaller ledgers, then the lack of sub-partitioning by LEDGER is not going to be a problem unless the query doesn't get rewritten because the materialized views are stale.  Keeping the materialized views up to date is going to be a bigger challenge.

    Monday, December 02, 2019

    Practical Application Performance Tuning: An nVision Case Study

    I gave this presentation at the UKOUG Techfest 19 conference.  It is closely based on a previous presentation about PeopleSoft nVision performance tuning, and uses the experience of a PeopleSoft project as a case study, so I am also posting both here on my PeopleSoft blog, and also on my Oracle blog.
    This video was produced as a part of the preparation for this session.  The slide deck is also available on my website.

    Learning about and understanding the principles and mechanics of the Oracle database is fundamentally important for both DBAs and developers. It is one of the reasons we still physical conferences.
    This presentation tells the story of a performance tuning project for the GL reporting on a Financials system on an engineered system. It required various techniques and features to be brought to bear. Having a theoretical understanding of how the database and various features work allowed us to make reasonable predictions about whether they would be effective in our environment. Some ideas were discounted, some were taken forward.
    We will look at instrumentation, ASH, statistics collection, partitioning, hybrid columnar compression, Bloom filtering, SQL profiles. All of them played a part in the solution, some added further complications that had to be worked around, some had to be carefully integrated with the application, and some required some reconfiguration of the application into order to work properly.
    Ultimately, performance improvement is an experimental science, and it requires a similar rigorous thought process.

    Tuesday, November 12, 2019

    nVision Bug in PeopleTools 8.55/8.56 Impacts Performance

    I have recently come across an interesting bug in nVision that has a significant performance impact on nVision reports in particular and can impact the database as a whole.

    Problem nVision SQL

    This is an example of the problematic SQL generated by nVision.  The problem is that all of the SQL looks like this. There is never any group by clause, nor any grouping columns in the select clause in from of the SUM().
    SELECT SUM(A.POSTED_BASE_AMT) 
    FROM PS_LEDGER A, PSTREESELECT10 L2, PSTREESELECT10 L1 
    WHERE A.LEDGER='ACTUAL' AND A.FISCAL_YEAR=2018 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 8
    AND L2.SELECTOR_NUM=159077 AND A.ACCOUNT=L2.RANGE_FROM_10 
    AND (A.BUSINESS_UNIT='10000') 
    AND L1.SELECTOR_NUM=159075 AND A.DEPTID=L1.RANGE_FROM_10 
    AND A.CURRENCY_CD='GBP' AND A.STATISTICS_CODE=' '
    Each query only returns a single row, that only populates a single cell in the report, and therefore a different SQL statement is generated and executed for every cell in the report.  Therefore, more statements are parsed and executed, and more scans of the ledger indexes and look-ups of the ledger table and performed.  This consumes more CPU, more logical I/O.

    Normal nVision SQL

    This is how I would expect normal nVision SQL to look.  This example, although obfuscated, came from a real customer system.  Note how the query is grouped by TREE_NODE_NUM from two of the tree selector tables, so this one query now populates a block of cells.
    SELECT L2.TREE_NODE_NUM,L3.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
    FROM PS_LEDGER A, PSTREESELECT05 L2, PSTREESELECT10 L3 
    WHERE A.LEDGER='S_UKMGT' 
    AND A.FISCAL_YEAR=2018 
    AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12 
    AND (A.DEPTID BETWEEN 'A0000' AND 'A8999' OR A.DEPTID BETWEEN 'B0000' AND 'B9149' 
    OR A.DEPTID='B9156' OR A.DEPTID='B9158' OR A.DEPTID BETWEEN 'B9165' AND 'B9999' 
    OR A.DEPTID BETWEEN 'C0000' AND 'C9999' OR A.DEPTID BETWEEN 'D0000' AND 'D9999' 
    OR A.DEPTID BETWEEN 'G0000' AND 'G9999' OR A.DEPTID BETWEEN 'H0000' AND 'H9999' 
    OR A.DEPTID='B9150' OR A.DEPTID=' ') 
    AND L2.SELECTOR_NUM=10228 
    AND A.BUSINESS_UNIT=L2.RANGE_FROM_05 
    AND L3.SELECTOR_NUM=10231 
    AND A.ACCOUNT=L3.RANGE_FROM_10 
    AND A.CHARTFIELD1='0012345' 
    AND A.CURRENCY_CD='GBP' 
    GROUP BY L2.TREE_NODE_NUM,L3.TREE_NODE_NUM

    The Bug

    This Oracle note details an nVision bug:
    "UPTO SET2A-C Fixes - Details-only nPlosion not happening for Single Chart-field nPlosion Criteria.
    And also encountered a performance issue when enabled details-only nPlosion for most of the row criteria in the same layout
    Issue was introduced on build 8.55.19.
    Condition: When most of the row filter criteria enabled Details-only nPlosion. This is solved in 8.55.22 & 8.56.07.
    UPTO SET3 Fixes - Performance issue due to the SET2A-C fixes has solved but encountered new one. Performance issue when first chart-field is same for most of the row criteria in the same layout.
    Issue was introduced on builds 8.55.22 & 8.56.07.
    Condition: When most of the filter criteria’s first chart-field is same. The issue is solved in 8.55.25 & 8.56.10."

    In summary

    • Bug introduced in PeopleTools 8.55.19, fully resolved in 8.55.25.
    • Bug introduced in PeopleTools 8.56.07, fully resolved in 8.56.10.