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
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_MULTINSERT 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- 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- Nigel Bayliss: Adding and Disabling Hints Using SQL Patch.
- Kerry Osborne: Oracle Support Sanctions Manually Created SQL Profiles!
- 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;
…
TS1_RT4_TBL4_12:1278395827722096644
TS2_RT4_TBL4_12:4126874017730826984
PL/SQL procedure successfully completed.
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
- Go-Faster Oracle Blog: Hints, Patches, Force Matching and SQL Profiles. This post also demonstrates dynamically generating SQL Profiles, on this occasion for nVision queries.
- Jonathan Lewis: Hacking Profiles
@go-faster.co.uk on BlueSky
Posts
