PeopleTools 8.54: %SQLHint Meta-SQL

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
 
This new PeopleCode meta-SQL macro performs a search of SQL statement for the n^th instance of SQL command keyword and inserts a string after it.

%SqlHint(SQL_cmd, index, hint_text, DB_platform [, {ENABLE | DISABLE}])

It is particularly effective with the %InsertSelect meta-SQL.  Previously the only way to put a hint into the main select was with a variable assignment, but that didn't work if the DISTINCT keyword was used because the hint appeared behind the distinct.

%InsertSelect(DISTINCT, DMK,JOB J, EMPLID= /*+LEADING(J)*/ J.EMPLID) 
  FROM PS_JOB J 
…

which resolves to:

INSERT INTO PS_DMK (EMPLID 
 , EMPL_RCD 
 , EFFDT 
 , EFFSEQ 
 , SETID_DEPT 
 , DEPTID)  
 SELECT  DISTINCT /*+LEADING(J)*/ J.EMPLID 
 , J.EMPL_RCD 
 , J.EFFDT 
 , J.EFFSEQ 
 , J.SETID_DEPT 
 , J.DEPTID  
  FROM PS_JOB J  
…

Here is a deliberately contrived example of how to use the command.

• I have created a separate SQL object, DMK_CURJOB, to hold effective date/sequence sub-queries which I will reference from an application engine SQL.

%P(2).EFFDT = ( 
 SELECT MAX(%P(3).EFFDT) 
  FROM %Table(%P(1)) %P(3) 
 WHERE %P(3).EMPLID = %P(2).EMPLID 
   AND %P(3).EMPL_RCD = %P(2).EMPL_RCD 
   AND %P(3).EFFDT <= %CurrentDateIn) 
   AND %P(2).EFFSEQ = ( 
 SELECT MAX(%P(4).EFFSEQ) 
  FROM %Table(%P(1)) %P(4) 
 WHERE %P(4).EMPLID = %P(2).EMPLID 
   AND %P(4).EMPL_RCD = %P(2).EMPL_RCD 
   AND %P(4).EFFDT = %P(2).EFFDT)

• I want my insert statement to run in direct-path mode, so I am putting an APPEND hint into the INSERT statement.
• I am going to put different hints into each of the different SQL query blocks, including the sub-queries in the SQL object.

%SqlHint(INSERT,1,'/*+APPEND*/',ORACLE,ENABLE)
%SqlHint(INSERT,1,'/*Developer Comment*/',ORACLE,DISABLE)
%SqlHint(SELECT,1,'/*+LEADING(J)*/',ORACLE)
%SqlHint(SELECT,2,'/*+UNNEST(J1)*/',ORACLE)
%SqlHint(SELECT,3,'/*+UNNEST(J2)*/',ORACLE)
%InsertSelect(DISTINCT, DMK,JOB J) 
  FROM PS_JOB J 
 WHERE %Sql(DMK_CURJOB,JOB,J,J1,J2)

Which resolves to:

 INSERT /*+APPEND*/ INTO PS_DMK (EMPLID 
 , EMPL_RCD 
 , EFFDT 
 , EFFSEQ 
 , SETID_DEPT 
 , DEPTID)  
 SELECT /*+LEADING(J)*/  DISTINCT J.EMPLID 
 , J.EMPL_RCD 
 , J.EFFDT 
 , J.EFFSEQ 
 , J.SETID_DEPT 
 , J.DEPTID  
  FROM PS_JOB J  
 WHERE J.EFFDT = (  
 SELECT /*+UNNEST(J1)*/ MAX(J1.EFFDT)  
  FROM PS_JOB J1  
 WHERE J1.EMPLID = J.EMPLID  
   AND J1.EMPL_RCD = J.EMPL_RCD  
   AND J1.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))  
   AND J.EFFSEQ = (  
 SELECT /*+UNNEST(J2)*/ MAX(J2.EFFSEQ)  
  FROM PS_JOB J2  
 WHERE J2.EMPLID = J.EMPLID  
   AND J2.EMPL_RCD = J.EMPL_RCD  
   AND J2.EFFDT = J.EFFDT)

The %SQLHint processing appears to be done after all other expansions, so the search and insert can reach into %SQL objects.  Previously we had to put hints into the SQL object.  Although, sometimes, we could avoid that by using query block naming hints.  Now, I can place any hint after any SQL command.  I can choose to apply a hint in just one step that references a SQL object, rather than in the SQL object which affects every step that references it.

If you put multiple substitutions in for the same SQL command, only the last enabled one is processed.

I frequently find that developers love to put comments into SQL which then appears in logs files and Oracle monitoring tool.  I hate that.  Comments in SQL that run on the database is an unnecessary overhead, and it turns up later in SQL monitoring and tuning tools.  It is worth noting that comments that are not hints are stripped out of SQL in PL/SQL procedures.  Perhaps developers should put their comment in a disabled %SQLHint so it will not appear in the final SQL?

Oracle SQL Outlines/Profiles/Patches/Baselines

All this talk of adding hints to source code is going to cause an Oracle DBA to ask why not use the techniques provided by Oracle to control execution plans on application engine SQL.  The problem is that those techniques are frequently thwarted by the dynamic nature of SQL created by PeopleSoft.

• Bind variables can become literals when the SQL is generated, though profiles and baselines can handle this.
• Different instances of temporary records are different tables in the database. You would have to handle each table (or every combination of tables if you have multiple temporary records in one statement).
• Many delivered processes have dynamically generated SQL and you would again have to handle every variation separately.

Conclusion

The %SQLHint meta-SQL brings a huge advantage for Oracle's PeopleSoft developers.  Although it is possible to create platform specific application engine sections, there is huge reluctance to do this in development in Oracle.  This is entirely reasonable as it results in having to develop, test and maintain separate code lines.  Many of the meta-SQL macros are designed precisely to overcome SQL differences between different supported database platforms. Now, using %SQLHint they can now put Oracle specific optimizer hints into platform generic application engine steps, safe in the knowledge that the hints will only affect Oracle platforms.

This is going to be a very useful feature.  Simple.  Effective.  I look forward to hinting the places that other techniques cannot reach!