PeopleTools 8.54: %SelectDummyTable 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.
  
PeopleTools simply evaluates this meta-SQL as 'DUAL' on Oracle.

In Oracle, DUAL is just a convenience table.  You don't need to use it, you can use anything you want. PeopleSoft applications often use PS_INSTALLATION when they needs a single row source in a query.  This was another example of platform agnosticism.  PS_INSTALLATION is available on every platform and every PeopleSoft installation, DUAL is not.

Instead of coding this (the example is taken from ESPP_REF_REVMAIN.PSHUP.Do When)

%Select(IF_FLAG) 
 SELECT 'X' 
  FROM PS_INSTALLATION 
 WHERE %Bind(ST_SEND_SRC) = 'N'

You can now code this instead:

%Select(IF_FLAG) 
 SELECT 'X' 
  FROM %SelectDummyTable
 WHERE %Bind(ST_SEND_SRC) = 'N'

Which resolves to:

%Select(IF_FLAG)  
 SELECT 'X'  
  FROM DUAL  
 WHERE %Bind(ST_SEND_SRC) = 'N'

There are two advantages to using DUAL.

• In the database, the Oracle optimizer knows that DUAL is a special one row, one column table.  When you use it in queries, it uses this knowledge when generating the execution plan.
• If you used a real table, there was a risk that it could have no rows, or more than one row.  Either could cause application problems.  In previous versions of Oracle, when it was a real physical table, that problem could also occur with DUAL.  However, since Oracle 10g it is just a memory structure in the database, and accessing it involves neither a logical nor a physical read. 

Conclusion

PeopleSoft have created a Meta-SQL that evaluates as DUAL so that this Oracle optimization is implemented in a PeopleSoft platform generic manner. 
I would not bother to go back and change existing code to use this, unless perhaps I was visiting the code anyway, but I would certainly recommend its use going forward.

%FirstRows MetaSQL uses FIRST_ROWS instead of FIRST_ROWS(n) hint on Oracle

More MetaSQL madness. The following SQL was identified as a long running query from a PeopleTools SQL trace. Note the FIRST_ROWS hint.

PSAPPSRV.904 1-22827 13.52.01 0.000 Cur#2.904.CRPRD01 RC=0 Dur=0.000 COM Stmt=SELECT /*+ FIRST_ROWS */ CASE_ID, BUSINESS_UNIT, RC_VERTICAL, SETID_CUSTOMER, BO_ID_CUST, ROLE_TYPE_ID_CUST, BO_NAME, BO_ID_CONTACT, ROLE_TYPE_ID_CNTCT, BO_NAME_2, ROLE_TYPE_DESCR, CM_TYPE_ID, PROFILE_CM_SEQ, RC_PURP_TYPE_DESCR, SITE_ID, DESCR, PERSON_PIN, SIN, CASE_CONTACT, RC_CONTACT_NAME, SETID_ENTL, SETID_PRODUCT, SETID_PROV_GRP, PROVIDER_GRP_ID, SECURE_CASE_FLG, CUST_STATUS, EMPL_STATUS, EMPLID, SETID_DEPT, DEPTID, RC_DEPT_DESCR, SETID_LOCATION, LOCATION, RC_LOC_DESCR, PHYSICAL_LOCATION, RC_PHONE, EXTENSION, EMAIL_ADDR, RC_SUMMARY, RC_STATUS, RC_SHORT_DESCR, TO_CHAR(CREATION_DATE,'YYYY-MM-DD'), TO_CHAR(CLOSED_DATE,'YYYY-MM-DD'), COMPETENCY, START_DT, END_DT, RBTACCTID, RBTACCTNO, RCTINSTPHONENO, BO_NAME_3, BO_ID_SITE, ROLE_TYPE_ID_SITE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ADDRESS1_AC, ADDRESS2_AC, ADDRESS3_AC, ADDRESS4_AC, CITY, CITY_AC, STATE, POSTAL, COUNTRY, BO_ID_AGR, ROLE_TYPE_ID_AGR
FROM PS_RC_CASE_HD_VW2
WHERE BUSINESS_UNIT = 'HRSUK'
AND RC_CONTACT_NAME = 'Hpolite'
AND RC_VERTICAL = 'HD'
AND MARKET = 'HHD'
ORDER BY CASE_ID DESC

The SQL Statement comes from a SQL exec in the DERIVEDRCSEARCH.SEARCH_BUTTON.FieldChange peoplecode, but it contains the %FirstRows metaSQL which controls how the rows are fetched.

…%FirstRows(" String(&rc_case_qry_rows + 51) ") …

The %FirstRows metaSQL is introducing the /*+ FIRST_ROWS */ hint on Oracle. On Microsoft SQLServer it evaluates to TOP(n), causing the query to return only the first n rows of the data set. This is a change in behaviour introduced in PeopleTools 8.46 prior to which this macro evaluated to a blank string when PeopleSoft runs on an Oracle database.

The solution to my performance problem was simply to remove the hint. The following table shows timings in SQL*Plus for the statement with and without the hints.

Hint	Execution Time(UAT system)	Execution Time (PRD system)
First Rows	69s	37.08s
No hint, any FIRST_ROWS(n), or ALL_ROWS	5s	<1s

This hint is deprecated in Oracle 9i having been replaced with FIRST_ROWS(n). FIRST_ROWS is that it uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. Oracle’s Performance Tuning and Planning manual was that “Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability”.

The problem is that the FIRST_ROWS hint includes some rules that override the usual cost-based behaviour, including that an index can be used to avoid a sort operation, no matter how expensive the path may be. In most situations FIRST_ROWS is simply an inappropriate hint in Oracle 9i.

I have found two support cases (200991709 and 200769258) where this hint was causing a problem, and it is probably in response to these, that there is now a workaround available in PeopleTools 8.46. A new parameter OracleDisableFirstRowsHint has been added to the Database Options section of both the Application Server configuration file (psappsrv.cfg) and the Process Scheduler configuration file (psprcs.cfg). This flag defaults to 0, and should be set to 1 in order to supress this hint.

However, PeopleTools 8.46 is not certified on any release of Oracle prior to 9.2.0.6.0, and the FIRST_ROWS hint was deprecated in Oracle 9i when it was replaced by FIRST_ROWS(n). MetaSQLs are there to produce appropriate platform specific code. It would be much better if this MetaSQL generates a FIRST_ROWS(n) hint in the first place, perhaps resulting in this

…Stmt=SELECT /*+ FIRST_ROWS(100)*/ …

%CurrentDateIn MetaSQL prevents Oracle CBO from correctly evaluating selectivity of predicate (up to Oracle 9i)

I thought this might be an interesting subject for the first post to a new blog.

Update 6.2.2009: The problem described in this posting is not an issue in Oracle 10g - see Changes in Calculation of Predicate Selectivity in Oracle 10g

I recently discovered the Oracle Cost Based Optimizer choosing a poor execution plan for a particular critical SQL statement in a CRM system because the expansion of the %CurrentDateIn macro is excessively complicated. The problem occurs in a delivered view PS_RBC_PACKAGE_VW and and a custom view PS_XX_RBCPKCLTR_VW. The views both contain a pair of date conditions, which are coded in line with PeopleSoft standards.

AND A.FROM_DATE <= %CurrentDateIn AND A.TO_DATE >= %CurrentDateIn
AND E.FROM_DATE <= %CurrentDateIn AND E.TO_DATE >= %CurrentDateIn

On an Oracle RDBMS, this expands to

AND A.FROM_DATE <= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’) AND A.TO_DATE >= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)
AND E.FROM_DATE <= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’) AND E.TO_DATE >= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)

With the result that this statement took over 15 seconds to execute. However, if the view is recoded as follows

AND A.FROM_DATE <= TRUNC(SYSDATE) AND A.TO_DATE >= TRUNC(SYSDATE)
AND E.FROM_DATE <= TRUNC(SYSDATE) AND E.TO_DATE >= TRUNC(SYSDATE)

Then the execution time fell to less than 1 seconds.

To explain why, I shall use a very simple example that is easy to reproduced. In the following script, I have created a table with a 1000 rows and a few columns. Column B is just for padding so that the rows are not unrealistically small. Columns C, D and E contain some dates. The data is evenly distributed in C and D. In E the distribution is deliberately uneven, there are more dates further in the past.

DROP TABLE t1;
CREATE TABLE t1
(a NUMBER NOT NULL
,b VARCHAR2(2000) NOT NULL
,c DATE NOT NULL
,d DATE NOT NULL
,e DATE NOT NULL);

INSERT INTO t1
SELECT rownum
, RPAD(TO_CHAR(TO_DATE(rownum,'J'),'Jsp'),1000,'.') padd
, SYSDATE-rownum+4.2
, SYSDATE-rownum+42
, SYSDATE-sqrt(rownum)+4.2
FROM dba_objects
WHERE rownum <= 1000 ; 

Lets look at the execution plans of a few simple SQLs. Both of the following queries return 4 rows, but the cardinality is very different. In the first statement I have used just the simple TRUNC(SYSDATE), the optimizer has correctly worked out that the query will return 4 rows. However, in the second I have used the expansion of the %CurrentDateIn macro. Because the predicate contains a function, the optimizer uses a hard coded guess that the selectivity of the condition is 5% of the table.

SELECT * FROM t1 WHERE c > TRUNC(SYSDATE);
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=4 Bytes=4096)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=4 Bytes=4096)

SELECT * FROM t1 WHERE c > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=50 Bytes=51200)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=50 Bytes=51200)

If I repeat the queries on column D, the cardinality goes up to 41 (it will in fact return 42 rows, but its close), but the cardinality is still 50.

SELECT * FROM t1 WHERE d > TRUNC(SYSDATE);
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=41 Bytes=41984)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=41 Bytes=41984)

SELECT * FROM t1 WHERE d > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=50 Bytes=51200)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=50 Bytes=51200)

If I now try column E, the calculated cardinality the calculated is now 115, although the queries actually return 22 rows. The optimizer makes a mistake because the data values are not evenly distributed between the high and low values for the column.

SELECT * FROM t1 WHERE e > TRUNC(SYSDATE);
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=115 Bytes=117760)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=115 Bytes=117760)

SELECT * FROM t1 WHERE e > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=50 Bytes=51200)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=50 Bytes=51200)

So this test illustrates that the expansion of %CurrentDateIn prevents Oracle’s CBO from evaluating the selectivity correctly, and that this causes it to use a guess that is sometimes too high, and sometimes too low, but always incorrect. In some cases this will lead to an inappropriate execution plan. If TRUNC(SYSDATE) were used, the optimizer would have better information about the SQL and be able to make better decisions.
I say that this is a bug in PeopleTools. There is, of course, a simple workaround. Simply code TRUNC(SYSDATE) in the application as a customisation where necessary. However, there is no reason why the expansion of this metaSQL could not be changed, and delivered as a PeopleTools patch.