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